Dlookup zip codes in two different tables

[rplohocky]

I am trying to write a basic dlookup expression in a query. The lookup will look at

=DLookUp("[GARAGING ZIP / POSTAL CODE]","[tbl Idle Vehicles]",[GARAGING ZIP / POSTAL CODE]=[zip_code],[tbl Zip Code Data])

In the query it will look in [tbl Idle Vehicles] in column [GARAGING ZIP / POSTAL CODE] to match what's in [tbl Zip Code Data] in column [zip_code].
This seems to be an easy lookup but I can't get this work. I have syntax errors every time I save it. Please, any help would be greatly appreciated!

 

[jdraw]

What exactly are you trying to do in plain English?
Forget dlookups etc until we understand what you are trying to accomplish in simple, plain English.

As for Access, many will advise you to not include embedded spaces or special characters in field and object names.

 

[Gasman]

A dlookup can only look in one table.
Also if the looked up value is text, it needs to be surrounded by single quotes.
You also have to build the string criteria
for numeric

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

for string

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

The last has been changed to hopefully the correct syntax, as I could not find any of mine that have text criteria.

HTH

 

[CJ_London]

you should not use domain functions in queries, they are extremely slow.

You should join your [tbl Idle Vehicles] to [tbl Zip Code Data] on [GARAGING ZIP / POSTAL CODE] to [zip_code].

note that spaces and non alpha numeric characters should not be used in table and field names. For fields, use the caption property, that is what it is for. If you need spaces to make the name more readable use the underscore instead.

When you come to write vba code, you will generally need to use the underscore to indicate a space because vba does not allow spaces.

e.g. if you have a field called 'zip code' any event associated with it will use 'zip_code'.

 

[rplohocky]

What exactly are you trying to do in plain English?
Forget dlookups etc until we understand what you are trying to accomplish in simple, plain English.

As for Access, many will advise you to not include embedded spaces or special characters in field and object names.

I will try to explain what I want to do. I am trying to build a basic data base that will keep track of a fleet of vehicles. These vehicles are sitting idle around the country. When I need to locate a vehicle in say Nebraska (zip code XXXXX) I want to open the DB and from a drop down select a zip code of where I need a vehicle and the DB would generate a query that would show the nearest vehicle to that zip code. I have a imported table with every zip code in the country,which is about 42,000. Each zip code has, in the column next to it, Longitude and Latitude coordinates and I linked the table with all the vehicles I have. I linked this table because the vehicles are always changing locations and are being assigned and remove from the list so each week I export an updated list from another database. I want to make it easy to simple replace the database in a folder by keeping the same name. Most people don't know how to upload or merge a DB so this method is the easiest way I could think of. They just download and save it to a file and everything else works when they go into the Access DB.

 

[jdraw]

It seems that you want to find the vehicle nearest to some identified zipcode.
So you have a " I need a vehicle at Point X" and " which vehicle is closest to point X"

See my remarks in the Distance between zipcodes (or lat,longs or any combination)
https://www.access-programmers.co.uk/forums/showthread.php?t=225339

 

[rplohocky]

It seems that you want to find the vehicle nearest to some identified zipcode.
So you have a " I need a vehicle at Point X" and " which vehicle is closest to point X"

See my remarks in the Distance between zipcodes (or lat,longs or any combination)
https://www.access-programmers.co.uk/forums/showthread.php?t=225339

Based on the DB you had in that other post, that is based on inserting 2 zip codes. I need something that I can type in 1 zip for where I need a vehicle and it will go through the vehicle DB, which has complete info on each vehicle including a zip of its location, and trigger a query that will show every vehicle in order of how many mile away each vehicle is from the zip I entered. I have a table in the DB with all U.S. zip codes and columns next to them with coordinates. I built a macro in excel to sort the data with lowest miles from inserted zip to the top. One page has a vlookup that calculates against the inserted zip code. Pretty basic but it works. I'm trying to build a DB that can be easily updated by saving the "new updated report" pulled each week.

 

[jdraw]

The code in the post we mentioned is applicable to your issue.

If, as you say, you have a db of vehicles with their current position (lat/long or postal code/zip), and you need a vehicle at X,Y (lat/long), then you can determine which vehicle lat/long has the minimum distance from X,Y. So you identify the X,Y and process the database to find the record where the distance from x,y to the vehicle lat/long is shortest/minimum.

