Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 4th, 2004, 12:35 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need help understanding Building a Query by form

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
 
Old March 4th, 2004, 12:46 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old March 4th, 2004, 01:36 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
jwelz@hotmail.com
 
Old March 4th, 2004, 01:58 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: Houston, Texas, USA.
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Query building anjurenjith SQL Language 0 August 2nd, 2007 05:07 AM
running out of field in query building yixchen Access 2 December 19th, 2005 10:19 AM
Building Cross Query... ZArrinPour SQL Server 2000 4 October 19th, 2005 06:33 PM
Building accounting receivable query mustafayildirim@msn.com Access 1 April 26th, 2005 06:45 AM
Building a simple form - I thought misterqj Access 3 April 27th, 2004 01:07 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.