Results 1 to 13 of 13
  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185

    DLookUp Sentax Error

    I am getting this error message



    Syntax Error (Missing Operator) in the query expression 'VendorNumber='.

    Here is the line of code that is marked

    IssueVar = DLookup("Issue", "Default_Reasons And Issues", "VendorNumber = " & VendorNumberVar)

    I have looked at the Microsoft Access Help database and have seen the correct generic Syntax but I cannot seem to find what is wrong with this code.

    Dlookup(expr, domain[,criteria])

    Any suggestions?

    Thanks,
    Nick

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Object names with spaces in them have to be enclosed in square brackets:
    "[Default_reasons And Issues]"

    It is good practice to NOT include spaces in object names.

  3. #3
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Ok, Thanks I will give it a try.

  4. #4
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Ok, I tried this fix and it still gave me the same area. It had no problem with me adding in the brackets but It still had an error involving the criteria.
    'VendorNumber='.

    'Issues
    IssueVar = DLookup("Issue", "[Default_Reasons And Issues]", "VendorNumber = " & VendorNumberVar)
    If IsNull(IssueVar) = False Then
    Me![Issue] = IssueVar
    End If

    Thanks,
    Nick

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Syntax Error (Missing Operator) in the query expression 'VendorNumber='.
    That error means that in the expression "VendorNumber = " & VendorNumberVar, VendorNumberVar is Null or a blank.

    How are you assigning a value to VendorNumberVar?

  6. #6
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    We use a form to input data. We are able to type in the first row of purchase orders then go to the next PO it gives us this error when we begin to input the second PO it gives us this error but if we just end that process and ignore the problem we can still complete the rest of the entries.

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    If you get the error when you begin to type in the second row (record), it sounds like you have some incorrect code somewhere - my guess would be in the On Insert event.

    What procedure is the code that is giving you the error in?

  8. #8
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    The Vendor Number is filled in. The form captures more than just the issue. It captures PO, Key Rec, Color Code, Color Name, Reason Code Total Units and so on. The last thing that is captured is the issue. We have a specially populated table set up to prepopulate a table and the vendor number is checked against the default reasons and issues table. The Vendor Number is imputed at the very beginning.

  9. #9
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Here is the full code

    Private Sub AVE_PO_AfterUpdate()
    Dim poVar, LookUpVar
    Dim debitVar

    debitVar = Forms![DebitMemo-Main-Coordination]![DebitNumber]
    'Capture PO Information
    poVar = Me![AVE_PO]
    VendorNumberVar = DLookup("VendorNumber", "A-2-SDC115-Final", "PO = " & poVar)
    'Check for and open comments
    If IsNull(DLookup("ID", "POComments", "PO = " & Me![AVE_PO])) = False Then
    DoCmd.OpenForm "POComments", , , "PO = " & Me![AVE_PO], acFormReadOnly
    End If

    'Check that PO entered is 6 digits
    poVar = [AVE_PO]

    'Capture PO information
    'ARVL DATE
    If IsNull(Me![Arvl DC]) = True Then
    LookUpVar = DLookup("[Arvl DC]", "DebitReport-KRSummary-Query-Coord3", "PO = " & poVar)
    If IsNull(LookUpVar) = True Then
    LookUpVar = "1/1/1900"
    End If
    Me![Arvl DC] = LookUpVar
    End If

    'KEYREC
    If IsNull(Me![KeyRec]) = True Then
    LookUpVar = DLookup("KeyRec", "DebitReport-KRSummary-Query-Coord2", "PO = " & poVar)
    If IsNull(LookUpVar) = True Then
    LookUpVar = "KKKKK"
    End If
    Me![KeyRec] = LookUpVar
    End If

    'LINE
    If IsNull(Me![Line]) = True Then
    LookUpVar = DLookup("Line", "DebitReport-KRSummary-Query-Coord2", "PO = " & poVar)
    If IsNull(LookUpVar) = True Then
    LookUpVar = "LL"
    End If
    Me![Line] = LookUpVar
    End If

    'CC
    If IsNull(Me![CC]) = True Then
    LookUpVar = DLookup("CC", "DebitReport-KRSummary-Query-Coord2", "PO = " & poVar)
    If IsNull(LookUpVar) = True Then
    LookUpVar = "CC"
    End If
    Me![CC] = LookUpVar
    End If

    'COLOR
    If IsNull(Me![Color]) = True Then
    LookUpVar = DLookup("Color", "DebitReport-KRSummary-Query-Coord2", "PO = " & poVar)
    If IsNull(LookUpVar) = True Then
    LookUpVar = "COLOR"
    End If
    Me![Color] = LookUpVar
    End If

    'CHARGE
    If IsNull(Me![UnitCharge]) = True Then
    LookUpVar = DLookup("Cost", "A-4 Historical 209", "PO = " & poVar)
    If IsNull(LookUpVar) = False Then
    If LookUpVar >= 3 Then
    Me![UnitCharge] = 0.25
    Else
    Me![UnitCharge] = 0.1
    End If
    End If
    End If

    'UNITS
    If IsNull(Me![ActualReceipts]) = True Then
    LookUpVar = DLookup("ActualReceipts", "DebitReport-KRSummary-Query-Coord2", "PO = " & poVar)
    If IsNull(LookUpVar) = True Then
    LookUpVar = 0
    End If
    Me![ActualReceipts] = LookUpVar
    End If

    'Reason
    If IsNull(VendorNumberVar) = False Then
    ReasonVar = DLookup("ReasonCodes", "Default_Reasons And Issues", "VendorNumber = " & VendorNumberVar)
    If IsNull(ReasonVar) = False Then
    Me![ReasonCode] = ReasonVar
    End If
    End If

    'Issues
    IssueVar = DLookup("Issue", "[Default_Reasons And Issues]", "VendorNumber = " & VendorNumberVar)
    If IsNull(IssueVar) = False Then
    Me![Issue] = IssueVar
    End If

    If IsNull(DLookup("PO", "POComments-Main-Coordination", "DebitNumber = " & debitVar)) = False Then
    DoCmd.OpenForm "POComments-Main-Coordination", , , "DebitNumber = " & debitVar, acFormReadOnly
    End If
    End Sub

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Oops - I posted this before I saw your full code. I'll take a look at it.

    The issue is VendorNumberVar, from what the error message indicates. When and how is it being set (and I guess I should ask what is it - a form field or a variable?).

    IssueVar = DLookup("Issue", "[Default_Reasons And Issues]", "VendorNumber = " & VendorNumberVar)
    That is the offending line of code - so where is it - in which event procedure? It appears that when that line is executed, VendorNumberVar has no value.
    Do you have a VendorNumberVar = ... statement somewhere?
    Last edited by John_G; 06-28-2016 at 12:13 PM. Reason: added first line

  11. #11
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    VendorNumberVar = DLookup("VendorNumber", "A-2-SDC115-Final", "PO = " & poVar)
    So this is what it is looking up. Ok, I think I have figured it out. She told me that there is a nightly process that now that I see this code. Once they fill out this report. It should run fine.

  12. #12
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Your "Issues" block of code is the only one which does not check for a Null value of something. Compare it with your "Reasons" block -
    it has If IsNull(VendorNumberVar) = False Then , but your "issues" block does not.

    So while the "Reasons" block allows for a Null VendorNumberVar, the "Issues" block doesn't, hence the error.

    I think you have already determined that
    VendorNumberVar = DLookup("VendorNumber", "A-2-SDC115-Final", "PO = " & poVar)
    can result in Null for VendorNumberVar.

    Welcome to the black art of debugging! Good luck with your project.

  13. #13
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    185
    Ok, Thank you for the help .

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-04-2016, 09:40 AM
  2. Dlookup error
    By Risto85 in forum Programming
    Replies: 7
    Last Post: 02-25-2015, 11:06 AM
  3. DLOOKUP #Error
    By neo651 in forum Forms
    Replies: 6
    Last Post: 12-12-2012, 12:54 PM
  4. Error in Dlookup?
    By cindytan in forum Forms
    Replies: 1
    Last Post: 02-07-2012, 02:40 AM
  5. DLookup error
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 08-25-2010, 07:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums