using vlookup and excel with access

shirkus

New Member
Joined
Aug 22, 2006
Messages
7
I have an excel spreadsheet that uses vlookup to search area codes and return a value based on the time zone that the area code is in. I then use the information in an access database. I would like to do the lookup in access but I can't figure out how? Can I vlookup an excel spreadsheet from access? Is there a much easier way to do this?

I would have no problem moving the area code data from excel to an access table if that would make it easier.

Can someone help?

Thanks in Advance....
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
What is the data going to look like in the table?
"AreaCode TimeZone" ???

Assuming this, you can do a DLookup in Access. Assuming the table name will be "tblAreaCode", here is the DLookup code to give you the TimeZone. EST, CST, MST, PST are my assumptions here.

TimeZone = DLookup("TimeZone", "tblAreaCode", "AreaCode=' " & myAreaCode & " ' ")

myAreaCode would be the variable that holds the area code you are looking for the time zone for. I'm also assuming the area code is a string, therefore the single quotes around the area code in the criteria parameter. For further information about DLookup, check the Help file within Access.
HTH,
 

shirkus

New Member
Joined
Aug 22, 2006
Messages
7
I'm confused. I have an area code table that looks like ACode TZone.
In my first table I have a ten diget phone #. In excel I used the Left command and VLookup to take the first 3 numbers and search a different sheet of area codes and return the time zone. I know excel a lot better than I know access so I'm sure I'm missing something obvious but I don't see DLookup can take an area code(Left 3 of phone number) from one table compare it to a list of area codes on another table(area codes) and return the time zone to the original table. Also do I right the code in the original table under a blank field called TZone or do I make a query.

I'd be happy to if I could dump all fields from one area code in the original table to a new table called lets say EST.

I really apreciate the help, I'm just starting with access

Thanks again
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

shirkus, the thread giacomo suggested might not really be a good idea to look at. Trust me, giacomo really knows what he is talking about, it's just that this thread is quite hard to follow in that the person he was helping wrote a lot of things that were hard to follow. Then again, it just might help, I'm not sure. I guess the idea I want to leave you with is this: If you find the thread hard to understand or confusing, don't worry, the tread is confusing and hard to understand.

Now, back to your situation. Need to know what you are trying to do with the lookup. Do you want to put the time zone into a table that will match the area code of the phone number in that record? Is this something that will be done while a form is being filled in? Do you want to do this on a batch basis within tables only? These answers will help us help you.
PLEASE CAREFULLY ANSWER ALL QUESTIONS. (There are 3) When only one or two questions are answered, we don't always get the full picture we are looking for. You know what you mean, but answering ALL the questions will help us know more about what you mean.
Thanks,
 

shirkus

New Member
Joined
Aug 22, 2006
Messages
7
I have a sql database from a website that imports among other things a phone number. It is the standard 10 digit number. I made a table in access that I import this information to. I have another table that has all of the US area codes in it with their appropriate time zone. I want to be able to do 1 of 2 things. I ideal solution would be to query the two tables and make a new table for each time zone which I could export to a csv file. I would be ok however with linking the 2 tables so that when the sql table talks to the area code table a field in the original table called lets say TZone gets filled with the correct time zone info. I think I have a good grasp of the Left command to take the first 3 digits of the phone number but I don't know if DLookup can do what VLookup does for me in excel.

I'm not really using a form because I'm constantly deleting the info from the table after I export it. The exported data needs to be space deliniated(sp?) thats why I use access. So any formula I use needs to be in one of the tables or in a query.

I think I answered all of your questions. Thank you for your help. I feel like the should be easy to do I'm just not that expereinced with access yet.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032

ADVERTISEMENT

So, your final outcome really is a .csv file, so a query that will link the two tables can be used with DoCmd.TransferText to create that .csv file.
The criteria row, for the AreaCode column from your AreaCode-TimeZone table would be:
tblTimeZone.AreaCode=Left([PhoneNumber],3)
That would take the place of the JOIN phrase.
HTH,
 

shirkus

New Member
Joined
Aug 22, 2006
Messages
7
I've not been able to make a query work with these two tables. I've never needed to query two tables before so can't say that I know what I'm doing.

Here's the deal my two tables are as follows

my tbInput has the following fields

phone (a ten digit string)
FName
LName
Address
City
State
Zip

My tbAreaCodes has

ACode
State
TZone
Comments

My two tables don't have a common link, so I don't know how to set up a relationship between the two. I tried using

tblTimeZone.AreaCode=Left([PhoneNumber],3)

changing the names to suit my tables but that didn't work. I'm sure I'm not setting the query up right, but I'm at a loss for what to do.

All I want is a query to search my tbInput and return all the fields that have an eastern time zone using the area code table as a source. I don't know how to do any of this. Once the query runs I can export it to a csv file no problem. I'm doing this now, I just have an extra step in excel.

Thanks for all of your patience with me and for the continued support.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Shirkus,
I don't really know how to explain it in words, so I felt I would just show you. Go to this link and you will see the sample data I entered for 4 people in various parts of the country, and 4 separate area codes and states. I used your field names and table names, then created a query that will use the two tables to produce the output I understand you want. I showed all three views of the query: SQL, Datasheet, and Design. Hope this makes it understandable. You might need to scroll down and to the side to see the whole thing.
 

shirkus

New Member
Joined
Aug 22, 2006
Messages
7
You Rock!

Thank you so much for all the help Vic. I have a bad tendancy to use the wizard when making a query. It won't let you use to tables unless they are linked by a common field. Once I fixed that everything worked like a charm. Those screen shots were above and beyond any expectations I had. I can't stress how helpful you've been. Thanks again!
 

Forum statistics

Threads
1,136,612
Messages
5,676,796
Members
419,651
Latest member
alexanderguhr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top