CODEWITHSUNDEEP
ms-accessvba
m4rkb4
m4rkb4

Reputation: 15

Order of records that DLookup uses to return first value (Access VBA)

long time stalker but first time poster here so apologies for any social faux pas I make.

I am trying to use DLookup to search for a record in a table using VBA. The specific record I am after is the one closest in date to sdate (a user specified date) which also meets some other criteria. There are likely to be a couple of records with dates prior to sdate which meet the same criteria, and I am only interested in the one which is chronologically closest to sdate.

The following is a simplified example of the code I am using to try and achieve this. I use baseTestString as there are quite a few similar DLookup expressions so it saves typing and clarifies the code slightly (to me at least).

    DoCmd.OpenTable ("Results")
    DoCmd.SetOrderBy "[Survey_Date] Desc"
    DoCmd.Close acTable, ("Results")

    'set a new criteria for baseline testing using Dlookup
    basetestString = "[Equipment_ID] = '" & equipID & "' AND [Baseline?] = True _
        AND     format([Survey_Date],""ddmmyyyy"") < format(" _
        & sdate & ",""ddmmyyyy"")"

    'set variables
    [Forms]![results]![text_A] = Nz(DLookup("[Input]", "[results]", _
        basetestString))

I believed (perhaps naively) that DLookup returns the first record it finds that matches the criteria specified. So, my code was designed to sort the table into chronological order, thinking that this would be the order that DLookup would cycle through the records.

However, each time I run the code, I am returned the lowest possibly date that matches the criteria rather than the one closest to sdate. After some playing around, I believe that DLookup uses the primary key as its basis for cycling through the records (the earlier dates are entered earlier, and hence given a primary key using autonumber which is lower than later dates).

This leads to me my questions...

1) I am correct in believing this is what is happening when I am returned the wrong record?

2) Is there a way to use DLookup in the way I am attempting? Can I choose which field is used to order the records for DLookup? Assigning the date field as the primary key is not possible as the dates might not always be unique.

3) Is there any other way I can achieve what I am trying to do here?

Thank you very much

Upvotes: 1

Views: 4704

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

It is always unsafe to rely on an order of records in a relational database. You could use DMax to get the date you need, but I think that in this case a recordset would be quicker.

Dim rs As DAO.Recordset
Dim db As Database
Dim ssql As String

Set db = CurrentDB
ssql=" SELECT input" _
& " FROM   results" _
& " WHERE  results.[baseline?] = true" _
& "        AND results.equipment_id = '" & equipID & "'" _
& "        AND results.survey_date = (SELECT" _
& "            Max(results.survey_date) " _
& "        FROM   results" _
& "        WHERE  results.[baseline?] = true" _
& "        AND results.equipment_id = '" & equipID & "'" _
& "        AND results.survey_date <#" & Format(sdate,"yyyy/mm/dd") & "#)"

Set rs = db.OpenRecordset(ssql)

strInput = rs("Input")
Debug.Print strInput

You can also check the number of records returned, in case of an error.

Upvotes: 2

Related Questions