DLookup with Two criteria

[vba317]

Recently I got a solution to a problem of how to use two criteria to do a dlookup. This was to lookup zipcodes using the city and state.

Me!txtCompanyZipCode = DLookup("ZipCode", "tblZipCode", "City='" & Me!txtCompanyCity & "' AND State='" & Me!lstCompanyState & "'")

I have realized a problem if a city has more than one zipcode. With this code I will get the first zipcode. How can this code be rewritten so that all the zipcodes come up? Any help would be appreciated.

 

[MajP]

How is the result being used. A dlookup can only return one value. But if this is meant to provide a default choice then you could write a query for a combobox.
If you want this to populate a textbox with all possible values then it will require some code.

 

[vba317]

Yes I would want this to populate a textbox with all the values.

 

[dhookom]

I can't imagine why you would want to "populate a textbox with all the values".

You can use faq701-4233.
Set the control source to something like:

=Concatenate("SELECT ZipCode FROM tblZipCode WHERE City='" & txtCompanyCity & "' AND State = '" & lstCompanyState & "'")

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Yes I would want this to populate a textbox with all the values.

Do you mean combobox?


Randy

 

[vba317]

Randy right now I have the box set up as a textbox, I guess I could chnge it to a combobox with no problem.

dhookup I am getting a Syntax error with your code

Me!txtCompanyZipCode = Concatenate("SELECT ZipCode FROM tblZipCode WHERE City='" & txtCompanyCity & "' AND State = '" & [red]lstCompanyState [/red]& "'")

 

[vba317]

I have changed my textbox to a combobox. I am wondering if using dlookup is inappropriate for multiple values than I could use a query as a row source using the city and state in a query. But how do I pass the city and state values from my form into a query?

I tried the following and it didnt work

SELECT qryZipCode_St_City.ZipCode, qryZipCode_St_City.ZipCode
FROM qryZipCode_St_City
WHERE (((qryZipCode_St_City.ZipCode)="frmJobInformation![enter city]" And (qryZipCode_St_City.ZipCode)="frmJobInformation![enter State]"));

 

[randy700]

What is the purpose of this?
A string of all possible zip codes placed in a text box does not seem very useful.
However, if you're trying to provide a list for the user to select from, a combo box would be the way to go.
Use your query as the record source for the combo box.

SELECT ZipCode FROM qryZipCode_St_City
WHERE City = Forms!FormName.txtCompanyCity
AND State = Forms!FormName.txtState

Randy

 

[vba317]

The purpose of this for example Lawrence Ma has 5 zipcodes I would like all 5 zipcodes to come up so the user could choose from the one they want.

 

[randy700]

Did you try my suggestion?
How can a user select 1 of 5 values in a single text box?


Randy

 

[MajP]

But how do I pass the city and state values from my form into a query?

SELECT ZipCode FROM qryZipCode_St_City
WHERE City = [b]Forms!FormName.txtCompanyCity[/b]
AND State = [b]Forms!FormName.txtState[/b]