You could probably use the SQL statement you have created; however, since
you have not posted your VB it is difficult to say.
Did you look at the references I posted? If so, did you have problems
understanding how to adapt one or both approaches to your situation?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
John
I was unable to adapt the code that you pointed out to me. The first
reference did not make sense because it looks to be written in VB and
I am not sure how to use this in a query. The second link you
provided I had previously used to build my report data upon. I posted
the VB code for that report below.
Thanks
Private Sub Report_Open(Cancel As Integer)
'This routine looks at all the values selected on the Lease Options
form and creates the "IN" statements
'That will be used in the SQL to generate the report with the multi
selects
Dim SQL As String 'variable used to hold SQL
statement
Dim strAgency2 As String
Dim x As Variant 'a variable to hold the values
selected from the multi list
Dim strdelimiter As String 'delimiter used for working with
text based "'s
Dim lngLen As Long 'variable used to hold length of
values and
Dim strAgency As String 'string to hold the IN clause
for Agency list
Dim strMarket As String 'string to hold the IN clause
for Market list
Dim strCounty As String 'string to hold the IN clause
for County list
Dim strCity As String 'string to hold the IN clause
for City list
Dim dtStartDate As Date 'Variable to hold report
starting date parameter
Dim dtEndDate As Date 'Variable to hold report ending
date parameter
On Error GoTo myerrhandler
strdelimiter = "'"
'check if the start date is blank and include all records
If Forms!frmSOFLeases.Start_date = "" Then
dtStartDate = #1/1/1900#
Else
dtStartDate = Forms!frmSOFLeases.Start_date
End If
'check if the end date is blank and include all records
If Forms!frmSOFLeases.End_date = "" Then
dtEndDate = #1/1/3000#
Else
dtEndDate = Forms!frmSOFLeases.End_date
End If
'check the values of each of the list boxes and ensure that at least 1
' value is selected from each of the 4 list boxes
If Forms!frmSOFLeases.lstbx_Agency.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the Agency list", vbOKOnly,
"Select a value!"
DoCmd.CancelEvent
Exit Sub
ElseIf Forms!frmSOFLeases.lstbox_Market.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the Market list", vbOKOnly,
"Select a value!"
ElseIf Forms!frmSOFLeases.lstbox_County.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the County list", vbOKOnly,
"Select a value!"
ElseIf Forms!frmSOFLeases.lstbox_City.ItemsSelected.Count < 1 Then
MsgBox "You must select a value from the City list", vbOKOnly, "Select
a value!"
'Else: DoCmd.OpenReport strReportName, acViewPreview
End If
'Find all values selected in the Agency list box and create the IN
statement
With Forms!frmSOFLeases.lstbx_Agency 'Look at the list
box with the agency values
For Each x In .ItemsSelected 'create routine to
loop through the selected values
If Not IsNull(x) Then 'check for nulls
strAgency = strAgency & strdelimiter & .ItemData(x) &
strdelimiter & "," 'Set strAgency equal to all values selected
End If
Next
End With
'Trim the strAgency variable and add the "IN clause used in the query
If Len(strAgency) > 0 Then
lngLen = Len(strAgency) - 1 'Trim the last
comma off of strAgency
strAgency = "[Agency] IN (" & Left(strAgency, lngLen) & ")"
'strAgency now contains the IN statement needed
End If
x = "" 'reset "X" equal
to nothing
'Find all values selected in the Market list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_Market
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strMarket = strMarket & strdelimiter & .ItemData(x) &
strdelimiter & "," 'store values in strMarket
End If
Next
End With
If Len(strMarket) > 0 Then
lngLen = Len(strMarket) - 1 'Trim the last comma from
strMarket
strMarket = "[Market] IN (" & Left(strMarket, lngLen) & ")"
'strMarket now contains the IN statement needed
End If
x = "" 'reset "X" equal
to nothing
' Find all values selected in the County list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_County
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strCounty = strCounty & strdelimiter & .ItemData(x) &
strdelimiter & "," 'store values in strCounty
End If
Next
End With
If Len(strCounty) > 0 Then
lngLen = Len(strCounty) - 1 'Trim the last comma
strCounty = "[County] IN (" & Left(strCounty, lngLen) & ")"
'strCounty now contains the IN statement
End If
x = "" 'reset "X" equal
to nothing
' Find all values seleceted in the City list box and create the IN
statement
With Forms!frmSOFLeases.lstbox_City
For Each x In .ItemsSelected ' Loop through the values
If Not IsNull(x) Then 'check for nulls or no item
selected
strCity = strCity & strdelimiter & .ItemData(x) & strdelimiter
& "," 'store values in strCity
End If
Next
End With
If Len(strCity) > 0 Then
lngLen = Len(strCity) - 1 'Trim the last comma
strCity = "[City] IN (" & Left(strCity, lngLen) & ")" 'strCity now
contains the IN statement
End If
'Generate the SQL statment for the reports record source
SQL = "Select * from [SOF Leases w Commission] Where " & strAgency & "
AND" & strMarket & _
" AND" & strCounty & " AND" & strCity & " AND" & "[Ending Date]
Between " & "#" & dtStartDate & "#" & " and " & "#" & dtEndDate & "#"
DoCmd.RunSQL (SQL)
Me.RecordSource = SQL
DoCmd.Maximize
Exit Sub
myerrhandler:
MsgBox "Error # is : " & Err.Number & Err.Description
End Sub- Hide quoted text -
- Show quoted text -