vba code with query

[Esmatullaharifi]

I have a form with three text boxes and one button. I want to use these three text boxes as parameters for specific query, thus i want a code that has the ability to pass these three value to query parameters or another code to solve this problem.


I need your help dear guise.
Thanks,

[JoeM]

If you open any query and switch to SQL View, you will see the SQL code behind the query.
What I often do in these situations, is use the Text Box selections and VBA to build the SQL code I need for the query. Then assign the built SQL code to an existing query, and open it.

What I do to keep the SQL code in VBA small is I first create a query that has all my records and calculations (without the criteria). I then save it (let's call it Query1). I then create a second query which selects all the records from Query1 (using the asterisk) and adds in my Criteria. Let's call that Query2.

So, then my SQL code will build the SQL code for Query2 based on my Criteria selections.

Here is some sample code that builds that code, assuming the first text box is a numeric entry and the second is a string entry. I then open a Report at the end which uses Query2 as its Control Source.

Private Sub cmdRunReport_Click()

    Dim mySelect As String
    Dim myCriteria As String
    Dim mySQL As String
    
'   Get basis of SQL code
    mySelect = "SELECT Query1.* FROM Query1 WHERE" 
    
'   Add first criteria (numeric example)
    myCriteria = " [Field1]=" & Me.TextBox1

'   Add second criteria (string example)
    myCriteria = myCriteria & " AND [Field2]=" & Chr(34) & Me.TextBox2 & Chr(34)

'   Build complete SQL string
    mySQL = mySelect & myCriteria";"
    
'   Assign SQL to query
    CurrentDb.QueryDefs("Query2").SQL = mySQL
    
'   Open report
    DoCmd.OpenReport "Report1", acViewPreview, "", ""

End Sub

A few things to note:
- Chr(34) returns double-quotes. All string values must be enclosed in quotes in SQL.
- To see exactly how your SQL query should look, build an "example" in Query2, switch to SQL View, and note the code (print or copy). This is what you are trying to build with VBA. To help debug, you can add a line of code after you build the SQL code to return its contents and compare to what you are trying to build, i.e.

...
'   Build complete SQL string
    mySQL = mySelect & myCriteria";"
    MsgBox mySQL
...