Search Form query problem

[dbar10]

I have several Search Forms that I use to pull data for an Entry Form. This particular one is an example of several others and I am having the same problem on all of them. This Search Form has 4 combo boxes to search on. They work fine as long as I am looking for any choice other than <All>. When I select all I get no data returned. I have tried everything I can think of with no success. Can you look at this. In the query below I am trying to search on <All> in the StatusID field. <All> = StatusID 1.


SELECT [Client Table].ClientID, [Client Table].StatusID, [Status Table].Status, [Client Table].ProgramID, Program.Program, [Client Table].LastName, [Client Table].FirstName, [Client Table].Middle, [Client Table].Address1, [Client Table].Address2, [Client Table].City, [Client Table].State, [Client Table].Zip, [Client Table].PhoneTypeID1, [Client Table].PhoneTypeID2, [Client Table].PhonetypeID3, [Client Table].Phone1, [Client Table].Phone2, [Client Table].Phone3, [Client Table].SSN, [Client Table].[SOC Date], [Client Table].[E-Mail], [Client Table].DOB, [Client Table].LocationID, [Client Table].[ProgramID#], [Client Table].Race, [Client Table].County, [Client Table].MaritalID, [Client Table].Gender, [Client Table].[MedRec#], [Client Table].DXCode, [Client Table].StatusID, [Client Table].[CBSA#], [Client Table].Team, [Client Table].Department, [Client Table].Instructions, [Client Table].[Contact Info], [Client Table].Location, [Client Table].[CBSA EffecDate], [LastName] & ", " & [FirstName] & " " & [Middle] AS FullName
FROM Program INNER JOIN ([Status Table] INNER JOIN [Client
Table] ON [Status Table].StatusID = [Client Table].StatusID) ON Program.ProgramID = [Client Table].ProgramID
WHERE ((([Client Table].ClientID)<>167) AND (([Status Table].Status)=IIf([Forms]![ClientSearch Form]![StatusSearch]="<All>",([Status Table].[Status]) Like "*",[Forms]![ClientSearch Form]![StatusSearch])) AND ((Program.Program) Like ([Forms]![ClientSearch Form]![ProgramSearch] & "*")) AND (([Client Table].LastName) Like ([Forms]![ClientSearch Form]![TextLname] & "*")) AND (([Client Table].FirstName) Like ([Forms]![ClientSearch Form]![TextFName] & "*")))
ORDER BY [Client Table].LastName, [Client Table].FirstName;

Thanks for any help that you can give.

 

[PHV]

Replace this:
(([Status Table].Status)=IIf([Forms]![ClientSearch Form]![StatusSearch]="<All>",([Status Table].[Status]) Like "*",[Forms]![ClientSearch Form]![StatusSearch]))
with this:
([Status Table].Status=[Forms]![ClientSearch Form]![StatusSearch] OR [Forms]![ClientSearch Form]![StatusSearch]="<All>")

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

 

[dbar10]

I never thought to use the OR in my statement. I must have tried everything else. Thank you