M
Marie via AccessMonster.com
I have a form with multiple list boxes. The user is able to select one or
multiple entries in the list boxes.
The following code uses these selections as criteria when the query is run.
However, I would also like to add a WHERE clause which will filter all stores
that are 'open' regardless of the other criteria.
The query works great until I add the WHERE clause as part of the strSQL
statement.
I do not receive an error, but the query does not return any records.
Does anyone have an idea how the WHERE clause should be set up and placed.
Any help would be greatly appreciated!
-------------------------------------------------------------
Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim StrTemp As String
Dim qryDefname As String
Dim SQLWHERE As String
Dim SQLWHERECounty As String
Dim SQLWHEREState As String
Dim SQLWHEREStoreStatus As String
Dim SQLWHERECLAUSE As String
Dim varItem As Variant
SQLWHERE = ""
SQLWHEREState = ""
SQLWHEREStoreStatus = ""
SQLWHERECounty = ""
SQLWHERECLAUSE = ""
Set db = CurrentDb
On Error Resume Next
'remove existing querydef if exists
db.QueryDefs.Delete "qryActiveStores"
strSQL = "SELECT tblCorpList.Store, tblCorpList.ACC, tblCorpList.AcqNo,
tblCorpList.EntityStatus, tblCorpList.EntityType,"
strSQL = strSQL & "tblCorpList.StoreStatus, tblCorpList.StoreType,
tblCorpList.RELLC, tblCorpList.EntityName,"
strSQL = strSQL & "tblCorpList.DBA, tblCorpList.Address, tblCorpList.City,
tblCorpList.State, tblCorpList.Zip,"
strSQL = strSQL & "tblCorpList.County, tblCorpList.StateInc, tblCorpList.
DateInc, tblCorpList.FID, tblCorpList.Open,"
strSQL = strSQL & "tblCorpList.Closed, tblCorpList.OldCorpName, tblCorpList.
OldFID, tblCorpList.Liquidated,"
strSQL = strSQL & "tblCorpList.Dissolved, tblCorpList.Comments, tblCorpList.
Owner, tblCorpList.StateRx_nbr, tblCorpList.DEA_nbr "
strSQL = strSQL & "FROM tblCorpList "
strSQL = strSQL & "WHERE tblCorpList.StoreStatus = 'open' "
If State.ItemsSelected.Count > 0 And State.ItemsSelected.Count < State.
ListCount Then
For Each varItem In State.ItemsSelected
If Len(SQLWHEREState) > 0 Then
SQLWHEREState = SQLWHEREState & " OR tblCorpList.State = '" &
CStr(State.ItemData(varItem)) & "'"
Else
SQLWHEREState = " ( tblCorpList.State = " & "'" & CStr(State.
ItemData(varItem)) & "'"
End If
Next varItem
SQLWHEREState = SQLWHEREState & " )"
End If
If County.ItemsSelected.Count > 0 And County.ItemsSelected.Count < County.
ListCount Then
For Each varItem In County.ItemsSelected
If Len(SQLWHERECounty) > 0 Then
SQLWHERECounty = SQLWHERECounty & " OR tblCorpList.County = '" &
CStr(County.ItemData(varItem)) & "'"
Else
SQLWHERECounty = " ( tblCorpList.County = " & "'" & CStr(County.
ItemData(varItem)) & "'"
End If
Next varItem
SQLWHERECounty = SQLWHERECounty & " )"
End If
If Len(SQLWHEREState) = 0 Then
Else
SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHEREState
End If
If Len(SQLWHERECounty) = 0 Then
Else
If Len(SQLWHERECLAUSE) > 0 Then
SQLWHERECLAUSE = SQLWHERECLAUSE & " AND " & SQLWHERECounty
Else
SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHERECounty
End If
End If
If Len(SQLWHERECLAUSE) > 0 Then
strSQL = strSQL & "WHERE " & SQLWHERECLAUSE
End If
'Build query def name
qryDefname = "qryActiveStores"
Set qd = db.CreateQueryDef(qryDefname, strSQL)
DoCmd.OpenQuery qryDefname
Exit_cmdRunQuery_Click:
Exit Sub
Err_cmdRunQuery_Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click
End Sub
multiple entries in the list boxes.
The following code uses these selections as criteria when the query is run.
However, I would also like to add a WHERE clause which will filter all stores
that are 'open' regardless of the other criteria.
The query works great until I add the WHERE clause as part of the strSQL
statement.
I do not receive an error, but the query does not return any records.
Does anyone have an idea how the WHERE clause should be set up and placed.
Any help would be greatly appreciated!
-------------------------------------------------------------
Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String
Dim StrTemp As String
Dim qryDefname As String
Dim SQLWHERE As String
Dim SQLWHERECounty As String
Dim SQLWHEREState As String
Dim SQLWHEREStoreStatus As String
Dim SQLWHERECLAUSE As String
Dim varItem As Variant
SQLWHERE = ""
SQLWHEREState = ""
SQLWHEREStoreStatus = ""
SQLWHERECounty = ""
SQLWHERECLAUSE = ""
Set db = CurrentDb
On Error Resume Next
'remove existing querydef if exists
db.QueryDefs.Delete "qryActiveStores"
strSQL = "SELECT tblCorpList.Store, tblCorpList.ACC, tblCorpList.AcqNo,
tblCorpList.EntityStatus, tblCorpList.EntityType,"
strSQL = strSQL & "tblCorpList.StoreStatus, tblCorpList.StoreType,
tblCorpList.RELLC, tblCorpList.EntityName,"
strSQL = strSQL & "tblCorpList.DBA, tblCorpList.Address, tblCorpList.City,
tblCorpList.State, tblCorpList.Zip,"
strSQL = strSQL & "tblCorpList.County, tblCorpList.StateInc, tblCorpList.
DateInc, tblCorpList.FID, tblCorpList.Open,"
strSQL = strSQL & "tblCorpList.Closed, tblCorpList.OldCorpName, tblCorpList.
OldFID, tblCorpList.Liquidated,"
strSQL = strSQL & "tblCorpList.Dissolved, tblCorpList.Comments, tblCorpList.
Owner, tblCorpList.StateRx_nbr, tblCorpList.DEA_nbr "
strSQL = strSQL & "FROM tblCorpList "
strSQL = strSQL & "WHERE tblCorpList.StoreStatus = 'open' "
If State.ItemsSelected.Count > 0 And State.ItemsSelected.Count < State.
ListCount Then
For Each varItem In State.ItemsSelected
If Len(SQLWHEREState) > 0 Then
SQLWHEREState = SQLWHEREState & " OR tblCorpList.State = '" &
CStr(State.ItemData(varItem)) & "'"
Else
SQLWHEREState = " ( tblCorpList.State = " & "'" & CStr(State.
ItemData(varItem)) & "'"
End If
Next varItem
SQLWHEREState = SQLWHEREState & " )"
End If
If County.ItemsSelected.Count > 0 And County.ItemsSelected.Count < County.
ListCount Then
For Each varItem In County.ItemsSelected
If Len(SQLWHERECounty) > 0 Then
SQLWHERECounty = SQLWHERECounty & " OR tblCorpList.County = '" &
CStr(County.ItemData(varItem)) & "'"
Else
SQLWHERECounty = " ( tblCorpList.County = " & "'" & CStr(County.
ItemData(varItem)) & "'"
End If
Next varItem
SQLWHERECounty = SQLWHERECounty & " )"
End If
If Len(SQLWHEREState) = 0 Then
Else
SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHEREState
End If
If Len(SQLWHERECounty) = 0 Then
Else
If Len(SQLWHERECLAUSE) > 0 Then
SQLWHERECLAUSE = SQLWHERECLAUSE & " AND " & SQLWHERECounty
Else
SQLWHERECLAUSE = SQLWHERECLAUSE & SQLWHERECounty
End If
End If
If Len(SQLWHERECLAUSE) > 0 Then
strSQL = strSQL & "WHERE " & SQLWHERECLAUSE
End If
'Build query def name
qryDefname = "qryActiveStores"
Set qd = db.CreateQueryDef(qryDefname, strSQL)
DoCmd.OpenQuery qryDefname
Exit_cmdRunQuery_Click:
Exit Sub
Err_cmdRunQuery_Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click
End Sub