Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unanswered: Data Type Mismatch error in my SQL code

    Hello,

    I am receiving this data type mismatch error in the following SQL statement.

    Me.RecordSource = "SELECT * FROM wo_tracker WHERE wo_tracker.Wo_Num ='" & cboWONum & "';"

    I am trying to filter my Work Order numbers using a combo box. The wo_num data type is Number. Is my Select statement incorrect?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Remove the quote marks delimuting cbowonum.
    you only need to delimt:-
    Text/string values with either a quote ' or doubke quote "
    date values with the hash symbol #, dates must be in yyyy/mm/dd or mm/dd/yyyy format
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Ugh! I'm doing something wrong.

    Me.RecordSource = "SELECT * FROM wo_tracker WHERE wo_tracker.Wo_Num = 'cboSiteInfo'"

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As you wrote it, the litteral "cboSiteInfo" is part of the expression. Try:
    Code:
    Me.RecordSource = "SELECT * FROM wo_tracker WHERE wo_tracker.Wo_Num = '" & Me.cboSiteInfo.Value & "'"
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    learn how to use the integrated debugger...
    the reason, you will quickly see what your code is doing as opposed to what you think its doing
    Code:
    Me.RecordSource = "SELECT * FROM wo_tracker WHERE wo_tracker.Wo_Num = 'cboSiteInfo'"
    ..assuming wo_tracker.Wo_Num is numeric
    Code:
    Me.RecordSource = "SELECT * FROM wo_tracker WHERE wo_tracker.Wo_Num = " & cboSiteInfo
    ..assuming wo_tracker.Wo_Num is string
    Code:
    Me.RecordSource = "SELECT * FROM wo_tracker WHERE wo_tracker.Wo_Num = '" & cboSiteInfo & "'"
    when building a string for passing to the SQL engine and you are wanting to use variable or controls from you Access app then you need to 'drop out/in' of VBA string mode to add such a value

    say cbositeinfo contains A12345
    what you code currently does is
    SELECT * FROM wo_tracker WHERE wo_tracker.Wo_Num = 'cboSiteInfo'
    as opposed to
    SELECT * FROM wo_tracker WHERE wo_tracker.Wo_Num = 'A12345'
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    There's no need to set the recordsource on the fly.
    Set the RSrc to a query. Let the query have the variable.

Posting Permissions

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