Problems with IIF. Null values required when cbo =....... 1

[aarondewberry]

All

I am trying to use a combo box to pull null records from a table , when the combobox = "Not Allocated". Else if a name is chosen from the same combo i.e "dave";"Bob" etc... then i want to pull only those records.
I have had a search around the FAQ's, threads etc and have found similar threads. But I don't know enough about IIF's to incorporate them to what i'm trying to do.
Thanks in advance.

 

[Remou]

What are you using to get the records? A form, table, query, recordset ...?

 

[aarondewberry]

I am trying to write a query to get the records using the cbo from my form. So basically, after either the persons name or 'not allocated' is selected from the cbo, i then want to open the query with the results. Null values for 'Not allocated' ....
Thanks

 

[aarondewberry]

Also, this is how i tried to write the iif!

IIf([forms]![frmAdWA]![cboUserName]="Not Allocated",[Agent] Is Null,[forms]![frmAdWA]![cboUserName])

 

[Remou]

Will it suit like so:

... WHERE Trim([Agent] & "")=IIf([forms]![frmAdWA]![cboUserName]="Not Allocated","",[forms]![frmAdWA]![cboUserName])

This will include zero length strings and Null.

 

[aarondewberry]

Brilliant.

Thanks Remou