DLookup with variable for expr

[ClearwaterDiver]

I have read as many of the posts on this and similar subjects and still having an issue. Heck, maybe I shouldn't even be using DLookup. After all, I just want to give the 'x and y' of an array and return the result!

background -

I have a table in my database that is a temperature correction chart that I am legally bound to use, no formula is approved. The data is literally from a scanned PDF from 1913... (google: ttb table 1 ) The table is 100 fields wide (1-100 degrees F) with 206 or so rows that correlate from 1 to 206 alcohol proof (indicated)

The field header names ARE numbers, but I cannot see changing them. I see where @The_Doc_Man said he had not seen all-numeric fields in 25 years, I might just have a candidate!

The key column is the 1-206 numbers that correlate to observed proof. (there is a section of data intentionally missing from the for below 31 degrees and 76 proof. Likely this is because ice would form and gauging would be inaccurate)

WHAT I HAVE:
I have a Table and a Form for a 'proofing operation' where I type in temperature and proof to one decimal point, I have a working simple operation that reads calibration correction factor for the hydrometer and thermometer based on me selecting the serial number of the instrument form a drop-down combo box and it fills both the serial# and correction factor fields based on a simple VB script for 'AfterUpdate' on the serial number field. This took me a few hours to get working but this site helped my out immensely!

The next step after getting the corrected values is to do temperature compensation via my large table "tempCorrection". This table is only whole degrees and whole proof, so double interpolation is needed, which takes four lookups. I am using DLookup(), and as a side note, I have this working fine an an excel spreadsheet with just Lookup() and Indirect(), but want to roll it into my database.

If I put a variable in for the 'expr' in DLookup( expr, domain [, criteria] ), it works as long as that variable is statically defined.



This version works since I have the 'expr' variables Temp and Temp1 statically defined at 40 and 41 degrees, respectively, so this only works for 40.0 to 40.99 degrees, which is obviously not much of a help. (FYI, the variables for 'domain' and 'criteria' work):

