Convert VLOOKUP equations to Access, by Crystal (cc) VLOOKUP Week

In my last VLOOKUP Week video, I showed you how to make a Distance Table using the VLOOKUP function in Excel. Now, we build the same thing in Access -- will show you how to convert VLOOKUP equations to Access :)

Import tables from another database, DLookup, DMin, Query Critera and Calculated fields, VBA, Crosstab, Report Design, Align controls, Change widths, Format numbers, Page Layout, Detail Section, Colors.

~~~~~
LINKS

GetDistance Function for Latitudes and Longitudes
http://www.rogersaccesslibrary.com/fo...

Bill Jelen's YouTube channel:
http://www.YouTube.com/bjele123

My Reference Database
http://www.AccessMVP.com/strive4peace...

Learn VBA
http://www.AccessMVP.com/strive4peace...

Call to Excel Gurus: VLOOKUP Week
http://vlookupweek.wordpress.com/exce...

Switch from tabs to overlapping windows (2007)
http://office.microsoft.com/en-us/acc...

happy learning!

Warm Regards,
Crystal

*
(: have an awesome day :)
*

---

Closed Caption:

Wait, wait, one more, one more …
... the last VLOOKUP Week video
... this is tagging on to "Excel VLOOKUP to Calculate Distances using Latitude & Longitude".
VLOOKUP Week is a tradition started by Excel MVP Bill Jelen.
This is a Distance Chart I created in Excel between Airports in New York.
The distance between airports was calculated by my GetDistance function which needs Latitude and Longitude for 2 points and which works the same in Excel and in Access.
In Excel, a cell formula calls my GetDistance function.
Latitude and Longitude for each airport is determined using VLOOKUP functions.
The cell that is being looked up is distance between the Airport on Top (LookupKey) and the Airport on the left (criteria)
I go to the Lookup Table and see that Column 5 is Latitude and Column 6 is Longitude.
IATA_Lat_Long is a range name on the Airports sheet.
On this sheet Latitude and Longitude are also VLOOKUPs to the another sheet with Zip Code information from the 1999 US Census.
In this case, VLOOKUP is wrapped with IFERROR so if an error message is returned, nothing is displayed in the cell.
Airports are listed for the world, but Zips are only available for the United States since the data came from the 1999 US Census.
VLOOKUP is wrapped in the IFERROR function to suppress error messages when there is no match for a specified State & City.
In Excel, getting the formatting exactly as I wanted was easy since I can see each cell as I do the formatting.
The distance of airports closer than 50 miles is displayed in Red using Conditional Formatting.
So how would I do this same thing in Access?
I start by creating a blank Access database. I will call it VLOOKUP_in_Access_Distances
I Double-click to open it up. I have a blank database with nothing in it.
I will use the the same zip code reference and list of airports.
Both of these tables are available in an Access database you can download from my website.
From the ribbon, I click on the External Data tab.
I click Access since I will import tables from another Access database.
I can Browse or paste my path and then Browse
I get the Airports and Zip tables. I usually get the States table too, but I don't need it for this example.
I see the 2 tables in my database.
In the Navigation Pane, I change the Category to Object Type.
The Airports table shows the Airport code - Airport name - City - Country - and State for US and Canada. The Airports are all over the world so the State field is often blank.
The Zips table shows Zip code - City - State - County - Latitude - and Longitude for the United States.
In Excel, I filtered the Airports for New York. In Access, I will make a query that just shows airports in New York.
Create ribbon >> Query Design. From the Show Table dialog box, I click Add for Airports and then Close.

