iif is null expression help

[Andromeda31]

A table with four fields of the same kind of data needs to be combined into a single field using commas as separators.

I'm trying to create a concatenated field in a query initially which I'll change to an update query, separating the data with commas
however if the fields are empty I still get the comma's :

expr1:[services]& "," &[services1]& "," &[services2]&","&[services3]

gives
field, field1, field2, field3 but also annoyingly
field, field1,,
field, ,,
etc


To get round this I have successfully found a way to combine the first two columns without getting the comma if [services1] is empty:

expr1: IIf(IsNull([services1]),[services],[services] & "," & [services1])

However, I can't get the syntax or logic correct to extend this to the other 2 fields.
Wonder if anyone could give a pointer to nesting it correctly?

it must be something like:

 IIf(IsNull([services1]),[services],[services] & "," & [services1]) AND 
IIf(IsNull([services2]),[services] &[services1] & "," & [services] & "," & [services1]& ","[services2]) etc etc

This familiar to anyone out there with 5 minutes to spare?

MTIA

 

[MSAccessRookie]

Instead of using "," as the separator, try using a conditional separator instead. Something like the following is a good start:

[services] & IIf([services1] Is Not NULL, ",", "") & [services1] ...

Of course you will need to tweak it to account for any case where services can be Null and any of the others can have a value, since this would leave you with a leading ",".

 

[Andromeda31]

I think if that works other services having a value won't be a problem - service2 is only used if there is a value in service1 etc.

Thanks for the help will give it a go and post back

 

[vbaInet]

The main problem is the comma.

What about service, will it always have a value?

 

[Andromeda31]

.. easy when you know how!

Thanks, I'm a little more fluent in 'iif' now.

 

[vbaInet]

You should note two things, Is Null is meant for queries, but the IsNull() function is what you would use to check for Null in VBA. If the value of your field is not Null but a zero-length string, then IsNull() will not suffice.

 

[Andromeda31]

vbaInet,
Yes there is always a value in 'service' and the 'service1' etc are filled in sequentially.

This is a db designed by someone else, I'm changing four combo's that have the same list for four separate fields to one multi-select combo box that puts the selections in one comma separated field.

I'll figure out how to count the number of instances each selection appears in the field for the reports later!

 

[vbaInet]

And you mentioned that service2 is dependent on service1?

Did you pick up on my point about IsNull() and Is Null?

 

[Andromeda31]

Yep they are dependent on the preceding field being filled in. I have just cast an eye over the 6000 rows and it all seems to have worked - the new field [servicelist] in the table has everything in it from [service] [service1] [service2] [service3]

I used

 expr1: [service] & IIf([service1] Is Not Null,",","") & [service1] & IIf([service2] Is Not Null,",","") & [service2] & IIf([service3] Is Not Null,",","") & [service3]

I then converted this to an update query
UPDATE table SET table.servicelist = [service] etc etc

are you saying this wouldn't concatenate say address fields where data is randomly entered into address1 address2 address3 etc and where there isn't necessarily anything in address 1 but there is something in address3?

Got the distinction between Is Not Null and IsNull, I read that elsewhere too.

off to review multi select combo boxes...

 

[vbaInet]

 expr1: [service] & IIf([service1] Is Not Null,",","") & [service1] & IIf([service2] Is Not Null,",","") & [service2] & IIf([service3] Is Not Null,",","") & [service3]

are you saying this wouldn't concatenate say address fields where data is randomly entered into address1 address2 address3 etc and where there isn't necessarily anything in address 1 but there is something in address3?

As long as you have validation checks in place then the following (called Null Propagation) is all you need (without changing anything):

[service] & (", " + [service1]) & (", " + [service2]) & (", " + [service3])

Try it out!

I then converted this to an update query
UPDATE table SET table.servicelist = [service] etc etc

Bad bad bad! If you can calculate it you shouldn't save it. Just perform the concatenation anytime you need to get a group of services. Here's why:

http://allenbrowne.com/casu-14.html

Got the distinction between Is Not Null and IsNull, I read that elsewhere too.

What I mean is Is Null and Is Not Null work better in the criteria part of a query, that's the main distinction. If you want to check if a field is null you use the IsNull() function, i.e. IIF(IsNull([service1]), "true", "false")