gcDataTechnology
Technical User
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
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