Set multiple query definitions in VBA code

[JoanieB]

I have code that runs thru a record set and for each record, sets the dept as a query criteria and then runs a union query based on the current dept. I am wondering if there's a way to have one of the queries run thru with multiple criteria (ie: where it says dept = TP, I would like to change the criteria to = TP or Bos.) I know this updated code that follows is not 100%, but my main concern is the strDept = Bos or TP line. How (if this is possible) do I arrange the quotes correctly?

Here's what I have - again, I know the code isn't all done, I just can't seem to figure out the "double criteria" definition...

Do While Not rstDepts.EOF
strDept = rstDepts!Department
If strDept = "Bos" Then
rstDepts.MoveNext
Else
If strDept = "T-P" Then
strDept = "'Bos' Or 'T-P'"
Set rstTemp = CurrentDb.OpenRecordset("qry_union1&quot
If rstTemp.RecordCount > 0 Then
strFileName = strpath & strDept & StrCurrDte & ".xls"
DoCmd.OutputTo acOutputQuery, "qry_union1", acFormatXLS, strFileName
End If
rstDepts.MoveNext
Else
Set rstTemp = CurrentDb.OpenRecordset("qry_union1&quot

If rstTemp.RecordCount > 0 Then
strFileName = strpath & strDept & StrCurrDte & ".xls"
DoCmd.OutputTo acOutputQuery, "qry_union1", acFormatXLS, strFileName
End If
rstDepts.MoveNext
End If
End If
Loop

 

[JeremyNYC]

When looking for records with one of several possible values, you can't do
... WHERE MyField = 'Yadda' OR 'Blah'
you have to do
... WHERE MyField = 'Yadda' OR MyField = 'Blah'

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[JoanieB]

Does anybody actually READ what questions these things are asking?

 

[JeremyNYC]

JoanieB,

Go cry yourself to sleep now, out of self pity because no one will do your entire project for you. I certainly will not read any of YOUR questions from now on.

You said that your main concern was the two criteria. I showed you how to do that. If you've got more problems, ask more questions. But don't go around moaning because you can't get other people to do your work.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[MichaelRed]

mmmmmmmmmm testy, testy ........ testy

The 'criteria' (strDept) part only appears in the path portion of strFileName, which is, as far as I can see, not used within the procedure snippet. Since the question appears to direct the 'alternate' selection criteria as part of a query, the process is a bit confusing, perhaps JoanieB could actually clarify what they are attempting here or at least elaborate on the snippet and what some of the statements are intended to be used for / with. MichaelRed
m.red@att.net

Searching for employment in all the wrong places