Connie,

<< The process is that the user is prompted for the parameters.  The results
are placed in a list, in the form of pname=pvalue;pdatatype.
This code goes through the list, and replaces the parameters. And, BTW, This
is happening for all of the previous queries.  >>

I'm afraid that your generic procedure doesn't tell me much since I don't
know what is being passed into this procedure.  Could you give me an example
that shows the inputs to the procedure ?

--
Tim Young
Elevate Software
www.elevatesoft.com

'StartDate=2005-03-28;Date'
'EndDate=2006-03-28;Date'
'RecordUID= ;String'
'Closed=True;Boolean'

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Connie,

<< The process is that the user is prompted for the parameters.  The results
are placed in a list, in the form of pname=pvalue;pdatatype.
This code goes through the list, and replaces the parameters. And, BTW, This
is happening for all of the previous queries.  >>

I'm afraid that your generic procedure doesn't tell me much since I don't
know what is being passed into this procedure.  Could you give me an example
that shows the inputs to the procedure ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Using this query
Select I.*
from Invoice I
where
( (:StartDate is null) or ( I.InvoiceDate >= :StartDate)) and        
((:EndDate is null) or( I.InvoiceDate <= :EndDate)) and
 ((:RecordUID is null) or (I.CustUID = :RecordUID) or (I.UID = :RecordUID)) and
( (:Closed is null) or (I.Closed = :Closed))

Order by I.BillName, I.CustName, I.InvoiceDate

and these parameters:
'StartDate=2005-03-28;Date'
'EndDate=2006-03-28;Date'
'RecordUID= ;String'
'Closed=True;Boolean'

I get 0 records
Add the 'isorder' statement and I get 254 records.

If I remove the 'recordUID' condition (the parameter is blank), I get no change.

Connie McBride <conniem@axcis.net> wrote in
news:FC090D6C-6101-404E-8441-FC2216D459A1@news.elevatesoft.com:

> Using this query
> Select I.*
> from Invoice I
> where
>  ( (:StartDate is null) or ( I.InvoiceDate >= :StartDate)) and        
>  ((:EndDate is null) or( I.InvoiceDate <= :EndDate)) and
>   ((:RecordUID is null) or (I.CustUID = :RecordUID) or (I.UID =
>   :RecordUID)) and
>  ( (:Closed is null) or (I.Closed = :Closed))
>
> Order by I.BillName, I.CustName, I.InvoiceDate
>
> and these parameters:
> 'StartDate=2005-03-28;Date'
> 'EndDate=2006-03-28;Date'
> 'RecordUID= ;String'
> 'Closed=True;Boolean'
>
> I get 0 records
> Add the 'isorder' statement and I get 254 records.
>

do you mean adding

(1)  I.IsFromOrder = false and

or adding

(2)   (I.IsFromOrder = false or I.IsFromOrder = true) and  

?

If (1) then we're back to my initial hypothesis of Isorder being equal to

(false and <all the rest which was already false for some strange
reason>)

i.e. AND takes priority over = , which is not very intuitive IMHO.

If (2) then I'm lost for an explanation - sorry.

I would have suggested testing the date values against zero as well, i.e.
1899-12-31 if my memory is correct, but since the correct rows are
returned by the query with the dummy test, this probably wouldn't be
relevant.

One more possibility: is InvoiceDate a DateTime value? Does it include
the time part? If so you should never get any rows corresponding to the
"EndDate" as InvoiceDate is always greater than 00:00h on the specified
date. Leaving EndDate empty should therefore work if you want all rows up
to now.
--
Chris

Chris Erdal <chris@No-Spam-erdal.net> wrote in
news:Xns97956488D539014torcatis@64.65.248.118:

> Leaving EndDate empty should therefore work if you
> want all rows up to now.

Sorry, I mis-read EndDate as being equal to StartDate, not one year
later...

--
Chris

Chris Erdal <chris@No-Spam-erdal.net> wrote:

I meant:
(2)   (I.IsFromOrder = false or I.IsFromOrder = true) and  

?