In order to see the dLookup equation I can right-click in the cell and choose Zoom from the shortcut menu.
I can also press SHIFT-F2 to Zoom.
Change the font to something bigger by clicking Font in the lower left corner -- and maybe something easier to read for equations.
You can see where all my quote markes are.
Notice that I have delimited the value that I am using from the field with quote marks as well.
I switch to Datasheet View and I see 3 cities that need to be edited so they can be found in my Zip code table.
As I change informaiton and move off the record Access automatically saves it and refreshes the record.
I can Save by clicking on the pencil as well.
dLookup gives me the information I want. I could get Latitude and Longitude the same way.
This method, however, does not take advantage of the power that Access gives us to relate data.
Drag the Zips table into the query from the Navigation Pane and resize the fieldlists so I can see everything.
Link on City by dragging City from one table to City in the other table. Do the same for State.
Now I will look at the Datasheet View.
I have an error message because the City field is in 2 places. I have an equation with city and Access doesn't know where to get it.
ok, I see, I see, I will fix this
I preface the City fieldname with Tablename and then DOT.
Since my criteria is coming from Airports I want my city to come from Airports also.
Now there are 703 records. This is a lot more records than we want.
We can limit these recrds by Grouping them. Click the totals button on the ribbon.
Now there are 19 records again.
The problem with this is that the zip codes are going to be arbitrary, which you may or may not see in a moment.
Instead of getting the Zip code from a DLookup function
lets see what the Zip code is from the Zips table.
When I run this again, now there are lots of combinations and the dLookup gets an arbitrary value everytime -- the first one it comes to.
In the zip code field, I see all the combinations of Zip code for each City & State.
I will limit the display of zip code to the minimum value for a specified City & State.
I go back to the Design View. I don't need the dLookup field anymore, but I will use these arguments.
I am highlighting the arguments and pressing Ctrl-C.
Under the Zip, in the criteria cell,
I will use dMin and paste the same arguments I had for dLookup.
I will look at the Datasheet View again.
There we go.
Now we can some consistency in the dLookup and dMin values and that is only because my Zip Codes table happens to be sorted by zip code.
I will go back to the Design View.
I don't need this dLookup column. I click in the little column header area to select the column and press DELETE.
I also want to get Latitude and Longitude.
I double-click each one of those.
Now I click Datasheet view to see the results. I see all 19 records.
I will go back to the design view and do a couple things to speed this up.
Instead of Grouping by Latitude and Longitude
I will have it take the first value it comes to.
Actually, what I think I want is for the GroupBy to go away.
Click off the totals button and see what we get without it.
There are 19 records.
I don't know that we gained any performance --
that dMin equation in there really slows things down
but it does make sure we get the lowest zip so the Excel and Access numbers compare
Ideally, we'd have an address for each airport and we could get the zip code from that.
I Save this query by right-clicking on the tab and choosing Save from the shortcut menu.
I'll call this qAirportsNY.
Now I Close this Query.
Look at the Excel sheet again:
There is a distance combination for every set of Airports.
Between every combination of airports, I am going to calculate a Distance.
On top of qAirportsNY
I will make what is called a "cartesian" query
which gives me all the combinations of every record.
In this case, I am going to Close the Show Table dialog box because
I will drag my fieldlist (qAirportsNY) twice from the Navigation Pane into the Query.
So this does not get confusing, I right-click on a fieldlist and show Properties so I can them Aliases.
The first Alias is Airport1.
The second Alias is Airport2.
I have one Airport listed down the left and one Airport listed across the top.
I need the Airport code - the Latitude - and the Longitude from each fieldlist.
I look at the Datseheet view of this information.
The data looks good. However I don't like these long labels at the tops of the columns;
that is because the field named Latitude is in both places
the field named Longitude is in both places
and the same with IATA.
I will specifically label these (columns).
This one will be Lat1:
Now press TAB to go to the next field then F2 to EDIT then HOME to go to the beginning
Now I have labeled all my columns so they will be easier to tell apart
and shorter names which is good since I will be typing them.
I am going to create another query using the Query Wizard.
Crosstab Query Wizard
Switch the View to Queries
choose the Cartesian query for the source.
In the Row Header is the first airport code (IATA).
In the Column Header is the second airport code (IATA2).
It doesn't really matter what goes in the middle because we are going to change it.
don't need to add things up (Uncheck the box for totals)
Go to the Next dialog box
and here is where I can give my query a better name
I will just shorten it, and view the query.
(there it is) Go to the Design View.
I am going to change what goes in the middle.
First I Best-Fit all the columns on the grid.
Here in the Field cell I am going to write an equation to calculate distance
(I need the code to calculate distance) so I'll grab my code and copy it.
In Access, I press Alt-F11 to go to the Visual Basic Editor (VBE)
From the menu --> Insert, Module …
I switch to the window where I have the code and copy it. Now I go to the module and) Paste my GetDistance function then Debug Compile
I copy the name of the function (GetDistance) so I can give the module a good name (mod_GetDistance)
Now I save and my function is ready to use.
GetDistance needs 2 sets of coordinates:
a Latitude and Longitude for the first coordinate
and then another Latitude and another Longitude.
Optionally I could change whether the distance is reported in statute miles or kilometers or natutical miles
I will leave it set to the default.
I save and my funciton is ready to use.
In this field, I am going to get the distance. Here is where I had to use a bunch of VLOOKUPS in Excel. In Acess they are all right here.
I will save this again before I look at it.
Oh! I don't want to Count my value. I will use First.
I save again. I always like to save everything before I actually look at it.
Switch to the Datasheet View and see how the numbers compare to Excel;
a few more decimal places than I've got in Excel, but the numbers look like they are the same.
What I can't do with a query is make everything all pretty.
I am going to have to define a report.
Rather than changing the format of the numbers here, I'll do it all when I set up a report.
I select my Crosstab query
and close the other tables that are open.
I am going to Create a regular report -- whatever the wizard is going to give me because I'm going to change it.
This is what my report looks like out of the box in Access.
I need to format the numbers so they don't show any decimal places.
I also need to color the rows and that is going to be a little tricky.
In Excel, I can just look at the rows and see every 4th one is going to be dark orange, and I colored them.
I used the Format Painter a lot.
In Access, I don't see the report until it is done so I have to think of another way to do it.
I can use a hidden control to count the rows, and then I can test to see if I am on an even row, which will always be white,
or if I am on an odd row, it will be one of various colors.
I am right-clicking on the tab and choosing Design View.
You can also use the View drop-down on the ribbon -- if your default is not Tabs, you can switch views easily
In the Design View, I collapse the Navigation Pane (to see more)
(mouse in vertical ruler as I click and drag) I select all the controls that my imaginary line touches
They don't need to be so wide.
On the Property Sheet, I change the Width to 0.65 for all of them.
I deselect (controls) by Shift-clicking them out
These are my numbers.
I want to Format my numbers to be Standard with 0 Decimal Places.
I am also going to drag my Page Number (control) to the top (Report Header section)
Then I'l get rid of anything else that is in the Page Footer section.
Then I try to close it up. It closed up.
In the Report footer (section) I don't need anything either (Delete stuff).
I"ll close this section up as well.
This is only going to be a one-page report. I could have just not had a Page Number at all.
I'll delete the little picture that Access gave us.
Edit the (text in the report) header (label)
I'll drop the Size down to 14 and make it Bold,
double-click one of the handles to do a best-fit and then stretch it out a bit more since not everyone's screen renders things the same.
Down here, I have Grouped controls -- they go all the way out.
Sometimes it can be hard to grab this border but you can grab the right edge (of the report) and drag it back (to resize it).
I need a little more space because if I print on Legal paper
go to Page Setup. On Page Layout
I am definitely going to need Landscape
Instead of Letter, I will use Legal (paper) which is 14 (inches).
As you can see, I am going out already very close to 14. I need some space for margins.
I am going to click (on my Grouped controls in the upper left) where I have the 4-headed arrow (to select them)
On the Arrange Ribbon, I (click Remove) am going to Remove the grouping
I can tighten these controls up.
Select them all,
right-click on them and choose Align >> Left
I use these alignment buttons a lot so I put them on my quick Access Toolbar (QAT).
Then I drag the controls over as far to the Left as they can go.
I click where the Rulers intersect (upper left) to select the Report itself
On the Property sheet, in the Width Property, you can just set it to a low number.
Access will say, "hey I can't go that tiny, but I can do this." so it will bump the value down as small as it will go.
I'll do a little more cleanup here.
I select all of these (controls in Page Header and Detail sections)
(Paragraph) Right-Align them (since they are numbers).
Bold all the labels.
Here (Airport code in the detail section)
I only bold this one because I only see this one row and that is why the formatting can get tricky.
I am going to set the Top property of those (controls in the Detail Section) to be 0.
I tighten this up as much as I can.
I also slide the detail height up.
I need some way to know which row I am on.
On the Design ribbon, I click the Textbox tool.
For now, I will make it (textbox) up here (in the report header section) since I have some space to work with.
I don't need the associated label, so I can delete that.
this is not going to need to be so big (I drag the right handle to make it narrower).
I will call this (Name property) RowCounter.
I know there are people out there cringing who want me to start the Name with "txt", and sometimes I do.
On the Data tab of the Property sheet, I set the ControlSource equal to 1.
for the RunningSum property, I choose OverAll.
this (control) is what is going to number all my rows for me.
In order for it to work right, though, it has to be in the section that it is numbering so I'll put it down here (in the Detail section).
I need to make it invisible because I really don't want it showing up.
I double-click YES to change it to NO for the Visible property on the Format (tab of the Property sheet)
When I do that I also change the color so I know that is something the users aren't seeing.
I save this (name = rpt_DistanceTable_Airports_NY)
I color every other label a light blue (actually it is aqua) to match the color that I am using on my Excel sheet.
I'll take a guess (at the shade) -- there we go.
(select the airport code in the detail section) I will pick a light orange.
Actually
it doesn't really matter because that is going to need to change -- sometimes it is white and somtimes it is a medium orange and sometimes it is a lighter orange.
This is where I am going to need to test this RowCounter to see where I am.
On these cells (controls) that are going to be blue -- I'll just pick all of them -- I need some way of telling them apart,
so I am going to use the TAG property. these are (all) blue.
Now, you guessed it, I am clicking and shift-clicking to select every other control (and I'll also get the label) -- these are going to be TAGged with orange.
On the format of the detail section is where I will do all the coloring.
I select the Detail section bar.
I can also click in the Detail section where there isn't anything else but that is a little difficult right now
so the Section bar is a good place to Click.
(On the Property sheet, in the) OnFormat event I click on the Builder button … and choose Code Builder.
Good practice is to put a time-date stamp. I use yymmdd.
Another good practice is to put an error handler in
but just to make this is a little simpler to read I am not going to.
I am going to DIMension a variable (ctl) that I can use as a way of flipping through all the controls in my section [ Dim ctrl As control ] so I can color them.
Depending on what the Row is, I want to do something different.
I am going to do a SELECT CASE
Here I am going to be testing different things s I will get the first thing that is TRUE [ Select Case True ]
Case Me.RowCounter Mod 2 = 0 'means this row (is an even number) and is going to be white in the case of the "orange" rows or it is going to be a very pale blue in case of the the other rows.
If we are on white
For Each ctl in Me.Detail.Controls
do this -- first lets see
If ctl.Tag = "orange Then
ctl.Backcolor -- and you can see that Intellisense is not prompting me for Backcolor so here is a little trick I do
I type another control (Name) to get Intellisense to prompt me for BackColor
= RGB(255, 255, 255) 'white
Now I'll change the control (reference) back to ctl (instead of Me.Controlname) now that I know I have the syntax right.
ElseIf ctl.Tag = "blue" then
I love the way Access tells you very quickly that you screwed up.
Here we are going to pick a very pale blue (actually aqua') =RGB(245, 250, 250)
255 is as high as you can go.
then End If and Next ctl.
Basically, we loop through all the controls looking at the Tag and if it is orange
in this case we will set the BackColor to white.
If the Tag is blue, the BackColor will be a very light blue.
Now I highlight that (code) and ctrl-Drag it --make sure I am still holding Ctrl when I let go of the mouse (to make a Copy)
The next test I will do is for the medium colors.
I will use Mod 4 and look for a remainder of 1
Case Me.RowCounter Mod 4 = 1 'medium
medium orange = RGB(250, 200, 170)
medium aqua = RGB(168, 216, 224)
These are Red-Green-blue values. They go from 0 (dark) to 255 (light).
there is only one row situation left so I will use CASE ELSE 'light
light orange = RGB(255, 232, 218)
medium aqua = RGB(200, 230, 230)
the other thing we need to do after End Select (balance like parentheses) is loop through all the controls and see if we need to make the text red (Conditional formatting in Excel)
My test is going to be
If ctl.Value <= 50 Then
ctl.ForeColor = RGB(255, 0, 0) 'all red - no green - no blue
ctl.FontBold = True
Now I will copy these 2 lines and change them for Else
ctl.ForeColor = RGB(0, 0, 0) 'black
ctl.FontBold = False
Now I choose Debug >> Compile
(stops on clt -- should be) ctl
If you spell these things wrong you get told and get a chance to fix them (Option Explicit)
Debug >> Compile ... same problem -- I fix it to ctl
Debug >> Compile ... click OK. Now I will do Ctrl-H (Replace). Find what --> clt .Replace with --> ctl. Search --> Current Procedure. Replace All.
since I screwed up in a lot of places
Debug >> Compile ... (nothing happens so it is OK)
I Save it.
Moment of truth ... is it going to work?
Go to the Print Preview
We need borders -- have to go back -- have to do borders to make it look more close.
also I take out the extra label in the top upper left corner.
In Design View I will select all of these (controls in the Detail section) at once (by clicking in the vertical ruler) and make the border orange.
Get rid of this extra label because Access doesn't need it.
Save.
I will take a look at the Print Preview.
This should look much better.
Here we are -- Access and Excel doing the same thing. The formatting is a little different but it looks pretty close. �