Link to home
Start Free TrialLog in
Avatar of DSE
DSE

asked on

IIF Criteria

Hi,

I need to create a make-table query that changes the value of a field titled Barcode depending upon the value stored within SP_DEFAULT_SA_EXP_END_DATE, which is a Date/Time field.

The expression I have entered into the criteria of the Barcode field is as follows. When run however it causes no data to be entered into any fields at all.

IIf([SP_DEFAULT_SA_EXP_END_DATE] Is Not Null,[SB_REF] & "/" & [SP_REF],[SB_REF])

Can anyone offer any suggestions?

Thanks,
David.
Avatar of TheMek
TheMek
Hi David,

try it like this instead:
IIf(IsNull([SP_DEFAULT_SA_EXP_END_DATE])=False,[SB_REF] & "/" & [SP_REF],[SB_REF])

Hope this helps,
   Erwin
ASKER CERTIFIED SOLUTION
Avatar of cjswimmer
cjswimmer
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TheMek
TheMek
Good point cjswimmer, I missed that he had put it in the criteria field instead of using it as an expression ;-)
Avatar of cjswimmer
cjswimmer
sorry, wrong syntax:

SELECT Field1, Field2,
 IIf([SP_DEFAULT_SA_EXP_END_DATE] Is Not Null,[SB_REF] & "/" & [SP_REF],[SB_REF]) AS
NewBarcodeField
INTO tblNewTable FROM tblOldTable
Avatar of DSE
DSE

ASKER

Hi,

Thanks for your suggestions...the SQL statement worked first time...saved me a lot of time and headaches! :-)

Cheers,
David.