VBA List Form and Date Filter -Ready to bang my head on a brick wall

[mukraker]

Hello,

I am new to VBA and have a tremendous task dropped on me. Until now I have been able to work through everything or apply code I have found online. I am trying to create a drop down list the will let me choose one or multiple items. In this case Different routes and then I want to the user to enter a begin date and end date in text boxes.

This is my code any help would be greatly appreciated.

First for the listbox
Private Function GetRoutes() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In ListFilter.ItemsSelected
stDocCriteria = stDocCriteria & "[Route] = """ & ListFilter.Column(0, VarItm) & """ OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetRoutes = stDocCriteria
End Function


Then For the Command Button:
Private Sub cmdPreview_Click()

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"


strReport = "rptShipViaGrossProfit"
strDateField = "[Date]" '
lngView = acViewPreview

If Not IsNull(GetRoutes) Then
strWhere = " ROUTE LIKE GetRoutes"

If IsDate(Me.txtStartDate) Then
strWhere = strWhere & " and (" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


MsgBox "Values Are Selected", 64, "test"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:


Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler

End Sub

 

[pbaldy]

You haven't said what's going wrong, but right off I see this:

strWhere = " ROUTE LIKE GetRoutes"

which has a couple of problems. One is that GetRoutes is inside the quotes, so it will be treated as the literal string "GetRoutes", not the result of your function. That said, GetRoutes returns a complete clause, so you just want it by itself. The handy tool that will let you see the problem is putting:

Debug.Print strWhere

right before the OpenReport line, which will let you see the completed string in the VBA Immediate window.

 

[mukraker]

pbaldy,

Thanks for the help! Moving the quotations and adding the Debug.print was very helpful.
I think I made some progress today. I am able to get all of the criteria (both routes I select from the listbox and Date Ranges in enter in to the text box) but it returns a Runtime error 3075 Syntax error (missing Operator)

This is what is says in the Immediate window
ROUTE = 'A1' " OR ROUTE = 'A3' " OR ROUTE = 'A4' " OR ROUTE = 'A7' " AND " ([Date] >= #07/10/2010#) AND ([Date] < #08/11/2010#)

Here is the code I have so far:

Private Sub cmdPreview_Click()

'On Error GoTo Err_Handler

Dim strReport As String
Dim varItem As Variant
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "rptShipViaGrossProfit"
strDateField = "[Date]"
lngView = acViewPreview '
'strWhere = "1=1"
'Build the filter string.
'If Not IsNull(Me.ListFilter) Then
For Each varItem In Me!ListFilter.ItemsSelected
strWhere = strWhere & "ROUTE = " & Chr(39) & Me!ListFilter.Column(0, varItem) & Chr(39) & " "" OR "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 4)


If IsDate(Me.txtStartDate) Then
strWhere = strWhere & " AND "" " & "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If



MsgBox "Values Are Selected", 64, "TEST"



If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere


Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler

End Sub


BTW-Nice Ayn Rand quote. I always identified with Hank Reardon.

 

[pbaldy]

We could use more Hank Rearden's!

Did you spot the problem with the string? All the extraneous quotes:

ROUTE = 'A1' " OR ROUTE = 'A3' " OR ROUTE = 'A4' " OR ROUTE = 'A7' " AND " ([Date] >= #07/10/2010#) AND ([Date] < #08/11/2010#)

Also, personally when there's a mix of AND & OR in a criteria, I like to include parentheses so there's no doubt how I want it interpreted. In other words:

A AND B OR C

could be:

(A AND B) OR C
A AND (B OR C)

I'm sure Access has a set of rules it follows when there's no parentheses, but I like clarify it, both for Access and anybody who may look at the code later.

 

[mukraker]

Success! Thanks Paul. You gave me just enough information to make me work my way through the problem so was I was able to learn in the process. As I get better in VBA I will pay it forward.

 

[pbaldy]

Excellent! Welcome to the site by the way.