WHERE clause in query

  • Thread starter Marie via AccessMonster.com
  • Start date
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
 
G

Guest

Your trouble is that the code you have given is to actually create the query
that the form (or another form, subform or the continuous part of the same
form) is then based on and you are ending up with 2 WHERE clauses, if you
remove your line and change;
If Len(SQLWHERECLAUSE) > 0 Then
strSQL = strSQL & "WHERE tblCorpList.StoreStatus = 'open'" &
SQLWHERECLAUSE
Else
strSQL = strSQL & "WHERE tblCorpList.StoreStatus = 'open'"
End If
near the end of the code - I've marked with ********

another option is to just create another button called bOpenStore (or
whatever u want to call it) and then have the following code in the On_Click
event;
Private Sub bOpenStore_Click()
Me.Filter = "tblCorpList.StoreStatus = 'open'"
End Sub

Hope this helps,

TonyT..

Marie via AccessMonster.com said:
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
Click to expand...
****************Lines below here***************
If Len(SQLWHERECLAUSE) > 0 Then
strSQL = strSQL & "WHERE " & SQLWHERECLAUSE
End If
Click to expand...
*****************and above here***************
 
G

Guest

oops missed an AND in that last post;
If Len(SQLWHERECLAUSE) > 0 Then
strSQL = strSQL & "WHERE tblCorpList.StoreStatus = 'open' AND " &
SQLWHERECLAUSE
Else
strSQL = strSQL & "WHERE tblCorpList.StoreStatus = 'open'"
End If

mind for wordwrap

regards,

TonyT..
 
D

David F Cox

"WHERE tblCorpList.StoreStatus = 'open'"
That had me wondering if 'open' was actually in that table, or was the
result of a lookup. .... been there ....




.....
 
M

mmccooey1 via AccessMonster.com

Tony,

Thank you so much, that was driving me crazy!
Works like a charm!

oops missed an AND in that last post;
If Len(SQLWHERECLAUSE) > 0 Then
strSQL = strSQL & "WHERE tblCorpList.StoreStatus = 'open' AND " &
SQLWHERECLAUSE
Else
strSQL = strSQL & "WHERE tblCorpList.StoreStatus = 'open'"
End If

mind for wordwrap

regards,

TonyT..
Your trouble is that the code you have given is to actually create the query
that the form (or another form, subform or the continuous part of the same
Click to expand...
[quoted text clipped - 140 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running query with input from list boxes 5
Simplify Code-Delete Query 3
Error in multiple select in a list box code 4
HOW TO Simplify following code 2
Combox box value used in Query 1
HELP with ERROR Message 9
Create Report On the Fly 1
Multi-Select List Box 2

Top