Report Filter

[Rohit Thomas]

Hello all,

I am trying to creating reports on the fly using criteria
that's specified in several comboxes. I have been able to
get the code to work when I only specify one combobox for
the filter. Can anyone show me the syntax to specify two
or more comboboxes in a filter.

Here's the code that works:

Dim stLinkCriteria As String
Dim stWhere As String

stDocName = "rptTellerFifty&UnderDetail"
stWhere = "FileDate = Forms!frmPrintPreviewReports!CmbDate"
DoCmd.OpenReport stDocName, acNormal, , stWhere

Here's what I need help with...syntax for combining 1 & 2
1)stWhere = "FileDate = Forms!frmPrintPreviewReports!
CmbDate"
2)stWhere = "BranchName = Forms!frmPrintPreviewReports!
CmbBranch"

Thanks for the help in advance,
Rohit Thomas

 

[Allen Browne]

Build up the string from the boxes the user did not leave blank, using AND
between the various parts. You also need to include the correct delimiter
for each field type. The example below assumes that FileDate is a Date/Time
field, and BranchName is a Text field.

Dim lngLen As Long

If Not IsNull(Me.CmbDate) Then
stLinkCriteria = stLinkCriteria & "([FileDate] = #" & _
Format(Me.CmbDate, "mm/dd/yyyy") & "#) AND "
End If

If Not IsNull(Me.CmbBranch) Then
stLinkCriteria = stLinkCriteria & "([BranchName] = """ & Me.CmbBranch &
""") AND "
End If

'etc for other boxes.

'Chop off the trailing " AND ".
lngLen = Len(stLinkCriteria - 5)
If lngLen > 0 Then
stLinkCriteria = Left$(stLinkCritiera, lngLen)
End If

DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

 

[Rohit Thomas]

Thanks Allen for the quick reply....this is great.

-----Original Message-----
Build up the string from the boxes the user did not leave blank, using AND
between the various parts. You also need to include the correct delimiter
for each field type. The example below assumes that FileDate is a Date/Time
field, and BranchName is a Text field.

Dim lngLen As Long

If Not IsNull(Me.CmbDate) Then
stLinkCriteria = stLinkCriteria & "([FileDate] = #" & _
Format(Me.CmbDate, "mm/dd/yyyy") & "#) AND "
End If

If Not IsNull(Me.CmbBranch) Then
stLinkCriteria = stLinkCriteria & "([BranchName] = """ & Me.CmbBranch &
""") AND "
End If

'etc for other boxes.

'Chop off the trailing " AND ".
lngLen = Len(stLinkCriteria - 5)
If lngLen > 0 Then
stLinkCriteria = Left$(stLinkCritiera, lngLen)
End If

DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to creating reports on the fly using criteria
that's specified in several comboxes. I have been able to
get the code to work when I only specify one combobox for
the filter. Can anyone show me the syntax to specify two
or more comboboxes in a filter.

Here's the code that works:

Dim stLinkCriteria As String
Dim stWhere As String

stDocName = "rptTellerFifty&UnderDetail"
stWhere = "FileDate = Forms!frmPrintPreviewReports! CmbDate"
DoCmd.OpenReport stDocName, acNormal, , stWhere

Here's what I need help with...syntax for combining 1 & 2
1)stWhere = "FileDate = Forms!frmPrintPreviewReports!
CmbDate"
2)stWhere = "BranchName = Forms!frmPrintPreviewReports!
CmbBranch"

Thanks for the help in advance,
Rohit Thomas

.

 

[Rohit Thomas]

One more question: I am getting an error when I compile on
the following statement: lngLen = Len(stLinkCriteria - 5)
Error: Variable required - can't assign to this expression

-----Original Message-----
Build up the string from the boxes the user did not leave blank, using AND
between the various parts. You also need to include the correct delimiter
for each field type. The example below assumes that FileDate is a Date/Time
field, and BranchName is a Text field.

Dim lngLen As Long

If Not IsNull(Me.CmbDate) Then
stLinkCriteria = stLinkCriteria & "([FileDate] = #" & _
Format(Me.CmbDate, "mm/dd/yyyy") & "#) AND "
End If

If Not IsNull(Me.CmbBranch) Then
stLinkCriteria = stLinkCriteria & "([BranchName] = """ & Me.CmbBranch &
""") AND "
End If

'etc for other boxes.

'Chop off the trailing " AND ".
lngLen = Len(stLinkCriteria - 5)
If lngLen > 0 Then
stLinkCriteria = Left$(stLinkCritiera, lngLen)
End If

DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to creating reports on the fly using criteria
that's specified in several comboxes. I have been able to
get the code to work when I only specify one combobox for
the filter. Can anyone show me the syntax to specify two
or more comboboxes in a filter.

Here's the code that works:

Dim stLinkCriteria As String
Dim stWhere As String

stDocName = "rptTellerFifty&UnderDetail"
stWhere = "FileDate = Forms!frmPrintPreviewReports! CmbDate"
DoCmd.OpenReport stDocName, acNormal, , stWhere

Here's what I need help with...syntax for combining 1 & 2
1)stWhere = "FileDate = Forms!frmPrintPreviewReports!
CmbDate"
2)stWhere = "BranchName = Forms!frmPrintPreviewReports!
CmbBranch"

Thanks for the help in advance,
Rohit Thomas

.

 

[Allen Browne]

Sorry, misplaced bracket. Try:
lngLen = Len(stLinkCriteria) - 5

 

[Rohit Thomas]

Thanks Allen,

I'm getting stuck on one more piece with the additional
comboboxes? What is the correct syntax if I'm using this
code and need to put the "AND" for the next combobox.

If Me.ChkBranch.Value = True And Not _
IsNull(Me.CmbBranch) Then
stLinkCriteria = stLinkCriteria & _
" [Transaction Office] = " & _
CInt(Me.CmbBranch)
End If

As you can see I have a long way to go before I become
familiar with the syntax...

Thanks again for your help.
Rohit

 

[Allen Browne]

The example code tacked an " AND " on the end of everything that was
appended to stLinkCritieria. Then, at the end, it chopped off the trailing "
AND ".

With that approach you can add as many combos as you need, each time using
something like this:

If Not IsNull(Me.SomeCombo) Then
stLinkCriteria = stLinkCriteria & "([SomeField] = " & Me.SomeCombo & ")
AND "
End If