DLookup Run Time Error 3075

[nick243]

Hi, I am having an issue with not finding the criteria in the domain. I cannot get the MEF# to populate and find it in Sheet1. I know there is a difference in code from a numeric value to a text value. MEF# is a text value. I tried changing the code to use the text value correctly but nothing has worked out. Any help would be appreciated. Thanks, Nick The problem begins after the comment Check if MEF# is valid.

Private Sub Command39_Click()
Dim MEFVar, MEFVarX


' Check that the MEF is 6 digits
MEFVar = InputBox("Enter a MEF Number to start a new Chargeback:", "Open New Chargeback")
If Len(MEFVar) <> 6 Then
    MsgBox MEFVar & " is not a valid MEF Number."
    End
End If


'Check that MEF is valid


MEFVarX = DLookup("MEF#", "Sheet1", "MEF# = " & MEFVar)
If IsNull(MEFVarX) = True Then
    MEFVarX = MsgBox(MEFVar & " is not found on the list of MEF numbers. " & vbNewLine & vbNewLine & "Do you want to write a Chargeback for this MEF " & MEFVar & "?", 4)
    If MEFVarX <> 6 Then
        End
    End If
End If

[PLangley]

Hi Nick243,
If MEF# is a text value (not a numeric data type), you must put single quotes around the value you are passing in the criteria as follows:
MEFVarX = DLookup("MEF#", "Sheet1", "MEF# = '" & MEFVar & "'")

In the criteria statements, always use single quotes (') around text data types and use pound sign (#) around date types. Numeric types require no enclosing characters.

Best regards,
Pat Langley

[nick243]

I put in the new code and checked to varify that in sheet1 it is a text field. I am still getting a Run Time error 3075 Syntax error in date in query expression 'MEF#'.

Thanks for your help Nick

[PLangley]

Since MEF# has a special character in the name, try putting square brackets around it in the lookkup:
MEFVarX = DLookup("[MEF#]", "Sheet1", "[MEF#] = '" & MEFVar & "'")

Regards,
Pat

[nick243]

It changed the error to
Run Time error 3075 Syntax error in date in query expression 'MEF# = '183309".

[PLangley]

Can you repost your modified code?
Pat

[nick243]

Private Sub Command39_Click()
Dim MEFVar, MEFVarX


' Check that the MEF is 6 digits
MEFVar = InputBox("Enter a MEF Number to start a new Chargeback:", "Open New Chargeback")
If Len(MEFVar) <> 6 Then
    MsgBox MEFVar & " is not a valid MEF Number."
    End
End If


'Check that MEF is valid


MEFVarX = DLookup("[MEF#]", "Sheet1", "MEF# = '" & MEFVar & "'")
If IsNull(MEFVarX) = True Then
    MEFVarX = MsgBox(MEFVar & " is not found on the list of MEF numbers. " & vbNewLine & vbNewLine & "Do you want to write a Chargeback for this MEF " & MEFVar & "?", 4)
    If MEFVarX <> 6 Then
        End
    End If
End If






End Sub

[nick243]

I got it, I forgot to put [] around Criteria MEF#.

Thank You for your help

Nick

[PLangley]

Yep, you found it. Glad I could help.

Best regards,
Pat Langley