Years ago, I was asked to create a program to do the following with these parameters:

-we had 50,000 companies and for each company a list of their products and services
-each company's postal code, address was known
-we also had a table that could convert postal code to approximate lat/long

now the key question:

If we had an emergency (fire, bombing, major disaster of any sort) at point X,Y (lat/long), and we could identify generally the products and/or services to assist/reduce the emergency, which companies
within W (10, 40, 60, 80,..) miles of X,Y could be called upon?

Seems similar in concept to your issue. You have the vehicle coordinates and you will know x,y, so which vehicle is closest to ( shortest distance from) x,y.

You may get more ideas by reading through this thread.
https://access-programmers.co.uk/forums/showthread.php?t=292811

Good luck.

 

[rplohocky]

The code in the post we mentioned is applicable to your issue.

If, as you say, you have a db of vehicles with their current position (lat/long or postal code/zip), and you need a vehicle at X,Y (lat/long), then you can determine which vehicle lat/long has the minimum distance from X,Y. So you identify the X,Y and process the database to find the record where the distance from x,y to the vehicle lat/long is shortest/minimum.

Years ago, I was asked to create a program to do the following with these parameters:

-we had 50,000 companies and for each company a list of their products and services
-each company's postal code, address was known
-we also had a table that could convert postal code to approximate lat/long

now the key question:

If we had an emergency (fire, bombing, major disaster of any sort) at point X,Y (lat/long), and we could identify generally the products and/or services to assist/reduce the emergency, which companies
within W (10, 40, 60, 80,..) miles of X,Y could be called upon?

Seems similar in concept to your issue. You have the vehicle coordinates and you will know x,y, so which vehicle is closest to ( shortest distance from) x,y.

You may get more ideas by reading through this thread.
https://access-programmers.co.uk/forums/showthread.php?t=292811

Good luck.

I opened that DB in your post reference. It's an incredible tool! Maybe I'm missing it but I don't see how that would work for my scenario. That DB wants 2 zip codes to determine distance. I need to have 1 field that I can insert a zip code for where the vehicle is needed, then have a query or report pop up to show a list of vehicles sorted from lowest miles to highest. The macro or formula should look at the zip I entered and look at each record of each vehicle and bring the lowest mile records to the top of the query or report.

Am I missing something here or does this DB do that with some manipulation?

BTW I really appreciate your help and patience in helping me!

 

[jdraw]

why do you need all the vehicles? If you need someone at X,Y, why do you need to report all vehicles?

Consider this scenario:

You have a current list of vehicle and their lat/long/zipcode

Vehicle ZipCode LatLong

A 68103
B 33461
C 85326
D 91724

I need a vehicle at 33428

So you find the distance between zip codes

33428 vs 68103
33428 vs 33461
.....

And you pick the one with minimal/shortest distance.

You can do this with a Form and a textbox(where you place the zipcode/lat/long needed)

After the entry is made you invoke a procedure to find the zip where distance is shortest
and display that vehicle and its zip.

 

[rplohocky]

why do you need all the vehicles? If you need someone at X,Y, why do you need to report all vehicles?

I need to see what vehicles are the closest to the location where the customer needs a vehicle and then also choose a certain type of vehicle. Some times a car is needed and other times a pickup or semi truck. What works best is to see
the closest vehicle at the top of the list and then I can scroll down to see which vehicle will work for them. My hope is build this portion, make this work, then add additional peremeters such as model or year. I thought I would get this to work first though.

 

[jdraw]

Well that is a manual approach, but if you want the computer to do the hard work, I'd suggest a form.

1.Enter/select the vehicle type required.
2. Enter the zipcode or lat long where vehicles is needed.

Programmatically, select/vehicle type matching required type.
Then, for each selected vehicle(by type) calculate the shortest distance between vehicle's current location and required lat/long/zip.
Display the vehicle and its location etc.


Who uses these values/zips etc? How often is the lat long of each vehicle recorded?

It might help if you gave us a clear, simple English description of what you're really trying to accomplish.

Suppose you need a car at 42.8666° N, 106.3131° W. What do you do now -step by step?

 

[rplohocky]

Well that is a manual approach, but if you want the computer to do the hard work, I'd suggest a form.

