Query with multiple parameters some can be blank

[Laurel92]

I want to make a query which accepts 1 to 3 zip codes as input, zip2 and zip3 I want to be able to leave blank. Currently it gives me an empty report. Can someone HELP?? please.

T

 

[Remou]

What is the SQL?

 

[Laurel92]

SELECT tbl_Org_Name.Name, tbl_Org_Address.Org_Address_txt, tbl_Org_City.Org_City_Txt, tbl_Org_Address.Org_Phone_txt, tbl_Org_Address.Org_Fax_txt, tbl_Solicitor.Sol_First_txt, [Zip2] AS Expr2, [Zip3] AS Expr3
FROM tbl_Org_Name INNER JOIN (tbl_Org_Address INNER JOIN tbl_Org_City ON tbl_Org_Address.[Org City_Skey_Lng] = tbl_Org_City.Org_City_Key) ON tbl_Org_Name.NameKey = tbl_Org_Address.Org_Add_Key, WHERE (([ZIP])=[Org_Postal_Cd_Txt]) AND (([Zip3])=[Org_Postal_Cd_Txt]))
ORDER BY tbl_Org_City.Org_City_Txt where (Zip2) <> blank;

 

[Remou]

I still am not quite sure what you want, try:

where Trim(Zip2 & " ") <> ""

PS Name is a very bad name for a field, it will eventually bite.
List of reserved words in Access 2002 and Access 2003

http://support.microsoft.com/kb/q286335/

 

[Laurel92]

Here is the code that works (Adds 1 person to any org within 1 zip code. What I want is to be able to do the same thing with 1 OR more zip codes. My code for adding a second zip doesn't work either. (even if zips match)


SELECT tbl_Org_Name.Name, tbl_Org_Address.Org_Address_txt, tbl_Org_City.Org_City_Txt, tbl_Org_Address.Org_Phone_txt, tbl_Org_Address.Org_Fax_txt, tbl_Sol_Prefix.Solicitor_prefx_txt, [Solicitor] AS Expr1, tbl_Solicitor.Sol_First_txt
FROM tbl_Org_Name INNER JOIN (tbl_Org_Address INNER JOIN tbl_Org_City ON tbl_Org_Address.[Org City_Skey_Lng] = tbl_Org_City.Org_City_Key) ON tbl_Org_Name.NameKey = tbl_Org_Address.Org_Add_Key, tbl_Sol_Prefix INNER JOIN tbl_Solicitor ON tbl_Sol_Prefix.Sol_prefx_key = tbl_Solicitor.Sol_Prefx_skey_lng
WHERE ((([Solicitor])=[Sol_Last_txt]) AND (([ZIP])=[Org_Postal_Cd_Txt]))
ORDER BY tbl_Org_City.Org_City_Txt;

 

[Golom]

I'm suprised that it runs at all

ORDER BY tbl_Org_City.Org_City_Txt [red]where (Zip2) <> blank[/red]

is invalid SQL and [blue]tbl_Solicitor[/blue] from which you are referencing a field in the Select clause doesn't appear in your FROM clause.

Which table contains [ZIP], [ZIP2] and [ZIP3] ?

 

[Laurel92]

I must have copied the wrong query, here it is.

SELECT tbl_Org_Name.Name, tbl_Org_Address.Org_Address_txt, tbl_Org_City.Org_City_Txt, tbl_Org_Address.Org_Phone_txt, tbl_Org_Address.Org_Fax_txt, tbl_Sol_Prefix.Solicitor_prefx_txt, [Solicitor] AS Expr1, tbl_Solicitor.Sol_First_txt
FROM tbl_Org_Name INNER JOIN (tbl_Org_Address INNER JOIN tbl_Org_City ON tbl_Org_Address.[Org City_Skey_Lng] = tbl_Org_City.Org_City_Key) ON tbl_Org_Name.NameKey = tbl_Org_Address.Org_Add_Key, tbl_Sol_Prefix INNER JOIN tbl_Solicitor ON tbl_Sol_Prefix.Sol_prefx_key = tbl_Solicitor.Sol_Prefx_skey_lng
WHERE ((([Solicitor])=[Sol_Last_txt]) AND (([ZIP])=[Org_Postal_Cd_Txt]))
ORDER BY tbl_Org_City.Org_City_Txt;

 

[Remou]

I am not sure where these extra zips are coming from, also, the first bit of SQL does not seem to have been built using the query designer, so I am a little confused. Have you tried:

[tt]<...>
WHERE [Solicitor])=[Sol_Last_txt] AND ([ZIP]=[Org_Postal_Cd_Txt] Or [Zip2]=[Org_Postal_Cd_Txt])
ORDER BY tbl_Org_City.Org_City_Txt;[/tt]

That is,
Select records Where Solicitor equals [Sol_Last_txt] And(either Zip Or Zip2 is equal to Org_Postal_Cd_Txt).

 

[Laurel92]

The whole thing was built with the query designer including the first zip. When I try adding a second zip to equal a [Org_Postal_Cd_Txt] that is when I get no records.

 

[Laurel92]

Thanks Remou, that did it.

 

[Remou]

Did you put the criteria for the second zip on the Or line (next one down) or the And line?

The criteria should look a bit like this:

[tt]
Solicitor Zip
...
...
Criteria Sol_Last_txt Org_Postal_Cd_Txt
Or Sol_Last_txt <>""[/tt]

Or there abouts.

 

[Remou]

Ok. I missed your last post, so ignore my last post.