coding equivalent to match

[JuniorWoodchuck24]

Curious what the coding would look like for an equivalent to the "match" function in Excel. I have a table for MaximumAllowableStresses for metals, and based on what the user selects as a metal and temperature it looks up both in a table and gets the value needed. The table for MaximumAllowableStresses is:

ID Temperature 316L 304
1 500 10900 12900
2 450 11300 13350

and so forth. What I want the code to do is go in and find the temperature based on what the user inputs and finds the MAS for the metal.

User inputs 460 as temperature and 316L as metal: answer 10900
User inputs 450 as temperature and 304 as metal: answer 13350

Thanks,

 

[JANR]

Basically what you ar looking for is called DLOOKUP which uses this syntax:

SomeVariabel = Dlookup("Field", "Domain", "Criteria")

Domain can either be a query or a table.

In your example it wil be something like this:

SomeVar = Dlookup("Answer" , "Tbl/Query" , "[Temp] = " & Me.InputTemp & " AND "[Material] = '" & Me.InputMaterial & "')

The Me. Part is a refrence to a formcontrol, please note the quotes around InputTemp and InputMaterial. Since InputTemp is a NUMBER you use " " around the expression. InputMaterial is basically a textfield so you must wrap it with single and normal quotes eg. '" ... "'

BUT

The table exemple you got isen't normalized, you shoulden't have the different materials as seperate columms, this is a classic many-to-many situation. To resolve this you must have 3 tables linked 1-many in a junctiontable, which you could create a query as a base for your lookup.

TableTemp:
-TempID
-Temp

TableMaterial
-MaterialID
-Material

TableJunction
-ID (optional)
-FK TempID (Taken from the Temptable)
-FK MAterialID ( Taken from the materialTable)
-Results (Answer you want to look up)

Hope this helps

JR

 

[JuniorWoodchuck24]

JANR thanks for the help and I understand the coding and other tips. My major concern is how the coding will act with the following:

InputTemp = 500 (500 is one of the temps in the temp table)
This will work and get the value

InputTemp = 505 (this is suppossed to go to the next value 550)
With temp seeking an equivalent value this will more than likely result in nothing or an error. Is there a function in Access07 that allows you to move to the next number? I guess I could always define all the temps as variables in VBA code and do the following:

Dim temp


If InputTemp <= 550 AND InputTemp > 500
temp = 550
ElseIf InputTemp <=500 AND InputTemp > 450
temp = 500

and then in the dlookup criteria set it equal to temp and so on

 

[JANR]

I would let the junction table handle all possible combination, as I understand EACH materialtype has ONE temprature which the material get to maximum stress.

So in the temprature table you will have all temperature ranges regardless of material and in the material table you will have all the material you are testing.

In the junction table each record will have ONE material ONE temprature and ONE Stressresults, it is the combination which will be uniqe.

JR

 

[JuniorWoodchuck24]

I got it to work with a "unique" coding design, which I highly doubt is anywhere remotely close to the "best" method. What is the data type for the "Results" field you're referring to?

I understand how you link temp/material through it's primary key to the junction table. The only think I'm a bit confused about is how the "Results" field works. I'm assuming that the "Results" field does the following:

Takes InputTemp and pulls it into table and based on criteria places it in correct spot with direct relation to temp/material.

 

[JANR]

I can wip together a samplebase for you to look at, just give me a sec.

JR

 

[JANR]

See attached DB.

Look at the relationship and settup of the junction table. I'v created a composit key on TempID and MaterialID to prevent duplicates.

Also 2 forms frmNewTests and frmSearchStress

frmNewTest is to enter new combinations in the junction table, this needs more work to populate the lookup tables tblTemp and tblMaterial, you can use the NotInList_Event of both comboboxes to add to these tables when a new temperature or a new material is added to the DB.

The form itself is bound to the junction table.

The form frmSearchStress is just an exemple on how to query the junction table using Dlookup. Have a look at the VBA behind the form for some pointers.

JR