Private Sub Hydrometer_Serial__AfterUpdate()
    Me.[Hydrometer Correction] = Me.[Hydrometer serial#].Column(1)
    
    Dim strTable As String
    Dim Proof As String
    Dim Proof1 As String
    Dim cProof As Double
    Dim cProof1 As Double
    Dim Temp As Variant
    Dim Temp1 As Variant
    Dim cTemp As Double
    Dim cTemp1 As Double

    strTable = "tempCorrection"
    Proof = "key = " + Str(Fix(Val([Actual Proof])))
    Proof1 = "key = " + Str(Fix(Val([Actual Proof] + 1)))
    Temp = "[40]"
    Temp1 = "[41]"
    cProof = DLookup(Temp, strTable, Proof)
    cProof1 = DLookup(Temp, strTable, Proof1)
    cTemp = DLookup(Temp, strTable, Proof)
    cTemp1 = DLookup(Temp1, strTable, Proof)
    Me.[Interpolated Proof] = cProof + (Me.[Actual Proof] - Fix(Val([Actual Proof]))) * (cProof1 - cProof) + ((Me.[Actual Temp] - Fix(Val([Actual Temp]))) * (cTemp1 - cTemp))
    
    
    

End Sub

WHAT I AM STUCK WITH:

I need to have Temp and Temp1 be taken from the Form as variables (val to make it a value if it is not, Fix is to round down, and str to make it back to a string to hand to DLookup() as the expr field

Temp = Str(Fix(Val(Me.[Actual Temp])))

I spent about 8 hours now researching and trying different things.
Thinks I have tried:

DOES NOT WORK:
    Temp = """ & Chr(91) & Str(Fix(Val(Me.[Actual Temp]))) & Chr(93) & """

DOES NOT WORK:
    Temp = Chr(34) & Chr(91) & Str(Fix(Val(Me.[Actual Temp]))) & Chr(93) & Chr(34)

DOES NOT WORK (takes value, not field name)
    Temp = Str(Fix(Val(Me.[Actual Temp])))

DOES NOT WORK (error message 39)
    Temp = Chr(91) & Str(Fix(Val(Me.[Actual Temp]))) & Chr(93)

DOES NOT WORK (runtime error 2471 error message 39)
    Temp = "[" + Str(Fix(Val(Me.[Actual Temp]))) + "]"

ERROR
    Temp = '[' & Str(Fix(Val(Me.[Actual Temp]))) & ']'

DOES NOT WORK (runtime error 2471 error message 39)
    Temp = "[" & Str(Fix(Val(Me.[Actual Temp]))) & "]"

DOES NOT WORK (runtime error 2471 error message 39)
    Temp = "[" + Str(Fix(Val(Me.[Actual Temp]))) + "]"

DOES NOT WORK (runtime error 2465 cant find |1 )
    Temp = [Str(Fix(Val(Me.[Actual Temp])))]

unexpected end of statement
    Temp = ""[Str(Fix(Val(Me.[Actual Temp])))]""

DOES NOT WORK (runtime error 'temp'
    Temp = Str(Fix(Val(Me.[Actual Temp])))
    cProof = DLookup("[Temp]", strTable, Proof)

RETURNS INCORRECT VALUE ) looks up 40, not field 40
    Temp = Str(Fix(Val(Me.[Actual Temp])))
    cProof = DLookup([Temp], strTable, Proof)

ERROR 39
    Temp = Str(Fix(Val(Me.[Actual Temp])))
    cProof = DLookup("['" & Temp & "']", strTable, Proof)

says there is an extra (  
    cProof = DLookup("[Str(Fix(Val(Me.[Actual Temp])))]", strTable, Proof)

attachments are the form and the table with a portion of the data

 

[RuralGuy]

Have you tried Debug.Print or MsgBox on the two proof(s)? See what is actually being returned.

 

[pbaldy]

You say you're legally required to use the table, but must it be in that format? The "correct" way to store that in a database would look more like:

76...1...101.9
76...2...101.5
...
77...1...102.8
77...2...102.4

That gives you the same answer with a much simpler method.

 

[plog]

I am legally bound to use

Federally mandated or state? Have a statue number I can use to read the actual language to possibly find a loophole?

I really don't understand your issue, but do see 2 things you should tackle before attempting whatever it is you are having trouble with.

1. You should not be using the table you posted. Just because you are given data in a certain manner doesn't mean you need to keep it as such. You need to normalize that table. Instead of 1 row with 207 values (206 + key), you need to turn that into 206 rows that hold 2 values (key + value).

2. You should not be storing numeric data as text. In your image your values are left justified which means that you are storing those values as numbers. Comparing numbers and comparing text are handled 2 very seperate ways with unexpected outcomes when you choose the wrong data type. I suspect this might be the issue.

With that said, can you try and demonstrate your issue with data? Show us a sample of your data then tell us what you hope your function does based on that data. Give a concrete example we can follow.

 

[ClearwaterDiver]

Federally mandated or state? Have a statue number I can use to read the actual language to possibly find a loophole?

I really don't understand your issue, but do see 2 things you should tackle before attempting whatever it is you are having trouble with.

1. You should not be using the table you posted. Just because you are given data in a certain manner doesn't mean you need to keep it as such. You need to normalize that table. Instead of 1 row with 207 values (206 + key), you need to turn that into 206 rows that hold 2 values (key + value).

2. You should not be storing numeric data as text. In your image your values are left justified which means that you are storing those values as numbers. Comparing numbers and comparing text are handled 2 very seperate ways with unexpected outcomes when you choose the wrong data type. I suspect this might be the issue.

With that said, can you try and demonstrate your issue with data? Show us a sample of your data then tell us what you hope your function does based on that data. Give a concrete example we can follow.

Federally mandated. absolutely no room to wiggle

I cannot post URL yet.. removed url tags and added hypens:
www-ttb-gov-/foia/gauging_manual_toc.shtml#27:1.0.1.1.25.5



1 - Making it rows that hold 2 values will be 100x206, or 20,600 entries.. not 206, that is unrealistic. I would have to have an independent key and then 3 fields, one field for the proof and then one for the temp and one for the resulting proof. That results in 100 entries for 1 proof, 100 entries for 2 proof, etc, all the way to 206... , I cannot see that 20,600 long line of entries being remotely the smart way to do this...

FYI - This table was gleaned from an existing android app called liquorproofer as a .db file, It works fine in that database app. I exported it a year or so ago and have it working quite well in an Excel Spreadsheet.


2 - That does make some sense, thank you, I will make sure they are numeric. One thing to note, though, is that the DLookup and table work fine if the variable is manually defined, this would say there is not an issue with the table, but instead in how I am getting the string assembled and into DLookup, correct?


I thought I did give examples of the dozen or so styles formatting I tried, not sure what you mean by 'demonstrate'...

This works:

    Temp = "[40]"
    Temp1 = "[41]"
    cProof = DLookup(Temp, strTable, Proof)
    cProof1 = DLookup(Temp, strTable, Proof1)
    cTemp = DLookup(Temp, strTable, Proof)
    cTemp1 = DLookup(Temp1, strTable, Proof)

and I gave a long list of what doesn't, including:

    Temp = Str(Fix(Val(Me.[Actual Temp])))
    cProof = DLookup("[Temp]", strTable, Proof)

and

    Temp = Str(Fix(Val(Me.[Actual Temp])))
    cProof = DLookup([Temp], strTable, Proof)

 

[ClearwaterDiver]

Have you tried Debug.Print or MsgBox on the two proof(s)? See what is actually being returned.

I don't have a lot of debug skill in access, but your pointers should help me poke around... thank you...

 

[ClearwaterDiver]

You say you're legally required to use the table, but must it be in that format? The "correct" way to store that in a database would look more like:

76...1...101.9
76...2...101.5
...
77...1...102.8
77...2...102.4

That gives you the same answer with a much simpler method.

but 20,600 entries is not simple...

31(key)...1(proof)...31degrees)...1.7 (corrrected proof)
...
...
94(key)...1(proof)...94(degrees)...0.1(corrected proof)
95(key)...2(proof)...31(degrees)...2.7(corrected proof)
...
...

20,600(key) 206(proof) 100(degrees)...199.6(corrected proof)


IMHO, that would go against all that databases are about and why they are used instead of spreadsheets when things get complicated!

 

[ClearwaterDiver]

I have changed the 20 of so fields that were 'short text' to double-general like the rest, it is likely they went that way because the first 70 entries are blank...

What I *think* I need to solve the issue is the correct syntax to take a field value, in my case [Actual Temp], make it into one version that is the lower integer and one that is the higher integer so that i can interpolate. I researched and Fix() seemed to be the best way, so I used Fix() and Fix() + 1 I need to then store those at Temp and Temp1, respectively, and then insert those into the DLookup

Again, my database works *just fine* to get the four values needed from the database for interpolation if I use:

    Temp = "[40]"
    Temp1 = "[41]"
    cProof = DLookup(Temp, strTable, Proof)
    cProof1 = DLookup(Temp, strTable, Proof1)
    cTemp = DLookup(Temp, strTable, Proof)
    cTemp1 = DLookup(Temp1, strTable, Proof)

it is when I try to put [Actual Temp] into Temp and Temp1 and then into the DLookup that it fails.

 

[ClearwaterDiver]

I may have found it, here are a couple screenshots with mouseover notes...

The first is where it errors..


In the 2nd, it looks like there is a leading space in my [Actual Temp] or Temp/Temp1 fields... I will work on getting rid of that... I am not sure if there is really an extra set of quotes or not, but I see two, we will get rid of the leading space first.

 

[ClearwaterDiver]

OK, I used trim to get rid of the leading space, and still got an error...

'type mismatch' error in first .png , which is something I can work with..

The 2nd .png shows the double quotes, so I deleted the Chr(34) from in front and behind the Temp and Temp1 statements.


And BINGO... it works... after 15 hours today on it, it works... Thank you guys for getting me looking in the right direction, and especially @RuralGuy for making me get off my ass and at least think about debug.... I still hunt and peck in debug, but I did find answers...



WORKING CODE FOR VARIABLE DLookup(expr):
(With completely numeric field names to boot...)

Private Sub Interpolated_Proof_Click()
  Dim strTable As String
    Dim Proof As String
    Dim Proof1 As String
    Dim cProof As Double
    Dim cProof1 As Double
    Dim Temp As Variant
    Dim Temp1 As Variant
    Dim cTemp As Double
    Dim cTemp1 As Double

    strTable = "tempCorrection"
    Proof = "key = " + Str(Fix([Actual Proof]))
    Proof1 = "key = " + Str(Fix([Actual Proof]) + 1)
    Temp = Chr(91) & Trim(Str(Fix([Actual Temp]))) & Chr(93)
    Temp1 = Chr(91) & Trim(Str(Fix([Actual Temp]) + 1)) & Chr(93)
    cProof = DLookup(Temp, strTable, Proof)
    cProof1 = DLookup(Temp, strTable, Proof1)
    cTemp = DLookup(Temp, strTable, Proof)
    cTemp1 = DLookup(Temp1, strTable, Proof)
    Me.[Interpolated Proof] = cProof + (Me.[Actual Proof] - Fix(Val([Actual Proof]))) * (cProof1 - cProof) + ((Me.[Actual Temp] - Fix(Val([Actual Temp]))) * (cTemp1 - cTemp))
  
End Sub

 

[pbaldy]

but 20,600 entries is not simple...

31(key)...1(proof)...31degrees)...1.7 (corrrected proof)
...
...
94(key)...1(proof)...94(degrees)...0.1(corrected proof)
95(key)...2(proof)...31(degrees)...2.7(corrected proof)
...
...

20,600(key) 206(proof) 100(degrees)...199.6(corrected proof)


IMHO, that would go against all that databases are about and why they are used instead of spreadsheets when things get complicated!

I know you've solved your problem, but I wanted to comment on this. 20k records is trivial. I think your logic is reversed. Your table has a piece of data as a field name (temperature). That's normal in a spreadsheet, a big no-no in a database. Plog was telling you the same thing, and my gut says Allan would also agree that the design isn't normalized. Your multi-step process is necessary because you are working around this design.

 

[Minty]

I'll chime in as well - It would take about 15 minutes tops, to create the table in the correct format if you posted up a copy of the original excel spreadsheet, and then your problem wouldn't be a problem.

In fact 15 minutes might be 10 minutes over the top.

 

[ClearwaterDiver]

I know you've solved your problem, but I wanted to comment on this. 20k records is trivial. I think your logic is reversed. Your table has a piece of data as a field name (temperature). That's normal in a spreadsheet, a big no-no in a database. Plog was telling you the same thing, and my gut says Allan would also agree that the design isn't normalized. Your multi-step process is necessary because you are working around this design.

IMHO the field names, even though they are numbers, are no more a piece of data than the field name of "FirstName", "LastName", "Address 1" "Address2", City", "State", "ZipCode"...

it is a 2-dimensional data field, an array.... Think of a fuel map database for fuel injection, do they unmap that data into a long list, do they? (pretty sure they don't). I googled 'fuel map database and they *all* look like 2-D arrays... (I cannot link an image with just 8 posts...)


Attached is the .db file (in a .zip) that is actually packaged with the android app 'Liquor Proofer" it is equivalent to mine, and there are other existing programs, some costing $500/mo to license for your distillery, that use the same table, the one that the government printed in 1913...


To summarize, I came asking for syntax help, I presented as much detail as I could think to include, I researched the problem for hours. My database lookup worked fine, and I stated that. One user helped me get on the right track with debugging and the helped me get the syntax right while several others just played sharpshooters and attacked my database layout.

 

[isladogs]

To summarize, I came asking for syntax help, I presented as much detail as I could think to include, I researched the problem for hours. My database lookup worked fine, and I stated that. One user helped me get on the right track with debugging and the helped me get the syntax right while several others just played sharpshooters and attacked my database layout.

I've not contributed to this thread till now.

You may have only wanted specific points to be answered.
However, the responses from several highly experienced forum users were pointing out simple ways that your database could be improved further.

It was advice that you are free to ignore
However I would recommend you consider the common points arising from those replies

They know what they are talking about.
That weren't sharp shooting nor were they attacking you or your db

 

[ClearwaterDiver]

I'll chime in as well - It would take about 15 minutes tops, to create the table in the correct format if you posted up a copy of the original excel spreadsheet, and then your problem wouldn't be a problem.

In fact 15 minutes might be 10 minutes over the top.

I spent 10 minutes doing the first 11 degrees(2266 lines) in excel, I imported them to access and it chopped them up because some fields were empty...

I spent another 10 minutes reformatting the spreadsheet and re-importing, attached is a snapshot. it will take an hour and there is a lot of chance for human error.


So lets say that database was completely butchered up and put into a linear, 4 field form(key, rawTemp, rawProof, correctedProof, what would the code look like to retrieve the data from the following four datapoints?

100p@10deg
100p@11deg
101p@10deg
101p@11deg

If you make the code that replaces mine, I will finish butchering the database... I did come here for syntax help, after all....

 

[ClearwaterDiver]

I've not contributed to this thread till now.

You may have only wanted specific points to be answered.
However, the responses from several highly experienced forum users were pointing out simple ways that your database could be improved further.

It was advice that you are free to ignore
However I would recommend you consider the common points arising from those replies

They know what they are talking about.
That weren't sharp shooting nor were they attacking you or your db

Well then, now that I have made the db table to their liking, I should expect working code? or will they sharpshoot more...

Maybe I just look at this as how computer architecture would access memory, a 100 bit wide bus is certainly faster than a 1-bit wide one, right???

total number of 'cells' is 17,655 for the 2D table...
total number of 'cells' is 73,120 for the new table... Now that is what I call *efficient*!!!

 

[plog]

To be fair, if you expect us to "sharpshoot" we can't "sharpshoot"; part of that definition is unexpected verbal attack. Boom. You just got meta-sharpshooted.

Now on to your issue that you gave us 13 minutes on before gloating about being efficient. Why even use a database at all? You're working in Access which sits on Windows which sits on 8 more layers of software which ultimately sits on silicon. If ultimate computer efficiency is the aim, I suggest you learn assembler and not VBA.

As for the test problem you proposed, I don't fully understand your question:

So lets say that database was completely butchered up and put into a linear, 4 field form(key, rawTemp, rawProof, correctedProof, what would the code look like to retrieve the data from the following four datapoints?

100p@10deg
100p@11deg
101p@10deg
101p@11deg

Are those email addresses? How does the sample data relate to the table and field you posted? Perhaps if you could post your problem with better data we could help you.

The screenshot of your table is good, now give us an example of what you want to look up. Something along the lines of:

When rawProof is 62.23 and rawTemp is 1.1 I expect Key 62 returned.

I need an understandable starting and ending point. Give me that and I can write the middle portion for you.

 

[ClearwaterDiver]

To be fair, if you expect us to "sharpshoot" we can't "sharpshoot"; part of that definition is unexpected verbal attack. Boom. You just got meta-sharpshooted.

Now on to your issue that you gave us 13 minutes on before gloating about being efficient. Why even use a database at all? You're working in Access which sits on Windows which sits on 8 more layers of software which ultimately sits on silicon. If ultimate computer efficiency is the aim, I suggest you learn assembler and not VBA.

As for the test problem you proposed, I don't fully understand your question:



Are those email addresses? How does the sample data relate to the table and field you posted? Perhaps if you could post your problem with better data we could help you.

The screenshot of your table is good, now give us an example of what you want to look up. Something along the lines of:

When rawProof is 62.23 and rawTemp is 1.1 I expect Key 62 returned.

I need an understandable starting and ending point. Give me that and I can write the middle portion for you.

NO, it is shorthand for look up 100 proof at 10 degrees

100p@10deg --> 100 in rawProof 'at' 10 in rawTemp you will get back key 119.40 degrees (happens to be key 1954 ) man, that table sure works nice!

Also, you cannot look up a decimal, only whole values are in the raw data, you have to do double interpolation, grab the higher and lower integer values for temp and proof. (yes, procedure is mandated by federal government, https://www.ttb.gov/spirits/interpolation-proof-table1-gauging-manual.shtml )

so if you measure 100.2 proof and 10.7 degrees, you need these four lookups:

rawProof = 100, rawTemp = 10 ---> 119.40
rawProof = 101, rawTemp = 10 ---> 120.30
rawProof = 100, rawTemp = 11 ---> 119.10
rawProof = 101, rawTemp = 11 ---> 120.00


the last line in my code that I supplied does the actual interpolation:

Me.[Interpolated Proof] = cProof + (Me.[Actual Proof] - Fix(Val([Actual Proof]))) * (cProof1 - cProof) + ((Me.[Actual Temp] - Fix(Val([Actual Temp]))) * (cTemp1 - cTemp))

that is 119.4 + ((100.2-100)*(120.30-119.40)) + ((10.7-10.0)*(119.40-119.10)) = 119.4




but you still have to put the value from the form cell into DLookup(), and that is where my original request came in... I needed to know that yes, you have to pad the value with [] , using Chr(91) and Chr(93), but you do not have to pad the quotes, as it will insert them already...

 

[ClearwaterDiver]

I am using 1-11 degrees in my example, as that is all the database has populated at this time... even with cutting and pasting hundreds at a time, it is still 7000 cells, I will do the other 63,000 or so when someone provides a working piece of code...

 

[plog]

First, here's a code snippet to demonstrate how to use a Dlookup to get your values using your new table structure:

actual_temp = 10.7
actual_proof = 100.2


v1 = Dlookup("correctedProof", "tmpCorrection2", "rawProof=" & Int(actual_proof) & " AND rawTemp=" & Int(actual_temp))

v2 = Dlookup("correctedProof", "tmpCorrection2", "rawProof=" & Int(actual_proof) + 1 & " AND rawTemp=" & Int(actual_temp))

v3 = Dlookup("correctedProof", "tmpCorrection2", "rawProof=" & Int(actual_proof) & " AND rawTemp=" & Int(actual_temp) + 1)

v4 = Dlookup("correctedProof", "tmpCorrection2", "rawProof=" & Int(actual_proof) + 1 & " AND rawTemp=" & Int(actual_temp) + 1)

To repeat, the point of the code aboveis to demonstrate how easy your lookups become with a properly structured table. I didn't test it, so I'm certain I missed a comma or misspelled something. Also, I didn't do your math you would use the four v values to do that.

Lastly, I don't think converting your existing table structure to the one we suggest has to be that painful. You have the correct table built, you just need it populated and you obviously know VBA. I think you can write some code that reads in data from your existing table and runs a few loops and DoCmd.RunSQL statements to populate the new table.