1.Enter/select the vehicle type required.
2. Enter the zipcode or lat long where vehicles is needed.

Programmatically, select/vehicle type matching required type.
Then, for each selected vehicle(by type) calculate the shortest distance between vehicle's current location and required lat/long/zip.
Display the vehicle and its location etc.


Who uses these values/zips etc? How often is the lat long of each vehicle recorded?

It might help if you gave us a clear, simple English description of what you're really trying to accomplish.

Suppose you need a car at 42.8666° N, 106.3131° W. What do you do now -step by step?

Who uses this? A customer calls and asks for a vehicle. I would look to see what vehicle is the closest to them with this tool. I would verbally share what have for options to the customer. Once they chose a vehicle I would basically take the VIN number and go to another system I use and send that vehicle to them. This DB would be updated with a new spreadsheet weekly and only used to see where and how far any vehicles are from where the customer is.

 

[jdraw]

Perhaps I can help you with a small demo. It seems you are not very familiar with database or Access.

I will need some sample info from you. But I can concoct a few things to help flesh things out.

You have vehicles, vehicle types and vehicle locations. You also have Customers who need some sort of vehicle at some location presumably on some Date and Time. There may be more detail than that and if so please describe same in your next post.

Can you supply some vehicle, type and location info?
What version of Access do you have?

 

[jdraw]

I am attaching a demo based loosely on the requirements stated by rplohocky. Since he/she did not provide specifics, I concocted a set up with 3800 vehicles covering 5 basic vehicle types. The current location of each vehicle was randomly selected and assigned based on a file found on the internet of some 42,000+ City State Zip records each with Lat/Long coordinates.
The demo is in Access 2010.
The scenario: You get a call requesting a VehicleType at some specific Zip or Lat/Long. You interrogate the database to find the vehicle of the required type that is closest to the requested location. The RequestedLocation(R) is where the vehicle is needed, the closest available vehicle (A) is identified. You can display a Google map showing markers for (R) and (A) .

As mentioned, it is based on the concept of needing a vehicle at point X, and having a variety of vehicles currently located at know Lat/Long positions around the USA.

Hope its helpful to someone.

 

[Pat Hartman]

I have to admit I didn't read the entire thread. It seemed to get off track and I didn't see anyone correct your syntax so here goes:


