using VBA to build an SQL statement

[mbath20110]

Am trying to use VBA to dynamically build a SQL statement that will end up being the data source for another form. (want to enable my users to enter in criteria as they see fit on a search form, collect their criteria and then build the statement programatically in SQL).

I'm so close I can taste it but I must be missing something simple.

I want my SQL SELECT statement to include reference to a combo box called cboClient but can't seem to figure out the best way to include cboClient in my SELECT statement. I 'believe' my snag has to do with my syntax (doesn't it always) in terms of single quotes, double quotes, brackets, etc.

Would appreciate any help ....

Mitch

 

[dcx693]

How about posting your code? Most importantly that part where you think your syntax is off. Here's a good reference article: Use Variables in SQL behind forms

 

[namliam]

Welcom to the site....

did you try searching?

a search for Build SQL with VBA returns 48 results which must contain answers to your question (I am sure of it ! since i answered a couple of em...) Post back with specific questions.

Regards

The Mailman

 

[Jbozward]

This looks a little daunting but here is an example with a lot removed.
The basics are that quotes inside strings are changed to " & stquo & " and that I used a line per SQL option.
In the end, I joined the strings together, and created a query.
Been as you are so close to taste it, you should understand!
Give me a shout if you dont......



stquo = Chr(34)
Set dbs = CurrentDb
strSQLselect = "SELECT Format([Full calculated date]," & stquo & "yyyy" & stquo & ") AS Month "
strSQLwhere = "WHERE ((([Query - Audi - Scrap entry].[Scrap code lookup]) Like " & Fault & ") AND (([Query - Audi - Scrap entry].[Full calculated date]) Between #" & RStart & "# And #" & RStop & "#) AND (([Query - Audi - Scrap entry].[Shift code]) Between " & ShiftL & " And " & ShiftU & ")) "
strSQLgroupby = "GROUP BY Format([Full calculated date]," & stquo & "yyyy" & stquo & "), [Query - Audi - Scrap entry].[Scrap code lookup] "
strSQLorder = "ORDER BY Format([Full calculated date]," & stquo & "yyyy" & stquo & ") "
If Choice = 1 Then srtSQLpivot = "PIVOT [Query - Audi - Scrap entry].[Comments field]; "
If Choice = 2 Then srtSQLpivot = "PIVOT [Query - Audi - Scrap entry].[Defect grid reference]; "
strSQLtransform = "TRANSFORM Count([Query - Audi - Scrap entry].ID) AS [The Value] "
strSQLfrom = "FROM [Query - Audi - Scrap entry] "
Set qdf = dbs.CreateQueryDef("Crosstab - Audi - By fault code", strSQLtransform & strSQLselect & strSQLfrom & strSQLwhere & strSQLgroupby & strSQLorder & srtSQLpivot)
stDocName = "Report - Audi - Fault code"
DoCmd.OpenReport stDocName, acViewPreview

 

[Mile-O]

This looks a little daunting but here is an example with a lot removed.

It wouldn't look as daunting if you used the [CODE] and [/CODE] tags to surround it.

 

[Jbozward]

Afraid you have lost me there Mile-O-Phile.......

 

[Mile-O]

vbCode Tags

The code tags to do this:

Public Sub SimpleMessage()
    MsgBox "Hello World!", vbInformation, "Simple"
End Sub

makes huge lumps of code easier to read...

 

[Jbozward]

Thanks,

I'll remember that!

 

[mbath20110]

code included

folks,

thanks for all the responses ...

still a little stumped. here's my code:

Set qdf = CurrentDb.CreateQueryDef("qryExample")
qdf.SQL = "SELECT tblCLIENT.ClientName FROM tblCLIENT WHERE (tblCLIENT.ClientName)=" & Me!cboClient

I have declared the necessary variables preceding this entry and this code is assigned to the OnClick event of a button on my search form (for right now, I'm just trying to get this code to work, i.e. create a query called qryExample with my criteria set).

After I make my selection from the cboClient dropdown and then click the button, I keep getting varying error messages basically saying that my SQL statement is incorrect. To be clear, I AM manually deleting the qryExample each time I attempt to execute the code.

any suggestions ????

 

[fishonland]

You are right those darn quotations are messing you up.

Try this

Set qdf = CurrentDb.CreateQueryDef("qryExample") 
qdf.SQL = "SELECT tblCLIENT.ClientName FROM tblCLIENT WHERE (tblCLIENT.ClientName)=" & chr(34) & Me!cboClient & chr(34)

Your problem exists because you are trying to set the criteria for a text field without having quotations around the data.

Hope this helps

 

[dcx693]

If you read the link I provided, you could've solved it on your own.

 

[mbath20110]

apologies to dcx ...

didn't get your response before my last post.

thanks for your help ....

 

[JoyScho]

I actually did something like this myself. Here is my code snipet. I hope it is helpful:

Private Sub CmdDisplaySelected_Click()
On Error GoTo Err_CmdDisplaySelected_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim intCurrentRow As Integer
    Dim anySelected As Boolean
    Dim ctlSource As Control
    
    Dim rst As Recordset
    Dim selectString As String
    
    stDocName = "FORM_TO_OPEN"
    stLinkCriteria = "([Field_1]="""
    
    anySelected = False
    Set ctlSource = Me!ListBox1
    
    For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then
            If anySelected = True Then
                stLinkCriteria = stLinkCriteria & " Or ([Field_1]="""
            End If
            anySelected = True
            stLinkCriteria = stLinkCriteria & ctlSource.Column(0, intCurrentRow) & """)"
        End If
    Next intCurrentRow
    
    If anySelected = False Then
        MsgBox "No Sites Selected"
    Else
        selectString = "Select * from [Table_1] where " & stLinkCriteria
        Set rst = New Recordset
        rst.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        If rst.EOF Then
            'Didn't find any matches
            MsgBox "No Records found for selected criteria."
        Else
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        End If
    End If

Exit_CmdDisplaySelected_Click:
    rst.Close
    Set rst = Nothing
    Exit Sub

Err_CmdDisplaySelected_Click:
    MsgBox Err.Description
    Resume Exit_CmdDisplaySelected_Click
    
End Sub

Hope that helps!