Null Values in query

[evanhughes]

Hi All

I have a query which I need to concactenate three fields with a following text string.
The expression I use is as follows:
Guests: [Adults in Party] & " Adult + " & [Child in Party] & " Children +" & [Infants in Party] & " Infant"
How do I get this to show only fields that have data in.
When I run the query I get this result when there are no children or infants.
6 Adult + Child + Infants
What I need is for it to show is
6 Adults or
2 Adult + 2 Child or
2 Adult + 1 Child + 1 Infant or
2 Adult + 1 Infant

Regards
Evan

 

[pbaldy]

Try this off the top of my head, which uses the fact that + propagates Nulls and & does not.

Guests: ([Adults in Party] + " Adult ") & ("+ " + [Child in Party] + " Children") & (" +" & [Infants in Party] + " Infant")

 

[evanhughes]

Paul

Thanks for the quick response but it didn't work.

Whichever way I tried it I would get a text box asking for user input of how many children in party which I don't want. Then no matter whethrer I left blank or entered a number I would get an error in the query when run.

Any other sugestions.

Evan

 

[pbaldy]

If you're getting a parameter prompt, that field name isn't spelled correctly. If that doesn't resolve it, can you post the db, or a sample of it? You may need to use CStr() on the numeric fields to combine them with text.

 

[evanhughes]

Paul

You were right about the misspelled field name but when corrected I still get an error in the field when running the query.

Here is a sample of my db.

 

[pbaldy]

It's fighting me and I'm too lazy to fight back. Try this:

Guests: IIf([Adults in Party]>0,[Adults in Party] & " Adult ","") & IIf([Children in Party]>0," + " & [Children in Party] & " Child ","") & IIf([Infants in Party]>0," + " & [Infants in Party] & " Infant ","")

 

[boblarson]

It's fighting me and I'm too lazy to fight back. Try this:

Guests: IIf([Adults in Party]>0,[Adults in Party] & " Adult ","") & IIf([Children in Party]>0," + " & [Children in Party] & " Child ","") & IIf([Infants in Party]>0," + " & [Infants in Party] & " Infant ","")

That's funny. So was I. I was waiting for you to post to see if you had the null propogation one. I have trouble remembering exactly how it needs to be written to work.

So, like you - I forced it - but slightly differently.

Guests: IIf(IsNull([Adults in Party]),"",[Adults in Party] & " Adult ") & IIf(IsNull([Children in Party]),"",[Children in Party] & " Child ") & IIf(IsNull([Infants in Party]),"",[Infants in Party] & " Infant")

 

[pbaldy]

I think it has to do with concatenating a string with a numeric value, but CStr() errors on the Nulls. Too lazy to fight it any further.

 

[evanhughes]

Thanks to all for our assistance.

Both scenarios worked fine.

Evan