Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 628
  • Last Modified:

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.
0
DSE
Asked:
DSE
  • 2
  • 2
1 Solution
 
TheMekCommented:
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
0
 
cjswimmerCommented:
you don't put that in the criteria section of the barcode field, you list it AS the new barcode field.  Put in into the SQL statement like:

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

in the table that is produced you will have the results you wanted in the field 'NewBarcodeField'.  You can change it to whatever name you want.

cjswimmer
0
 
TheMekCommented:
Good point cjswimmer, I missed that he had put it in the criteria field instead of using it as an expression ;-)
0
 
cjswimmerCommented:
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
0
 
DSEAuthor Commented:
Hi,

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

Cheers,
David.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now