Dlookup returns second record not first

[gcDataTechnology]

As I understand Dlookup function is to return the 1st record that matches the lookup criteria when multiple records match the criteria. for me it returns the 2nd or 3rd record matching the criteria and not the first record.

To trouble shoot the situation I've sorted the table by date ascending ensuring the first record is first. I've done this for several parts. I've even copied the part number from the record that was returning and pasted it into the real first record to be sure the text was exactly the same.

This is why I only use Dlookup for unique records...it has never returned the first instance of the criteria for me.

Here is my code:
Option Compare Database

Private Sub cmdDLookUp_Click()
On Error GoTo Err_cmdDLookUp_Click
Dim varNote As Variant
Dim varDate As Variant

varDate = DLookup("Date", "tblQuality_ProductSpecific_Codes", "[Product Assembly] = 'PartNumber1'") & Me.txtEnterProduct)
varNote = DLookup("Notes", "tblQuality_ProductSpecific_Codes", "[Product Assembly] = 'PartNumber1'") & Me.txtEnterProduct)

Me.txtReturnDate = varDate
Me.txtReturnNotes = varNote

Exit_cmdDLookUp_Click:
Exit Sub

Err_cmdDLookUp_Click:
MsgBox Err.Description
Resume Exit_cmdDLookUp_Click

End Sub

Microsoft help "If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. "

"The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain."

I appreciate your time and help.

Learnin', Growin' and Failing Forward

 

[MajP]

Table sort order is only for presentation and thus not really stored (without going into too much detail). You need to run this on a sorted query not the table.

 

[dhookom]

As MajP correctly suggests, records in tables are like marbles in a bag. There is no order to them. Never make assumptions about the order of these records.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Is your field/control name actually this
txtEnterProduct)
or is there a hanging ")"?

I think I would highly discourage using ")" in any object name. I had to test this to see if it was even possible, and unfortunately it is. That could cause some serious headaches in debugging code.

 

[gcDataTechnology]

MajP and everyone the code was edited to use a static part number while troubleshooting. Originally code is for a text box input to be used as criteria. The parenthesis closes the function and orginal post is missing the comment out ' apostrophe before the inclusion of the text box input.

My appologies!!! for anyone newer I'm sorry for confusion. Below are the proper function and names.

varDate = DLookup("Date", "tblQuality_ProductSpecific_Codes", "[Product Assembly] = 'PartNumber1' ")

varNote = DLookup("Notes", "tblQuality_ProductSpecific_Codes", "[Product Assembly] = 'PartNumber1' ")

Learnin', Growin' and Failing Forward

 

[gcDataTechnology]

I appreciate everyone's input. I am learning on the job doing what I can; reading as much as I can about Data systems, Access and SQL.

As far as "real" table record order I surmised that sort order and any user manipulation were visual to the operator and not "real" in the actual data system. We enter a date to indicate when the record went in for our reference. Does Access use anything to actual keep a real record order?

Dhookom, I like the analogy of the marbles...It makes sense that way. We use a query to find the "marble" we need.

Why does it make a difference to run the Dlookup on a query vs a sorted table?

Learnin', Growin' and Failing Forward

 

[dhookom]

If you want the first or last date, you might want to try using DMax() or DMin().

I typically don't trust DLookup() on an ordered query. I always use domain aggregate functions in a way that only a single value can be returned.

Duane
Hook'D on Access
MS Access MVP

 

[gcDataTechnology]

Thanks dhookom. That is how I've applied it. Now in actual use it returns a primary key. I started to play with it more to see how it worked. I thought maybe it's inconsitancy was something I was doing wrong.



Learnin', Growin' and Failing Forward

 

[MajP]

Duane,
Although I always follow your paradigm, I cannot say I ever experienced a case where the dlookup would return incorrect results from an ordered query. Out of curiosity are you suggesting that it can return incorrect results, or that a user can create a logic error and not use a properly ordered query.

If I was doing the above I would have have a qryMaxDate that held the record with the max date for each part number.

 

[dhookom]

I don't doubt that the DLookup() would return the correct result from an ordered query. I just don't like creating a query just to get an ordered recordset for a DLookup() function call.

Duane
Hook'D on Access
MS Access MVP