lookup function

[damiendad]

I'm trying to create a database that uses something similar to the vlookup function in excel. I need to lookup data based on 2 different criteria. Anyone have an idea?

 

[Calvin]

Welcome,

Use a combo box, if your toolbox wizards are turned on, when you add a combo box to the header of a form, a wizard will start and guide you through a basic lookup combo box that will filter records in your form when you select relevant criteria.

 

[damiendad]

I don't think I gave enough info the first time. If I have an excel spreadsheet like the one below:

1 2 3 4
5 2.3 3.3 4.4 5.5
6 3.3 4.4 5.5 6.6
7 4.4 5.5 6.6 7.7
8 5.5 6.6 7.7 8.8

I want a user to be able to enter 2 and 6 and access to return the value 4.4. I need to be able to lookup data based on 2 variables.

 

[damiendad]

I realize the spacing didn't turn out the way I expected it to. The 1 is supposed to be over the 2.3, the 2 over the 3.3, the 3 over the 4.4, and the 4 over the 5.5

 

[Calvin]

Ok, so add two combo boxes or add two text box and a 'Go' button to the header of a form.

You have to create a form to do this, you cannot do this in Table/Datasheet view like you can in an Excel Sheet, Access is a great deal more powerful than excel, and as a result some simple things in excel are a little more complicated in access to replicate, but the increased degree of control that access gives you more than makes up for it.

 

[damiendad]

I'm not following you. If I create a form, where does the data come from? Let me try to explain exactly what I'm doing. I ship a lot of UPS where I work. People also ask me for rates. UPS ships up to 150 lbs to 8 different zones across the US. There are different rates for for each zone in 1 lb. incriments. If someone asks me how much a 10 lb. box going to zone 4 costs, I want to be able to enter the data and get a price.

 

[Calvin]

I know exactly what you are talking about, but I'm telling you, you have to use a form. The form that you create will be tied/related/connected to the table of your choice and on the form you add combo boxes, list boxes, text boxes, radio buttons, check boxes, command buttons, the list of controls that you can add to form keeps going on, but you add what ever control is going to aid/speed your process of looking up or maintaining your data, but I'm telling you, to do what you want you have to use a form.
When you create a form you can use the Form Wizard which will guide you through the process of creating a basic form, connecting the the table to the controls on the form. After the basic form is created you can arrange, add, delete controls to suit your need.

If you have question on creating a form, use the Help file in access, it is going to be your best imediate resource, and lookup "Creating Forms", and when you've exhausted the Help file, we will be here for you.

 

[Jon K]

Calvin is right. As the field to look up varies with the zone number entered, you cannot do it with a query.

Instead you can do it on a form using the DLookup() function.

I have attached a demo DB using your sample data. You can open the form, enter a zone number and a weight, and click on the command button to look up the price.

The code is in the On Click Event of the command button:-
----------------------------
Private Sub cmdLookup_Click()
' if the zone number entered is out of range, display message and exit.
If Me.txtZone < 1 Or Me.txtZone > 4 Then
MsgBox "Zone number is between 1 and 4. Try again."
Me.txtZone.SetFocus
Exit Sub
End If

Me.txtPrice = DLookup("[Zone " & Me.txtZone & "]", "tblPriceLookup", "Weight=" & Me.txtWeight)
End Sub
----------------------------


(Note. To open the DB in Access 2000 or 2002, just choose Convert and save as a new name when the DB is opened for the first time.)

 

[damiendad]

Thanks for the sample db. It was a big help.