Results 1 to 10 of 10
  1. #1
    joecamel9166 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    85

    String criteria used in DLookup

    have 4 tables to enter repair costs.


    layout:

    tblUnits:
    unitID(autonumber)
    Unit(PK)
    Branson Model Number(FK)
    Repair Class(FK)
    Estimate
    Etc...

    tblModels:
    Model(PK)

    tblRepairClass:
    Class(PK)

    tblEstimates:
    Model(FK)
    Class(FK)
    Estimate


    VBA Code:
    Private Sub Form_Current()



    strCriteria = Model = " & Me.Branson_Model_Number & " And Class = """ & Me.Repair_Class & """""


    If Not IsNull(Me.Branson_Model_Number) And Not IsNull(Me.Repair_Class) Then
    Me.Estimate = DLookup("Estimate", "tblEstimates", strCriteria)
    Else
    Me.Estimate = 0
    End If
    End Sub

    Each model has 4 classes. A,B,C,0
    Depending on the model and repair class, I need to know what the cost of repair is and have it entered in the quote form and updated in the tblUnits table.

    Debugger isn't picking anything up, but the data is not being filled in.

    I'm not sure what I am missing.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, you realize the current event would fire when the form loads or you change records. It would not fire when you made selections in the model and class controls.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    joecamel9166 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    85
    Correct. Those are selected in the Previous (Evaluation) Form. They are there just for reference.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I don't think this line is correct: strCriteria = Model = " & Me.Branson_Model_Number & " And Class = """ & Me.Repair_Class & """""

    Model = is not in quotes, so Access treats "Model" as a variable.

    Try this (I have used single-quotes for clarity): strCriteria = "Model = " & Me.Branson_Model_Number & " And Class = '" & Me.Repair_Class & "' "


  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The model field is numeric rather than text? Just noticed you didn't have starting quotes. Try

    strCriteria = "Model = " & Me.Branson_Model_Number & " And Class = '" & Me.Repair_Class & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    joecamel9166 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    85
    The Model field is text.

    Syntax error (missing operator) in query expression 'Model = B1510R-DTH And Class ='
    Code:
     strCriteria = "Model = " & Me.Branson_Model_Number & " And Class = '" & Me.Repair_Class & "'"

  8. #8
    joecamel9166 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    85
    I still cant get this. Ive tried your suggestions. Maybe there is something else in the way the DB is set up or something else in my code.

    Ive attached a copy of my DB. If one of you gurus could take a look, I would really like to know what I am doing wrong.

    Thanks.
    Attached Files Attached Files

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Code:
    strCriteria = "Models = " & Me.Branson_Model_Number & " And Class = "" & Me.RepairClass & """""
    There are actually several mistakes in that statement:

    - The quotes around me.Repairclass should be single-quote, not double
    - You need single quotes around Me.Branson_Model_Number, as it is also string type
    - the form control referenced inMe.Branson_Model_Number has spaces in it, not underscores. Access does not recognize underscores as representing spaces, since the underscore is a valid character to use in a field or control name. To reference a field or control with spaces in the name, enclose the field or control name in square brackets: Me.[Branson Model Number]

    Once you fix those up, you have this:

    strCriteria = "Models = '" & Me![Branson Model Number] & "' And class = '" & Me!repairclass & "'"

    which works without error.

    Note: Access WILL automatically replace spaces with underscores in the names of event procedures associated with controls that contain spaces in their names.

  10. #10
    joecamel9166 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    85
    Thank you! That cleared up a lot of questions as well.

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

Similar Threads

  1. Dlookup() With Multiple Criteria (Specifically: a String, & Two dates)
    By QA_Compliance_Advisor in forum Programming
    Replies: 19
    Last Post: 07-09-2015, 05:26 PM
  2. DLookup 2nd character in a text string
    By DubCap01 in forum Forms
    Replies: 5
    Last Post: 03-06-2015, 02:37 PM
  3. How to use DLookup with string variable Criteria
    By JrMontgom in forum Programming
    Replies: 1
    Last Post: 03-09-2014, 07:55 PM
  4. Command Like DLookup but to store a string
    By viruzman in forum Programming
    Replies: 2
    Last Post: 07-28-2011, 02:04 PM
  5. # added to string with Dlookup
    By normever in forum Programming
    Replies: 1
    Last Post: 02-27-2009, 10:52 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