Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2014
    Posts
    12

    Unanswered: Issue with multiple where conditions in vba

    I am trying to return a text for a particular record based on where criteria below is my statement but I am getting a datatype mismatch any help would be great.

    Code:
    b = DMax("[Production_Off_Date]", "MeterStatement_Table", "[wellid]=forms![frmMeterStatementchgsManual]![txtwellid]")
    c = DLookup("[NESC_Plate_Size]", "MeterStatement_Table", "[wellid]=forms![frmMeterStatementchgsManual]![txtwellid] AND [Production_off_date] = '" & b & "'")

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    date literals should be delimited with a pair of # and in either ISO yyyy-mm-dd or US foramat mm/dd/yyyy
    text literals should be delimited with pairs of ' or "
    = #" & format(b,"yyyy-mm-dd") & "#")

    Id strongly recommend that you use meaningful variable names b & c just dont' cut it
    Last edited by healdem; 10-01-14 at 18:51. Reason: cockup in sample code!
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    12
    Thank you healdem just plain forgot to use the hash

  4. #4
    Join Date
    Jun 2014
    Posts
    12
    Now just having an issue with '" here's what I have
    Code:
    LastProd = DMax("[Production_Off_Date]", "MeterStatement_Table", "[wellid]=forms![frmMeterStatementchgsManual]![txtwellid]")
    PlateSize = DLookup("[NESC_Plate_Size]", "MeterStatement_Table", "[wellid]=forms![frmMeterStatementchgsManual]![txtwellid] AND [Production_off_date] =#'" & LastProd & "#'")

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and what does that look like

    I'd recommend that you assign the value of SQL (effectivley the 3rd parameter is a SQL WHERE clause without using hte word where) to a variable so its easy to debug
    Code:
    strSQL = "[wellid]=forms![frmMeterStatementchgsManual]![txtwellid]"
    LastProd = DMax("[Production_Off_Date]", "MeterStatement_Table", strSQL)
    strSQL = "[wellid]=forms![frmMeterStatementchgsManual]![txtwellid] AND [Production_off_date] =#'" & LastProd & "#'"
    PlateSize = DLookup("[NESC_Plate_Size]", "MeterStatement_Table", strSQL)
    put a breakpoint on the first assignment to strSQL
    then step through the code.
    if you dont' know how to use the debugger, then now is a good time to start

    if you can't be bothered to use the debugger (boo, hiss, shame) then put a msgbox statement after each assignement of strSQL and examine the value.

    hint: its all about
    Now just having an issue with '" here's what I have
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •