"The expression you entered as a query parameter producted this error"

I am getting run-time error 2471 saying that "The expression you entered as a query parameter producted this error: Kelly".  This occurs for the line below.  "Kelly" is the value of strTempLast.  Any ideas?

strCurrentLast = Nz(DLookup(strTempLast, "dbo_STUDENT_DIM", "[STU_LAST_NAME]='" & strTempLast & "' and  [STU_ID]=" & strTempID))
Private Sub yesButton_Click()
    Dim strCurrentID As String
    Dim strCurrentLast As String
    Dim strTempID As String
    Dim strTempLast As String
    strTempID = Forms![FRM_Main_Login_Students]![stuIDTextBox].Value
    strTempLast = Forms![FRM_Main_Login_Students]![lastNameTextBox].Value
    'Search for inputed values in dbo_STUDENT_DIM table.
    strCurrentID = Nz(DLookup(strTempID, "dbo_STUDENT_DIM", "[STU_ID]=" & strTempID))
    strCurrentLast = Nz(DLookup(strTempLast, "dbo_STUDENT_DIM", "[STU_LAST_NAME]='" & strTempLast & "' and  [STU_ID]=" & strTempID))
    'If input is not found in TBL_Students, give error message and return to main login screen
    If strCurrentID = -1 Or strCurrentLast = "" Then
        If MsgBox("The information you entered did not match any IWU student records.  Please try again.", _
        vbOKOnly, "Invalid Entry") = vbOK Then
            DoCmd.OpenForm "FRM_Main_Login_Students", acNormal
            DoCmd.Close acForm, "FRM_First_Time_Message_Students", acSaveNo
            Exit Sub
        End If

    'If input is found, add all user data into the fields of TBL_Students
        DoCmd.Close acForm, "FRM_First_Time_Message_Students", acSaveYes
        DoCmd.OpenForm "FRM_New_Student_Detail", acNormal, "", , , acWindowNormal
    End If
End Sub

Open in new window

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You most likely need to change this one also:

strCurrentID = Nz(DLookup(strTempID, "dbo_STUDENT_DIM", "[STU_ID]=" & strTempID))

The syntax for DLookup is:

DLookup(TheFieldYouWantToReturn, TheTableYouWantToLookIn, YourCriteria)
Rey Obrero (Capricorn1)Commented:

----------------------------vvvvvvvvv--- change with field name
strCurrentLast = Nz(DLookup(strTempLast, "dbo_STUDENT_DIM", "[STU_LAST_NAME]='" & strTempLast & "' and  [STU_ID]=" & strTempID))

if you are looking for the LastName, change strTempLast with "LastName"
what is the value in :
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mkelly2384Author Commented:
The value of Forms![FRM_Main_Login_Students]![lastNameTextBox].Value is "Kelly".
mkelly2384Author Commented:
So based on what you all said, I changed it to this:

strCurrentID = Nz(DLookup([STU_ID], "dbo_STUDENT_DIM", "[STU_LAST_NAME]='" & strTempLast _
       & "' and [STU_ID]=" & strTempID))
    strCurrentLast = Nz(DLookup([STU_LAST_NAME], "dbo_STUDENT_DIM", "[STU_LAST_NAME]='" & strTempLast _
       & "' and [STU_ID]=" & strTempID))

But now I'm getting an ""Invalid Use of Null" error.
Rey Obrero (Capricorn1)Commented:
you have to enclose the field name with quotes  "FieldName"

strCurrentID = Nz(DLookup("[STU_ID]", "dbo_STUDENT_DIM", "[STU_LAST_NAME]='" & strTempLast _
       & "' and [STU_ID]=" & strTempID))
    strCurrentLast = Nz(DLookup("[STU_LAST_NAME]", "dbo_STUDENT_DIM", "[STU_LAST_NAME]='" & strTempLast _
       & "' and [STU_ID]=" & strTempID))
mkelly2384Author Commented:
Ahh--that did it.  Thank you!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.