none
I HAVE DEVELOPED A SOFTWARE USING EXCEL VBA IN WHICH I STORE DATA IN A SEPARATE ACCESS DB FILE AND I FETCH IT IN EXCEL AS PER MY NEED. BUT I AM UNABLE TO FETCH DATA USING WHERE CRITERIA IN SQL QUERY. RRS feed

  • General discussion

  • SQL = SQL & "WHERE [Purchase_date] <= textbox1.text"

    purchase_date=field name in access

    textbox1=a textbox in userform which contains date value

    Saturday, November 30, 2019 3:58 PM

All replies

  • You could build the SQL statement by concatenating the text box value like below instead of inside the SQL statement:

    SQL = SQL & "WHERE [Purchase_date] <= '" & textbox1.text & ';"

    However, instead of concatenating values that vary by execution as literals, it would be better to use a parameterized query for the reasons detailed here. Below is example ADODB.Command code snippet to add a parameter to the query:

    SQL = SQL & "WHERE [Purchase_date] <= ?;"
    command.CommandText = SQL
    Set parmPurchaseDate = command.CreateParameter("@PurchaseDate", adDate, adParamInput, , textbox1.text)
    command.Parameters.Append(parmPurchaseDate)


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Saturday, November 30, 2019 4:34 PM
    Moderator