IIf not working??

[mrsbean]

Hi

I am working in Access 97 and would like to "join" fields in a report using the IIf statement. This is what I have so far:

=IIf(IsNull([Job1]),"",[Job1]) & IIf(IsNull([Job2]),""," / " & [Job2])

The problem is that the "/" character is displayed regardless of whether the fields are empty or not. Where am I going wrong? Is there a better way of doing this, i.e., showing all the "Job" fields separated by a "/"?

Thanks

 

[neileg]

Are you sure that Job 2 is null? That is not the same as a zero length string (ie "") Is Job 2 a text field or numeric?

 

[mrsbean]

Thanks for your reply.

The field is definitely empty and it is a text field - what is the difference between a zero length string and a null field (excuse my ignorance, I am a real hack!) and how does this affect the way the fields are displayed (or not)?

 

[namliam]

try this:

= [Job1] & iif(len([Job1])>0 and len([Job2])>0;" / ";"") & [Job2]

Regards

The Mailman

 

[mrsbean]

Thanks for the quick reply! I have tried this but I get an error message telling me the expression contains invalid syntax. I'm assuming "len" refers to the length of the field? Is there another way around this?

 

[neileg]

The Mailman's code will work for nulls and zero length strings. However, if Job1 is blank and Job2 is populated, does it matter that you can't distinguish between the result from Job1 being populated and Job2 blank? For example:
Job1 = abc
Job2 = 123
Result abc/123

Job1 = abc
Job2 is blank
Result is abc

Job1 is blank
Job2 =123
Result is 123

Your formula would have produced an odd result if Job1 was blank and Job2 was populated because it would have included the / character but only one code. For example
Job1 is blank
Job2 =123
Result is /123

You should read the help files on ZLS and nulls, and search this forum. You may need to become familiar with the Nz() function which converts nulls to useable values.

 

[neileg]

Thanks for the quick reply! I have tried this but I get an error message telling me the expression contains invalid syntax. I'm assuming "len" refers to the length of the field? Is there another way around this?

Mailman has used semicolons instead of commas ie ; instead of ,