Imbedded IIF Query Statement 1

[bigjohn99]

Not sure why the following query failed:

Select FieldA from TableA where (TableA.FieldA = IIf([Forms]![frmFormA]![ComboA]<> "ALL", [Forms]![frmFormA]![ComboA], ([TableA].[FieldA] like "*"))

This query gets created dynamically in Forms.frmFormA based on FieldA's comboBox selection with selection items: (All, Item1, Item2... etc).

I need the query to do the following:

If user selected All, then all records in FieldA will be selected else only the selected item records are selected.

In my test, this query will work if an item is selected but not 'ALL' which will return an empty recordset.

I tested the (LIKE "*") syntax using the following qurey and it works:

Select FieldA from TableA where ([TableA].[FieldA] like "*").

Any insight or suggestion is appreciated.

 

[PHV]

What about this ?
SELECT FieldA
FROM TableA
WHERE (FieldA = [Forms]![frmFormA]![ComboA] OR [Forms]![frmFormA]![ComboA] = 'ALL')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bigjohn99]

Thanks PH. But 'ALL' is not an value in Field A.

 

[PHV]

Have you even tried my suggestion ?

 

[bigjohn99]

I just tried it and it works. Thanks.

 

[bigjohn99]

Hi PH,

I must be running a different query when I tested it. When I tried it again and it still failed.

 

[PHV]

What is the SQL code of the failing query ?
And, please, define 'failing'.

 

[bigjohn99]

The qurey I ran is:

Select FieldA from TableA where (TableA.FieldA = IIf([Forms]![frmFormA]![ComboA]<> "ALL", [Forms]![frmFormA]![ComboA], 'ALL')


When I select an item, the query returns the correct item record set, but when I select 'ALL' it returns an empty recordset. (There is no SQL error but just no records being returned).

 

[PHV]

But, I suggested this !
Select FieldA from TableA where (FieldA = [Forms]![frmFormA]![ComboA] OR [Forms]![frmFormA]![ComboA] = 'ALL')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bigjohn99]

Thanks PH! your way does work...

Do you know why the other way does not work?

 

[PHV]

Do you know why the other way does not work?
Just because as you stated 30 May 07 13:50, 'ALL' is not an value in Field A