Iif problem in query

[Greaser]

I created the following query:

SELECT personelTbl.name, personelTbl.phone
FROM personelTbl
Where ((personelTbl.name) = (Iif ([Forms]![personelReportFrm]![nameCombo] = "All", " ", [Forms]![personelReportFrm]![nameCombo])))

If a single name is selected in nameCombo, the query works.
If "All" is selected, the query returns nothing.

Can you help me?
Thanks,
John

 

[SkipVought]

John,

Is it your intention that when [nameCombo] is "All" that the query criteria for [personelTbl.name] is [space]? That's what the iif statement resolves to under that case.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Greaser]

I want the criteria to be blank (no criteria).
Null and "" don't work.

 

[dhookom]

Try:
SELECT personelTbl.name, personelTbl.phone
FROM personelTbl
Where personelTbl.name = IIf([Forms]![personelReportFrm]![nameCombo] = "All", personelTbl.name, [Forms]![personelReportFrm]![nameCombo])

BTW: Name is not a good name for a field since every object in Access has a name property. So when you name something name, Access sometimes mistakes name for name and returns the name of the object.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Greaser]

I changed name to theName.
"personelTbl.theName" as the TRUE part of the Iif statement does not work neither.
Any other ideas?
Thanks,
John

 

[dhookom]

Did you change the name in the table or just in the sql view? Does your table "personelTbl" have a field named "theName"? You can't use a name in sql that isn't a name in your table/query.

My suggestion was purely "from now on, it might be a good idea to create field names that are not reserved words".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Greaser]

Thanks dhookom.
It worked like a charm.
Cheers,
John