If (2) then I'm lost for an explanation - sorry.


One more possibility: is InvoiceDate a DateTime value? Does it include
No

Connie,

<< 'StartDate=2005-03-28;Date'
'EndDate=2006-03-28;Date'
'RecordUID= ;String'
'Closed=True;Boolean' >>

I've attached what I tried here, and it seems to work just fine, so I'm
going to need to see the tables that you're using to try and find out what
is going on.  I can't seem to recreate anything here based upon what you've
given me so far.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Connie,

I've attached what I tried here, and it seems to work just fine, so I'm
going to need to see the tables that you're using to try and find out what
is going on.  I can't seem to recreate anything here based upon what you've
given me so far.


Tried with your table (it worked).
Replaced with my table (it doesn't work - returns 0 records)
Attached is my table.

Connie McBride <conniem@axcis.net> wrote in
news:7C295728-09DC-4BB1-A85E-6C64A386BBF2@news.elevatesoft.com:

> Attached is my table.

Connie,

 I've tried a little test program (attached) on your table, and it seems
to work with your SQL:

Select I.*
from Invoice I
where
 (:StartDate is null or  I.InvoiceDate >= :StartDate) and     
 (:EndDate is null or I.InvoiceDate <= :EndDate) and
 (:RecordUID is null or (I.CustUID = :RecordUID or I.UID = :RecordUID))
and
 (:Closed is null or I.Closed = :Closed)

--
Chris

=ybegin line=128 size=2143 name=TestParams.zip
zu-=n>*,*2*↨^‡fKL=n**¾;**3***˜“ž[XŽ�—�…™Dc>¨‰“)­=I2¥kö*-·tZº@³¾^]—I`��6¢ÃÛ»ñÝê($Û43—Üåô”ÿfŽ¦¸¹é�i`¦e3NäLàX»Ùéi(ªú¶VO¤
¯dYFʦ讘-³°œ×òk‰½™3‡×k=@C« €|ø>ÍËûDÄóMX>U²p¼‡ª*´]œ
‚N=nø¹Q�í9])µ% ¢w&|Ìb%³Üº¦ÜÁï�Ë'�õTšvj)ß±fö<;kÁ·Œ½±2 ©%6Ö£>䬊
sLNÊ4q³àMwlu$LÿÆc=@¦1\ô]‚,�v<äŒH'=}NYª­úêÛ÷ìŸÄÚäË¥<Oanvƒ*ëõÔ†SfÚdêPK½ºô¸t2¦7|È;¥Š’½=}õ[~È>Õ=Ia¶,=@à\£æòg–‘FÏGìΟ_6™ø~l4
†CÕÂêÀ�bÚy‚‚Ûû·¢ll¡çC¿ÏÄ=nÅ;=IÉÝ=@Hƒcà=@ôBíRåa,Ir‚³©×䶤žÛôÏÏ{þ–Æ¢•?Èo^¦µ�=MûþEˆ¹q™=n7ËUÔž÷ç W? å‡{=n¯¡çxQ�y+A3>K*Y2™
>6¦˜kB@MXO¹iÅ=}ô’~=nƒ”\^ãÃxÉô²¦ç픬9^„â=IÔ¥9á‡Ô§ÔOÓ~´ýÂOÿetrÈ[r°„%^PaTÃnþü‹¡øàðê£Ê_*<´ŸZ’eטٗ¸g ÿæý©ã1a2¿£fèËœ+%U˜
•g�iÕÅÑö7[‹°§=Mû¢úÒ¶1Â0r+�x±lûmë·2@Èîlhðœ•nõßÐ%—¡ÈŠå"ZGÉßåÁáõ=IÞ…cßâ)î;&hOzxŠw�-^¿¦v½—®9]æz�•Ÿ¢ê¥�¥ÂÝ ÕzeMܬöÿ0Â{
û|˜ŽDÀ¼²¥4¨cÁAbK]vΘÑõå[9üÂZÃJÖÖ²¾¾×¼e¡W’L# ×l];K¸�fê{S=J½¢¥
jiA‚‰Žú÷¬d‹UôòqL¼VÀ—Í{tÍ:¡"_=IÇõ}�0ߤ‘” 4úzè}>âqL2"Þ}Í
=JW/¹û¾'ëÓä˜ÐO©Ñ–PO³úï�{ѲP²k/ÒenË�Ñø�Ò¾• ’_ÊÖÕZŸ¾Á4O=}üBï&Å+¼ªµzÿ‹? Œœýn,�ë¾L„uó×n‡ËCÃ�‡Å=@»çg”à�f/Ž€wþ’Äma‚=JBÌ(\1˼îÏ
LÒK)Ú¯$ƒ B¿…#Ǥf8´¨#9Êÿ±�ŠÃähoU&iíE›Ø˜M 4’T=Im¥‡å]Æ çéožã­°?)ç)Kæ×"½‘¤(õfþ]Ô¦:¢€ÿšØ™ö€ú)(,zu-=n>*,*2*×|¨^X=@g±***æ*
**6***zœ™”��ž[XŽšœ�·é4ìZ:ð!j°ltJ�Q;=naS+Œgôs¥3ÁžÛ—\  'ã=I¡k&R˜°å"a8óà„“ßn¶„+†çöÛó ë¾V:m‡¾3X=@Ú˜/ƒÕ8xpZ˜g›Nøc®³0ÁòÝãNã
³h(€AkÁZùG$\¾=I "¡Wæ‘òYÍÿAzu-=n>*,*2*a†¨^X‘ª,**
1**3***˜“ž[Xš‹�Çw™Z:çMI¦‚ÓÚl»jgºú‡›À—Š!b=}¼»ÞÊÔ)§ñø1,/¤KHùæ£ù=M
C}2˜ò<©°=M˜ÑìªtR-•>DžámòIXŒ#Ì1+ÞÐUêâýuí}†'д}‹š?Î~•&Ë’èö˜s‹¾Þ‹=IÏôê�ÅœÄô¿5=M[¨Ð¨ß²MØ“ð¶ÔW=}KUÿÅ+ƒrÃñFç˳-Íü\…J]‘b�Á­†
‚àKãMöt cÝ™GòùìóíÙ,þ˜bLµÀcØ,ÒËËV>-™Öô—O¦m±'€…kT_îë¤=M™û;Ö«—¦ãGá#ô]3u¥ù2`‡ï15ÈëÆÝ7Ò¡+áÑ,¬~µ}4¬>Ò47Žª„øÐQÆã¼6l«åëšW‰¤K²B>°h
p©«Ã(kŽåð½=nE [1û�Ï=n}A?ø*§rάµ»Ÿ7­_?UbØ[�&C=MtUÃÁP³+n{íÏú÷/@{ôƒ=}ÖM‹òÃT•äÐE÷�x}ÀѺ«Z8�¡ˆéf»ÙˆÆŽ™=@Ykq_¸Šï¯EÊ€¿f…Jž=@
|8\ˆ›A7kj�ÕÁ¶òv¯6· 'q$=nvÔbº¯šêµòù‘íø½!WãÈ®ÂÂ_²í[ ¢WÃé%s]b—±ÅèâÅt_†-Æ’åÚ/’DõÝU¡ötŒ]œ�ómú‘ü˜9Ïÿ¦¿Þ€;¤×â`šÌۅ₃«
×@Ú_¿bp_ÞÉu9•¸‘»sóêÌt-Ü<¦&u* $þǪ̀˃®)ieżVÊ7œS‘?N>U_i5ѱ&}Ÿ¦þG"5ª‰Ù#1zu+,>*>*,*2*↨^‡fKL=n**¾;**3*********J*****
**˜“ž[XŽ�—zu+,>*>*,*2*×|¨^X=@g±***æ***6*********J***s=n**zœ™”��ž[XŽšœzu+,>*>*,*2*a†¨^X‘ª,**
1**3*********J***$=n**˜“ž[Xš‹�z
u/0****-*-*Ò***Ë1****
=yend size=2143 crc32=ad56cebc