IIF Syntax

P

Paul LeBlanc

I'm having a problem with the following statement
SOURCE:
IIf(Mid$([TblProp]![SOURCE],1,2)=[TblSTAGE]![STAGE],[TblSTAGE]![STAGENAME])
When I added it to the SQL statement
SELECT TblPROP.CODE, VARIETY.NAME, TblPROP.PLOT, TblPROP.DATEIN,
TblPROP.DATEOUT, TblPROP.QTY, TblPROP.LOC, TblPROP.SEQ,
IIf([Qty]<315,1,Int([QTY]/315)) AS [ROWS],
IIf(Mid$([TblProp]![SOURCE],1,2)=[TblSTAGE]![STAGE],[TblSTAGE]![STAGENAME])
AS SOURCE, "*" &
Code: 
 & [PLOT] & [LOC] & [SEQ] & "*" AS BC
FROM TblSTAGE, TblPROP INNER JOIN VARIETY ON TblPROP.CODE = VARIETY.ItemCode
WHERE (((TblPROP.DATEIN)=[STICK WEEK ?]))
ORDER BY TblPROP.LOC, TblPROP.SEQ;
I get thousands of results, most with the source field null but always with
one that is right
 
J

Jeff Boyce

Paul

Perhaps it is insufficient caffeine this morning, but I don't understand
what problem you are having.

(if you wish to exclude "nulls", use a WHERE clause to do that)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

Paul LeBlanc

Jeff,
we are converting to a different method of tracking the source and this is
the first week of the changover. The SQL statement is the datasource for a
report/label. the data did not get appended right and I was looking for a
workaround other than going in and doing it manually, turned out that was
easier!!
Thanks anyway, your probably right I could have added an is not null filter
but hopefully this is a one time occurence

Jeff Boyce said:
Paul

Perhaps it is insufficient caffeine this morning, but I don't understand
what problem you are having.

(if you wish to exclude "nulls", use a WHERE clause to do that)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Paul LeBlanc said:
I'm having a problem with the following statement
SOURCE:
IIf(Mid$([TblProp]![SOURCE],1,2)=[TblSTAGE]![STAGE],[TblSTAGE]![STAGENAME])
When I added it to the SQL statement
SELECT TblPROP.CODE, VARIETY.NAME, TblPROP.PLOT, TblPROP.DATEIN,
TblPROP.DATEOUT, TblPROP.QTY, TblPROP.LOC, TblPROP.SEQ,
IIf([Qty]<315,1,Int([QTY]/315)) AS [ROWS],
IIf(Mid$([TblProp]![SOURCE],1,2)=[TblSTAGE]![STAGE],[TblSTAGE]![STAGENAME])
AS SOURCE, "*" &
Code: 
 & [PLOT] & [LOC] & [SEQ] & "*" AS BC
FROM TblSTAGE, TblPROP INNER JOIN VARIETY ON TblPROP.CODE =
VARIETY.ItemCode
WHERE (((TblPROP.DATEIN)=[STICK WEEK ?]))
ORDER BY TblPROP.LOC, TblPROP.SEQ;
I get thousands of results, most with the source field null but always
with
one that is right[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
Top