Need help understanding Building a Query by form

watrout · March 4th, 2004, 12:35 PM

Hello all,

I am currently working through âBeginning Access 2000 VBA.â Right now Iâm on Chapter 8 â Data management techniques, and more specifically Iâm working on building an SQL statement based on multiple criteria that a user can select from a form. I REALLY want to understand this chapter because this is probably the most immediate thing I want to develop in my own database. So for those of you willing to help, here is the code from the book and Iâll explain my questions afterwards.

Code:

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "s.SalesID "
strFROM = "tblsales s "

    If chkIngredientID Then
        strFROM = strFROM & " Inner join tblIceCreamIngredient i " & _
        "ON s.fkIceCreamID = i.fkIceCreamID"
        strWHERE = " AND i.fkIngredientID = " & cboIngredientID
    End If

    If chkCompanyID Then
        strWHERE = strWHERE & " AND s.fkcompanyID = " & cboCompanyID
    End If

    If chkIceCreamID Then
        strWHERE = strWHERE & " AND s.fkIceCreamID = " & cboIceCreamID
    End If

    If chkDateOrdered Then
        If Not IsNull(txtDateFrom) Then
            strWHERE = strWHERE & " AND s.DateOrdered >= " & _
            "#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
        End If
        If Not IsNull(txtDateTo) Then
            strWHERE = strWHERE & " AND s.DateOrdered <= " & _
            "#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
        End If
    End If

    If chkPaymentDelay Then
        strWHERE = strWHERE & " AND (s.DatePaid - s.DateOrdered) " & _
        cboPaymentDelay & txtPaymentDelay
    End If

    If chkDispatchDelay Then
        strWHERE = strWHERE & " AND (s.DateDispatched - s.DateOrdered) " & _
        cboDispatchDelay & txtDispatchDelay
    End If

strSQL = "Select " & strSELECT
strSQL = strSQL & "From " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "where " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

watrout · March 4th, 2004, 12:46 PM

Sorry, I hit the post button instead of preview button. Here is the code again, this time with the questions following:

Code:

Function BuildSQLString(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String

strSELECT = "s.SalesID "
strFROM = "tblsales s "

    If chkIngredientID Then
        strFROM = strFROM & " Inner join tblIceCreamIngredient i " & _
        "ON s.fkIceCreamID = i.fkIceCreamID"
        strWHERE = " AND i.fkIngredientID = " & cboIngredientID
    End If

    If chkCompanyID Then
        strWHERE = strWHERE & " AND s.fkcompanyID = " & cboCompanyID
    End If

    If chkIceCreamID Then
        strWHERE = strWHERE & " AND s.fkIceCreamID = " & cboIceCreamID
    End If

    If chkDateOrdered Then
        If Not IsNull(txtDateFrom) Then
            strWHERE = strWHERE & " AND s.DateOrdered >= " & _
            "#" & Format$(txtDateFrom, "mm/dd/yyyy") & "#"
        End If
        If Not IsNull(txtDateTo) Then
            strWHERE = strWHERE & " AND s.DateOrdered <= " & _
            "#" & Format$(txtDateTo, "mm/dd/yyyy") & "#"
        End If
    End If

    If chkPaymentDelay Then
        strWHERE = strWHERE & " AND (s.DatePaid - s.DateOrdered) " & _
        cboPaymentDelay & txtPaymentDelay
    End If

    If chkDispatchDelay Then
        strWHERE = strWHERE & " AND (s.DateDispatched - s.DateOrdered) " & _
        cboDispatchDelay & txtDispatchDelay
    End If

strSQL = "Select " & strSELECT
strSQL = strSQL & "From " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "where " & Mid$(strWHERE, 6)

BuildSQLString = True

End Function

As indicated by the Red text I am a little confused as to why the Mid$ method is used here. The book explains it as needing to strip the " AND " clause and replace it with a " WHERE " clause. But I'm still a little foggy on how this statement actually accomplishes that action. Can anyone here clarify this idea for me? Is there an alternative method that accomplishes the same thing that is a little easier to understand?

Also, I have little to no knowledge of SQL statements. Is there a good help reference that anyone has found that could help me understand the basics of SQL?

Thanks for the help.

jurgenw · March 4th, 2004, 01:36 PM

The basic sql string should read:

'Select fields From table Where Condition' or

'Select fields From table Where Condition And Condition' or

'Select fields From table Where Condition And Condition And Condition' etc

The sql string concatenation begins each Condition with ' And ' because it does not know which condition is the first it will encounter based on arbitrary user selections. The mid$() strips out the ' And ' for the first condition so the SQL does not read:

'Select fields From table Where AND Condition' or

'Select fields From table Where AND Condition And Condition' or

'Select fields From table Where AND Condition And Condition And Condition' etc

To see this in operation modify the end of the code a bit:

If strWHERE <> "" Then strSQL = strSQL & "where " & strWHERE
MsgBox strWHERE
BuildSQLString = False

and you'll see the starting ' And ' in the Where portion of the SQL clause that will cause it to choke that the Mid$() function would otherwise strip out.

The best reference on SQL is the QBE query designer. Build your query using the visual designer tools and then choose 'SQL' from the view menu. It's free and it's always there for basic queries (not crosstabs and union queries though, but it's a start).

When learning to write SQL strings, it is often helpful to Debug.Print the string to the Immediate window whence you can copy/paste it into the SQL view of a new query. The QBE Designer will highlight SQL syntax errors that you won't otherwise get notification of.

Ciao
JÃ¼rgen Welz
Edmonton AB Canada
[email protected]

BethMoffitt · March 4th, 2004, 01:58 PM

You might go to http://rogersaccesslibrary.com and click on Other Developers Libraries, then scroll down to Olislagers, Reinier. He has a sample db called SQLExpress which is described as follows:
This database contains functions that help you create valid SQL statements in code. Not only does it handle strings (and the nasty quote problem), but also delimits dates and numbers into a suitable string for inclusion in SQL statements. Probably most useful in non-US locations/locales.

Regards,

Beth M