Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    New Zealand
    Posts
    47

    Unhappy Unanswered: VBA Sql statement with form value!

    I am trying to use a value from a combobox in an sql statement. But it doesn't work- when i use this line

    'sSQL = "SELECT * FROM QuotationNew WHERE (((QuotationNew.[Quote No])= Me.Quote_No))" 'creates a new instance of the object

    if i print Me.Quote_No to the debug window it displays the value. What is wrong with mysql statement.

    this statement works when i hard code the value.

    sSQL = "SELECT * FROM QuotationNew WHERE (((QuotationNew.[Quote No])= 22))"

    How do i get the value form the form into my sql statment. This statment id going to be used to populate a recordset. All in VBA

    thank you anybody.
    Nerdy Girl

  2. #2
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    try this if me.quote_no is a numeric data type:
    sSQL = "SELECT * FROM QuotationNew WHERE (((QuotationNew.[Quote No])= " & Me.Quote_No & "))"

    and this if it's a character type:
    sSQL = "SELECT * FROM QuotationNew WHERE (((QuotationNew.[Quote No])= """ & Me.Quote_No & """))"

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487

    Re: VBA Sql statement with form value!

    Code:
    sSQL = "SELECT * FROM QuotationNew WHERE (((QuotationNew.[Quote No])= " & CLng(Me.Quote_No) & "))"
    And if that fails try:

    Code:
    sSQL = "SELECT * FROM QuotationNew WHERE (((QuotationNew.[Quote No])= '" & CLng(Me.Quote_No) & "'))"
    Make sure the data type you are supplying to the SQL statement matches the tables' field data type. Also, make sure that the Me.Quote_No combo box is actually supplying what you expect...test it.

    Code:
    MsgBox Me.Quote_No
    If you don't get what you expect, then you may need to take a closer look at the properties for your combo box. You may find that you may need to use: Me.Quote_No.Column(x) in order to ooooz out the data you want

Posting Permissions

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