=DLookUp("[GARAGING ZIP / POSTAL CODE]","[tbl Idle Vehicles]", "[GARAGING ZIP / POSTAL CODE]= ' & Me.[zip_code] & "'")

The assumptions I made were that the zip code you wanted to look up was on the current form in a field named [Zip_code] and that the zip code is stored as a string as it should be.

 

[rplohocky]

I am attaching a demo based loosely on the requirements stated by rplohocky. Since he/she did not provide specifics, I concocted a set up with 3800 vehicles covering 5 basic vehicle types. The current location of each vehicle was randomly selected and assigned based on a file found on the internet of some 42,000+ City State Zip records each with Lat/Long coordinates.
The demo is in Access 2010.
The scenario: You get a call requesting a VehicleType at some specific Zip or Lat/Long. You interrogate the database to find the vehicle of the required type that is closest to the requested location. The RequestedLocation(R) is where the vehicle is needed, the closest available vehicle (A) is identified. You can display a Google map showing markers for (R) and (A) .

As mentioned, it is based on the concept of needing a vehicle at point X, and having a variety of vehicles currently located at know Lat/Long positions around the USA.

Hope its helpful to someone.

jdraw,
Thank you for building that DB! It has been the closest locator tool I've seen so far. That said, I attached a sample file that I need to load into the DB every Monday. The vehicles are constantly changing and the data in the DB must change with it. This DB will just be used to locate the closest vehicle to a zip code then I would take the VIN number and search for it in another system I use. Please look at the spreadsheet I attached and let me know how I can quickly upload/replace the data each week and please tell me which table I would upload to.

 

[jdraw]

I still do not understand the requirement in plain English (business) terms and context. Also, I am not an Excel person. I do see your spreadsheet, but I don't really know what the fields represent. The most confusing (to me) is that you seem to have a number of disjointed systems that you have to feed data into manually to get some value(s) that you use in your daily business. Perhaps you could explain/describe the data and how your business processes use this data and to what result specifically.

Has anyone stepped back to identify the "big picture" requirements? You are probably trying to solve an issue specific to you/your job, but there seems to be several related factors that are not being described or considered.

If you are trying to simplify/streamline some recurring business activity(ies), then identify the pieces and how they fit together in simple terms(plain English). What would the ideal situation look like? Then determine what exists and what is missing and what priorities are involved. You don't have to solve it all at once, but at least you will know the business; how things fit in business terms; an identified path (plan/priority) for future work.

If you provide more details, I'll help with a solution, but it would help if you could give a description of what your business process is in plain English and a sample scenario -step by step.

Please look at the spreadsheet I attached and let me know how I can quickly upload/replace the data each week and please tell me which table I would upload to.

I would not upload the spreadsheet as it is. It refers to several things (not set up for database as it is) and needs to be understood in terms of your business.
You have 5 Levels of something, some vehicle types are null, some numeric fields contain alphabetics...

My demo database was based on what I gleaned from your earlier posts. To adjust or adapt it will require specifics of what you really need and/or what exactly is missing from the demo.

Good luck.

You can link an excel sheet to Access as shown in the attachment. Linking may be easier than importing??? Depends on needs and business processes involved.

 

[rplohocky]

I still do not understand the requirement in plain English (business) terms and context. Also, I am not an Excel person. I do see your spreadsheet, but I don't really know what the fields represent. The most confusing (to me) is that you seem to have a number of disjointed systems that you have to feed data into manually to get some value(s) that you use in your daily business. Perhaps you could explain/describe the data and how your business processes use this data and to what result specifically.

Has anyone stepped back to identify the "big picture" requirements? You are probably trying to solve an issue specific to you/your job, but there seems to be several related factors that are not being described or considered.

If you are trying to simplify/streamline some recurring business activity(ies), then identify the pieces and how they fit together in simple terms(plain English). What would the ideal situation look like? Then determine what exists and what is missing and what priorities are involved. You don't have to solve it all at once, but at least you will know the business; how things fit in business terms; an identified path (plan/priority) for future work.

If you provide more details, I'll help with a solution, but it would help if you could give a description of what your business process is in plain English and a sample scenario -step by step.

I would not upload the spreadsheet as it is. It refers to several things (not set up for database as it is) and needs to be understood in terms of your business.
You have 5 Levels of something, some vehicle types are null, some numeric fields contain alphabetics...

My demo database was based on what I gleaned from your earlier posts. To adjust or adapt it will require specifics of what you really need and/or what exactly is missing from the demo.

Good luck.

You can link an excel sheet to Access as shown in the attachment. Linking may be easier than importing??? Depends on needs and business processes involved.

Hello jdraw,
To answer your question about stepping back and really seeing what the bigger picture is, the answer is yes. We all know we have a bigger problem with the process flow. I am tasked with trying to remove some bumps in the road and still try to kepp things moving.

I created a spreadsheet in excel to do exactly what I need to locate the vehicles around the country but I am trying to create a DB that will make it easier for everyone to use the tool. The spreadsheet works fine but I think a access DB will far out perform a spreadsheet in the long run. Also I would be able to send out one email, to everyone, with the front end DB and I will be able to update the back end without any disruption. Access will allow to drill down based on what kind of search each person will need.

I attached the spreadsheet I built to show you what I use now. It has some macros in it as well. You can choose a zip code, hit search and it will sort the data in the middle to lower part of the page and show every vehicles info. The sort shows the closest vehicle first then the next and so on.

Yes I know that we should have some internal system that should be connected with our data and produce what this tool does but for now that's not going to happen.

 

[jdraw]

Zip codes are not/should not be stored as numeric data type. (you will not do any arithmetic with a zip code)
Some of your zip codes have no lat/long data (eg 76209, 76210)
The zips seem/may be the same file I found and used in my demo. I had to remove some where there was no lat/long.
I think you misunderstood my point on building a big picture of your business and what it could like.
Start with a picture that you all can agree on; identify priorities; map out some plan....

We all know we have a bigger problem with the process flow

. What is the company's plan to identify/evaluate/correct the recognized problem? I realize it may not be yours to solve, but someone/group in the organization must be looking to the future and current business processes.
Adding a partial Access/database solution/tool to the mix is extremely short term at best.
But you and your management know the situation better than any reader, so act accordingly.

Here is a link to more info on database concepts an planning.

Good luck.