Hello all,
I am currently working through the book “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 concept 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.
For those of you without this book please let me know if you need any additional information about this or any related functions/subs
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.
I am currently working through the book “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 concept 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:
[FONT=Courier New]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 " & [COLOR=DarkRed]Mid$(strWHERE, 6)[/COLOR]
BuildSQLString = True
End Function[/FONT]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.
