using DMax and DLookup with time and date data

[grgimpy]

Currently I'm using the following code to look up a value in my table:

Point1 = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQuery", "[ID]=" & DMax("[ID]", "Ni-Au-CooperSubformQuery"))

I want to change the code to look up the most recent entry based on the "Date/Time" field instead of "ID", but I'm having a syntax error. I believe that I need to put some #'s somewhere, but I can't get it right.

Point1 = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQuery", "[Ni-Au-Date/Time]=" & DMax("[Ni-Au-Date/Time]", "Ni-Au-CooperSubformQuery"))

How do I fix this?

My second question is this: The code above should look up the "most recent" data entered because the field is setup on a [Ni-Au-Date/Time]= Now routine. How do I modify the code so that is looks up previous values that are not the maximum time/date value. ie: How do I look up the value that is the 2nd most recent?

Any information would be much appreciated!

 

[lameid]

Your Date Delimited Data...

Point1 = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQuery", "[Ni-Au-Date/Time]= #" & DMax("[Ni-Au-Date/Time]", "Ni-Au-CooperSubformQuery")) & "#"

As for the xth value I would sort "Ni-Au-CooperSubformQuery" descending by "[Ni-Au-Date/Time]".

From there there are two possibilites.

1) You make a query that gets the top x values (Right click in the query design view grey space at top, get its properties and set Top Values to x) then get the Minimum instead of the maximum value (dmin vs dmax).

2) Alternately you could open the recordset and use movenext x - 1 times. Don't forget to test for the End of the recordset with EOF.

Hopefully that's enough detail for you?

 

[grgimpy]

I went with option 2. For this particular code I need to look up 3 different values. I don't think this code is actually looking up the values specified for the record set. Maybe it's b/c I'm trying to look up values in a subform. Unfortunately the Query I need to look the values up in is not the main query for the form. Each "PartNumber" has its own query.

Any help would be appreciated. Thanks.

    If Me.[cboPartNumber] = "156882-001" Then
        SPCAve = DLookup("[Ni-Au-CooperNiAve]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
        SPCSigma = DLookup("[Ni-Au-CooperNiSigma]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
        Me.Ni_Au_CooperSubformQuery_subform.Form.Recordset.MoveFirst
    For j = 1 To 3
        Point1 = DLookup("[Ni-Au-Nickel Thickness Avg]", "Ni-Au-CooperSubformQuery")
        If Point1 > SPCAve + 2 * SPCSigma Then TwoOfThreeP = TwoOfThreeP + 1
        Me.Ni_Au_CooperSubformQuery_subform.Form.Recordset.MoveNext
    Next j
    If TwoOfThreeP > 1 Then
    MsgBox "Nickel-Gold Cooper Nickel Thickness Out of Control.  SPC rule: 2 out of 3 consecutive points all appear above two standard deviations from the center line.", vbExclamation, "Ni/Au Line Cooper Nickel Thickness Out of Control'"
    DoCmd.GoToRecord , , acLast
    End If
    End If

 

[grgimpy]

here's my second attempt to write this code. if instead of using

DoCmd.GoToRecord acDataQuery, "Ni-Au-Valeo75SubformQuery", acLast

i use:

DoCmd.GoToRecord ,, acLast

the code goes through the last three records fine and anaylzes it the way i want. unfortunately, each different "cboPartNumber" has its own query and must be evaluated separately. when i use the docmd.gotorecord for each query, the code only evaluates the most current value three times, instead of evaulating the current value, then previous value, then previous value. i'm confused about what i am missing. here's how i want the code to work, but it is not going back to previous values in the queries....

    If Me.[cboPartNumber] = "162475-001" Then
    
    DoCmd.RunCommand acCmdSaveRecord
    
        SPCAve = DLookup("[Ni-Au-Valeo75NiAve]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
        SPCSigma = DLookup("[Ni-Au-Valeo75NiSigma]", "Ni-Au-Production Log", "[ID]=" & DMax("[ID]", "Ni-Au-Production Log"))
        DoCmd.OpenQuery "Ni-Au-Valeo75SubformQuery"
        DoCmd.GoToRecord acDataQuery, "Ni-Au-Valeo75SubformQuery", acLast
    For i = 1 To 3
        If Me.[Ni_Au_Nickel_Thickness_Avg] > SPCAve + 2 * SPCSigma Then TwoOfThreeP = TwoOfThreeP + 1
        DoCmd.GoToRecord acDataQuery, "Ni-Au-Valeo75SubformQuery", acPrevious
    Next i
    If TwoOfThreeP > 1 Then
    DoCmd.Close acDataQuery, "Ni-Au-Valeo75SubformQuery", acSaveNo
    MsgBox "blah", vbExclamation, "blah"
    Else
    DoCmd.Close acDataQuery, "Ni-Au-Valeo75SubformQuery", acSaveNo
    End If
    End If

 

[lameid]

Sorry I don't have the time to really fix this for a while but you don't want to use GoToRecord instead you want to open a recordset and loop through records an appropriate number of times. There should be plenty of threads with expamples out there (or start a new thread to grab someone elses attention).

 

[grgimpy]

i tried using a

Me.Ni_Au_CooperSubformQuery_subform.Form.Recordset.MoveFirst

type of routine, but you probably saw that also. i'll search around for more info. thanks for the responses.