Dlookup from a combo box selection

[rplohocky]

Looking for help on a dlookup expression.

I created a query named "qry idle" that has a list of addresses for various vehicles around the country.

I have another query named "qry Zip Codes" with all the zip codes in the U.S. along with the coordinates for each zip code.

In the "qry idle", I have a field named "Driver Lat". I am trying to write an expression that will look at my combo box "combo ZipCodeSearchBox", which is where I would enter a zip code to search for, then look through the list of all the zip codes in the country and return the lat coordinates for that zip code, which is in the column next to each zip, to "Driver Lat".

As plain as i can explain is....
combo, look through zip code column and return lat coord's from the column next to you and return the coord's to "Driver lat" in the "qry idle".

This what i wrote so far...

combo ZipCodeSearchBox = DLookUp("[zip_code]","qry Zip Codes","[latitude]")

Definition:
1. combo ZipCodeSearchBox is the combo box where I enter a zip code.
2. [Zip_code] is the field.
3. qry Zip Codes is the query.
4. [latitude] is the coords.

Basically this expression is supposed to bring back the coord's from "qry Zip Codes" and place the coord's into "qry idle".

My expression is not working, any help would be great, thanks.

 

[Gasman]

You have the syntax all wrong.
You also have to concatenate the filedname you are looking for with the criteria

http://access.mvps.org/access/general/gen0018.htm

One of mine

    strEmployee = DLookup("[Forename] & ' ' & [Surname]", "tblEmployee", "EmployeeID = " & rst!EmployeeID)

That gets the forename and surname from the table tblEmployee where the EmployeeID is equal the EmployeeID from a recordset I created.

Yours would be along the lines of

latitude = dlookup("[Latitude]", "qry Zip Codes", "zipcode = " & Me.comboZipCode)

Also pay attention in the link to the type of criteria and it's delimiters if required.

That said, it is recommended to link the data and not use domain functions like DLookup in queries, as they are supposed to be slow, but that is up to.you

 

[rplohocky]

You have the syntax all wrong.
You also have to concatenate the filedname you are looking for with the criteria

http://access.mvps.org/access/general/gen0018.htm

One of mine

    strEmployee = DLookup("[Forename] & ' ' & [Surname]", "tblEmployee", "EmployeeID = " & rst!EmployeeID)

That gets the forename and surname from the table tblEmployee where the EmployeeID is equal the EmployeeID from a recordset I created.

Yours would be along the lines of

latitude = dlookup("[Latitude]", "qry Zip Codes", "zipcode = " & Me.comboZipCode)

Also pay attention in the link to the type of criteria and it's delimiters if required.

That said, it is recommended to link the data and not use domain functions like DLookup in queries, as they are supposed to be slow, but that is up to.you

Thank you Gasman.
I inserted the expression into the criteria area in the [storage lat] field and i am still getting syntax errors. I made sure that every character is correct as well. Any idea's?
Also I would rather do relationship joining but I'm new to access and can't see how this can be done. I have a table with every zip code in the country and a table that has about 500 rows of data. Each row has a different zip code in it. I'm trying create this DB so i can insert a zip code of a location where a customer needs a vehicle and access with look at each of the 500 rows of data to find a vehicle that is closest that that zip. It should work just like a store locator search on websites. For example, if a person wanted to find the closest walmart they would put their zip code into the store locator search box and the site returns with locations sorted by miles to the closest store. I have spent about 12 hours scanning the internet trying to find something that i can modify but no success. If you have any suggestions i would be very grateful.

 

[Gasman]

If you are putting it directly into the criteria field of the query window, you would need the full form name and control. When I have to do that I use the Build wizard to get the correct syntax.?

So I would get the form control name correct first, then edit that to include the dlookup syntax.

Others on here have worked with locations like you are trying to do, so hopefully someone more knowledgable will chip in?
I think a lot will have to do wth your structure?

All I can help with is getting your syntax correct.

Post back with the expression you have in your query.
Can you upload the DB? or post you relationship window?

Also search this site for Latitude or Longitude
I found this thread doing that. It might help?

 

[rplohocky]

If you are putting it directly into the criteria field of the query window, you would need the full form name and control. When I have to do that I use the Build wizard to get the correct syntax.?

So I would get the form control name correct first, then edit that to include the dlookup syntax.

Others on here have worked with locations like you are trying to do, so hopefully someone more knowledgable will chip in?
I think a lot will have to do wth your structure?

All I can help with is getting your syntax correct.

Post back with the expression you have in your query.
Can you upload the DB? or post you relationship window?

Also search this site for Latitude or Longitude
I found this thread doing that. It might help?

Yes I found that tread as well but it has to do with adding 2 zip codes to determine the distance. I have a DB with zip codes of vehicles around the country. I want a to put a combo box on to a form which has a drop down of every zip code in the country then select or type the zip for the area I need a vehicle. Then a query would open to show all the vehicles sorted by the closest vehicle to that zip code. This DB would work exactly like a store locator function on a website to find the nearest store location. I have found a ton of methods for zip to zip but virtually nothing, access wise, on a DB where you can import data to a table, choose a zip code and have it return with the nearest location to that zip. ts been very frustrating because I know it's possible to build I'm just so green at access.

Thanks for your help though!

 

[Gasman]

Well I am pretty much a novice at Access as well, but I would have *thought* that you would need to run a function against each zipcode to your zipcode and get the distance, then sort by that distance?
So you could twist the logic of that thread to get what you want?

I haven't looked at that thread in depth, but if it can caculate the distance between two zipcodes, I would have thought that would be what you want, then sort for the smallest number?

 

[rplohocky]

Well I am pretty much a novice at Access as well, but I would have *thought* that you would need to run a function against each zipcode to your zipcode and get the distance, then sort by that distance?
So you could twist the logic of that thread to get what you want?

I haven't looked at that thread in depth, but if it can caculate the distance between two zipcodes, I would have thought that would be what you want, then sort for the smallest number?

Right now I use an excel spreadsheet to do calculations. On one sheet I have a list of all zip codes in the country in one column and then the coords in the 2 columns next to it.. On another sheet I have my data which shows all the vehicles which includes addresses and zip codes. On the data sheet I use a vlookup function. The vlookup looks at the zip for a vehicle and then finds the zip in the zip code sheet and return the coords for that zip then looks at the zip in the combo box and instantly calculates the distance between each other. Very basic and cumbersome but it works. I am launching this to more then 50 people as a tool to use just to locate close vehicles, get some info of that vehicle then close it. I thought a Access DB would be a great method to distribute to everyone and I could update the data by running a new report from another system showing the latest available vehicles into the back end DB. I wouldn't have to send out the excel sheet every time its updated, they would just know the Access DB was current. Oh well, for now it looks like I'll have to do this in excel until someone can refer me to an example that I could modify.

 

[Gasman]

How does the vlookup find an adjacent zipcode?

If I had to do this I would try something like.
Get the latitude for the zip code entered,
Then I'd create a query that would look at every zipcode (including the one you already have) and with somesort of function return the distance between them and sort it by distance ascending.
Then with let's say the top 5 zipcodes, look for all the cars at those zipcodes.

I would build this bit by bit, testing each part.
I still believe if that thread can calculate distance between two zipcodes then you can use that logic or a modification thereof.

Alternatively, put the excel file on google docs and share with whoever, read only.