Writing SQL in VBA

[Guest]

In the OnClick event of a button on a form I have the following code that
creates the filter for my report. My report includes a chart. The report
filter part of this code works great.

I'm having problems with the last bit of code (qdf_Chart.SQL = "Select . . .
), which is setting the filter for the query which the report is using. I
think I'm on the right track but that I'm not writing it correctly. I'm sure
I've tried everything, but obviously not. My chart is actually using a query
called "RCVGDeliveryMethodTrucksCrosstab", which uses query,
"RCVGDeliveryMethodTrucksChart1". The filter should contain text in the
Shift field and a between begin and end date in the DelDate field. Any idea
how I'm writing this incorrectly. Thanks so much

Private Sub cmdApplyFilter_Click()
Dim db As DAO.Database
Dim qdf_Chart As DAO.QueryDef
Dim qdf_Unfiltered As DAO.QueryDef
Dim sql1 As String
Dim sql2 As String
Dim VarItem As Variant
Dim strDock As String
Dim strShift As String
Dim strMethod As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strFilter As String
Dim strFilterChart

Set db = CurrentDb
Set qdf_Unfiltered = db.QueryDefs("RCVGDeliveryMethodQuery")
Set qdf_Chart = db.QueryDefs("RCVGDeliveryMethodTrucksCrosstab")


'Check for Beginning and Ending date
If Len(Me.cmdBeginDate.Value & "") = 0 Then
MsgBox "You must type a Beginning date"
Exit Sub
End If

If Len(Me.cmdEndDate.Value & "") = 0 Then
MsgBox "You must type an Ending date"
Exit Sub
End If


' Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "RCVGDelMethodReport") <>
acObjStateOpen Then
DoCmd.OpenReport "RCVGDelMethodReport", acViewPreview
End If


' Build criteria string from method listbox
For Each VarItem In Me.cmdMethod.ItemsSelected
strMethod = strMethod & ",'" & Me.cmdMethod.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strMethod) = 0 Then
strMethod = "Like '*'"
Else
strMethod = Right(strMethod, Len(strMethod) - 1)
strMethod = "IN(" & strMethod & ")"
End If


'Build criteria sting from Shift listbox
For Each VarItem In Me.cmdShift.ItemsSelected
strShift = strShift & ",'" & Me.cmdShift.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strShift) = 0 Then
strShift = "Like '*'"
Else
strShift = Right(strShift, Len(strShift) - 1)
strShift = "IN(" & strShift & ")"
End If

'Build criteria string from Dock listbox
For Each VarItem In Me.cmddock.ItemsSelected
strDock = strDock & ",'" & Me.cmddock.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strDock) = 0 Then
strDock = "Like '*'"
Else
strDock = Right(strDock, Len(strDock) - 1)
strDock = "IN(" & strDock & ")"
End If


'Build Beginning & Ending date parameter
If Not IsNull(Me.cmdBeginDate) Then
datBeginDate = Me.cmdBeginDate
End If
If Not IsNull(Me.cmdEndDate) Then
datEndDate = Me.cmdEndDate
End If



' Build filter string [name] is the table field name
strFilter = "[Shift]" & strShift & " AND [Dock] " & strDock & " AND
[DeliveryMethod] " & strMethod & " AND [DelDate] Between #" & datBeginDate &
"# and #" & datEndDate & "#"
strFilterChart = "[Shift]" & strShift & " AND [DelDate] Between #" &
datBeginDate & "# and #" & datEndDate & "#"

' Apply the filter and switch it on
With Reports![RCVGDelMethodReport]
.Filter = strFilter
.FilterOn = True
.DelReportTitle.Value = "Receiving Method Dock " &
Me.cmdBeginDate.Value & " - " & Me.cmdEndDate
'.DelChartReportTitle.Value = "Receiving Method Dock by Shift
" & Me.cmdBeginDate.Value & " - " & Me.cmdEndDate
End With


qdf_Chart.SQL = "SELECT RCVGDeliveryMethodTrucksChart1.DelDate,
RCVGDeliveryMethodTrucksChart1.Shift,
RCVGDeliveryMethodTrucksChart1.Trailers" & _
"FROM RCVGDeliveryMethodTrucksChart1 WHERE " & strFilterChart & ""


End Sub

 

[John Vinson]

The filter should contain text in the
Shift field and a between begin and end date in the DelDate field. Any idea
how I'm writing this incorrectly. Thanks so much

I think you hit my personal stack overflow error on that one... <bg>

What error are you getting? I would suggest using debug mode (set a
breakpoint and step through the code) to see what's actually being
stored in your SQL string. You can then copy and paste this entire
string into the SQL view of a new query and open it, to see what error
messages it generates.

John W. Vinson[MVP]