Hi!
Working on a similar project where users can make selections from one of two listboxes to run a query.
My code worked fine for just one listbox ('Phases').
Phase is numerical and Zone is text.
Having trouble incorporating the right code to have Access check one list OR the other ('Zones').
The trouble started when I added 'Zones' into the mix.
My latest attempt returns an error message "Data Type Mismatch."
Any advice is appreciated.
Code:
Option Compare Database
Private Sub btn_RunAdHocQry_Click()
On Error GoTo Err_btn_RunAdHocQry_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
'-----------------------------------------------------------------------------
Set MyDB = CurrentDb()
'------------------------------------------------------------------------------
strSQL = "SELECT * FROM AREA_GROWTH2"
'------------------------------------------------------------------------------
'Build the IN string by looping through the listbox
For i = 0 To lst_AdHocPhase.ListCount - 1 Or lst_Zone.ListCount - 1
If lst_AdHocPhase.Selected(i) Or lst_Zone.Selected(i) Then
If lst_AdHocPhase.Column(0, i) = "All" Or lst_Zone.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lst_AdHocPhase.Column(0, i) & "'," Or strIN = strIN & "'" & lst_Zone.Column(0, i) & "',"
End If
Next i
'------------------------------------------------------------------------------
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Phase] Or [Zone] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'------------------------------------------------------------------------------
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
'------------------------------------------------------------------------------
MyDB.QueryDefs.Delete "qry_AdHoc"
Set qdef = MyDB.CreateQueryDef("qry_AdHoc", strSQL)
'------------------------------------------------------------------------------
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qry_AdHoc", acViewNormal
DoCmd.Close
strfilename = "C:\Documents and Settings\" & Environ("username") & "\Desktop\AGDB_AdHocQuery.xls "
DoCmd.TransferSpreadsheet acExport, , "qry_AdHoc", strfilename
MsgBox "Successful Export to Desktop", , "File Export"
'------------------------------------------------------------------------------
'Clear listbox selection after running query
For Each varItem In Me.lst_AdHocPhase.ItemsSelected
Me.lst_AdHocPhase.Selected(varItem) = False
Next varItem
'-------------------------------------------------------------------------------
Exit_btn_RunAdHocQry_Click:
Exit Sub
Err_btn_RunAdHocQry_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection from the list" _
, , "Selection Required !"
Resume Exit_btn_RunAdHocQry_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_btn_RunAdHocQry_Click
End If
End Sub