Calling all SQL gurus.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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".
 
Thank you both. I will try your suggestions.

Douglas J Steele said:
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!)


Mr. Smiley said:
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.
Click to expand...
Click to expand...
 
Back
Top