DLookup with variable for expr

[plog]

Also some things to think about when writing this function:

Dlookup returns Null when it can't find a value and that can screw up a lot of things. Its a good idea to wrap Dlookups in a Nz() function which forces nulls to some value (-1 perhaps to indicate an error.)

You are also going to need to test your input values to make sure they need to be double interpolated. Suppose you have an actual record for the exact values you are looking up--you would want that exact value and not the double interpolated one, right?

 

[ClearwaterDiver]

Also some things to think about when writing this function:

Dlookup returns Null when it can't find a value and that can screw up a lot of things. Its a good idea to wrap Dlookups in a Nz() function which forces nulls to some value (-1 perhaps to indicate an error.)

You are also going to need to test your input values to make sure they need to be double interpolated. Suppose you have an actual record for the exact values you are looking up--you would want that exact value and not the double interpolated one, right?

the error, yes, probably should look at that, I did find one character incorrect in the entries I copied from the array to the 3-column table

not worried about interpolating an exact number, it still works out correctly, and keeps code simple...

 

[isladogs]

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*!!!

Surprised nobody has responded to this point already
Excel has cells, Access has records
They may look similar in datasheet view but the whole structure is entirely different

73120 records in a properly normalised table will be processed FAR faster by Access than 17655 records in an unnormalised table of many more fields.

 

[pbaldy]

Surprised nobody has responded to this point already

He seemed to resent people telling him of the design flaw, so I left it alone.

 

[ClearwaterDiver]

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

-snip-

OK, I took that and tweaked it, tmpCorrention had to have an e added, and I changed my database to have integers since you used Int().

I also realized after looking at my spreadsheet commands that I really did not need V4, I did not use it... it uses v2-v1 and v3-v1 for the multipliers...


here is the code:

Private Sub Interpolated_Proof2_Click()
    Dim strTable2 As String
        Dim v1 As Double
        Dim v2 As Double
        Dim v3 As Double
        Dim v4 As Double
        
        strTable2 = "tempCorrection2"
        v1 = DLookup("correctedProof", "tempCorrection2", "rawProof=" & Int(Actual_Proof) & " AND rawTemp=" & Int(Actual_Temp))
        v2 = DLookup("correctedProof", "tempCorrection2", "rawProof=" & Int(Actual_Proof) + 1 & " AND rawTemp=" & Int(Actual_Temp))
        v3 = DLookup("correctedProof", "tempCorrection2", "rawProof=" & Int(Actual_Proof) & " AND rawTemp=" & Int(Actual_Temp) + 1)

Me.[Interpolated Proof2] = v1 + (Me.Actual_Proof - Fix(Me.[Actual_Proof])) * (v2 - v1) + ((Me.[Actual_Temp] - Fix(Me.[Actual_Temp])) * (v3 - v1))

End Sub

The both work, I do not notice any discernible speed differences, I will work on adding the other 50,000 cells to the table and see if that still holds true.

here is a snapshot of the form with both versions on it

 

[ClearwaterDiver]

He seemed to resent people telling him of the design flaw, so I left it alone.

Frustration can look like resentment when people point out things other than your problem at hand... It felt like..... My car wouldn't start and someone insisted on informing me that I had mismatched tires....

 

[isladogs]

They were just trying to help you steer your car in a straight line despite it having mismatched tyres

 

[pbaldy]

I can appreciate that. My thinking was that if you corrected the design, the problem at hand changes, so I was trying to work from the bottom up. Table design is the critical first step in creating an application, so I think we were all starting there.

In any case, I'm glad you have a solution.

 

[ClearwaterDiver]

I can appreciate that. My thinking was that if you corrected the design, the problem at hand changes, so I was trying to work from the bottom up. Table design is the critical first step in creating an application, so I think we were all starting there.

In any case, I'm glad you have a solution.

best part is I have 2 solutions, and learned some things...

 

[Minty]

I certainly wasn't sharpshooting, just trying to help the "steering process".

And if you have a google for excel unpivot, you will find a method to extract your data in one fell swoop , albeit with a bit of fiddling for the blanks. https://www.mrexcel.com/forum/excel-questions/701104-convert-matrix-worrk-sheet-flat-file.html or https://stackoverflow.com/questions/32115219/unpivot-an-excel-matrix-pivot-table/32116657#32116657

Glad you have it sorted out.