Calling all SQL gurus.

[Guest]

I really need some help with the following statement:

QTYTOBEORDERED: IIf([ORDERNOW]="YES",
IIF(Int(([NEWREORDERPT]+[NEWREORDERQTY])-([QTYONHAND]+[ONORDER]))-(([NEWREORDERPT]+[NEWREORDERQTY])-([QTYONHAND]+[ONORDER]))=0,
([NEWREORDERPT]+[NEWREORDERQTY])-([QTYONHAND]+[ONORDER]),
Int(([NEWREORDERPT]+[NEWREORDERQTY])-([QTYONHAND]+[ONORDER]))+1)),0),"")


Specifically, I keep getting the following error:



The expression you entered contains invalid syntax, or you need to enclose
your text data in quotes.

I am stumped! Any help with this is GREATLY appreciated. Thank you.

 

[Guest]

Hi.

Count your parenthesis. You have 13 left ones and 15 right ones, so there
seem to be some missing or extra. If you can find them, you should be able
to solve the problem. To help with this, you might try inserting spaces and
linefeeds to separate the statement into smaller pieces, which will make it
easier to read.

-Michael

 

[Douglas J Steele]

In addition to the error Michael pointed out, you're trying to return a
number in the True case, and a string in the False case. You should probably
return Null (or 0) instead of "".

Also, what type of datafield is OrderNow? Your code assumes that it's a text
field. If it's a Yes/No field (more correctly called a Boolean field), you
want [ORDERNOW] = True, not [ORDERNOW] = "Yes".

 

[Guest]

Thank you both. I will try your suggestions.

In addition to the error Michael pointed out, you're trying to return a
number in the True case, and a string in the False case. You should probably
return Null (or 0) instead of "".

Also, what type of datafield is OrderNow? Your code assumes that it's a text
field. If it's a Yes/No field (more correctly called a Boolean field), you
want [ORDERNOW] = True, not [ORDERNOW] = "Yes".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I really need some help with the following statement:

QTYTOBEORDERED: IIf([ORDERNOW]="YES",
IIF(Int(([NEWREORDERPT]+[NEWREORDERQTY])-([QTYONHAND]+[ONORDER]))-(([NEWREOR
DERPT]+[NEWREORDERQTY])-([QTYONHAND]+[ONORDER]))=0,
([NEWREORDERPT]+[NEWREORDERQTY])-([QTYONHAND]+[ONORDER]),
Int(([NEWREORDERPT]+[NEWREORDERQTY])-([QTYONHAND]+[ONORDER]))+1)),0),"")


Specifically, I keep getting the following error:



The expression you entered contains invalid syntax, or you need to enclose
your text data in quotes.

I am stumped! Any help with this is GREATLY appreciated. Thank you.