Query - NZ function

[akb]

I have imported a spreadsheet and then created a query - in the query I am trying to format the data. In my spreadsheet there are two fields that a job number can be in. If there is a job number in the Job_Num1 field I want it to pull from there, if there is not a job number in the Job_Num1 field I need it to pull from the Job_Num2 field.

I tried using the NZ function... Job_Number: Nz([JobNum1],[JobNum2])

This pulls the job number if there is something in Job_Num1 but will not pull Job_Num2 when nothing is in Job_Num1.

Any help is appreciated ...I feel like this is something simple that I am missing??

 

[pbaldy]

My guess is that the first field contains a zero length string rather than null. You'll probably need to use an IIf() to test for that.

 

[akb]

When I search for criteria "" it pulls up the data that has nothing entered. When I try Is Null, nothing pulls up.

I tried to import a .csv file rather the excel file but when I import the .csv my columns get all mixed up. However, of course the NZ function works with the .csv file. So obviously it's how Access is reading the Excel data - correct?

So how can I get my function to recognize the "" fields?

 

[pbaldy]

I was thinking

IIf([JobNum1]<>"",[JobNum1],[JobNum2])

 

[akb]

Fantastic! This worked! Thank you

 

[pbaldy]

Happy to help!