• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

"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
    Else
        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

0
mkelly2384
Asked:
mkelly2384
2 Solutions
 
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"
0
 
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)
0
 
zadeveloperCommented:
what is the value in :
Forms![FRM_Main_Login_Students]![lastNameTextBox].Value
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mkelly2384Author Commented:
The value of Forms![FRM_Main_Login_Students]![lastNameTextBox].Value is "Kelly".
0
 
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.
   
   
0
 
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))
0
 
mkelly2384Author Commented:
Ahh--that did it.  Thank you!!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now