Query Help

[Guest]

I have a form where the user can enter from 1 to 4 different criteria in text
boxes. The text boxes are: ReceiptNum, Date, BuyerName, Staff. What I'm
trying to accopmplish is to run a query based on that criteria. What I want,
for example, is if I'm looking for a record of all sales on 11/30/2006, then
I enter this in the Date field and leave the other fields empty. All sales
on that date appear, regardless of Staff. If I'm looking for all sales on
11/30/2006 by Staff John Doe, then I enter 11/30/2006 in the Date field and
John Doe in the Staff field and leave the other 2 fields empty, and it should
pull only those records for that date where John Doe was the staff. I have
the query set up so that it references the form fields for it's criteria, but
it's not working. For example, if I enter criteria for sales on 11/30/2006
by Staff John Doe, I end up with all records on 11/30/2006, not just John
Doe's.

Any help is appreciated!

 

[Guest]

Tara,

What does your query currently look like?

I usually write the criteria for a query like this in a command buttons
click event. You could probably create a query that has all of the right
stuff in it, but I prefer to create the where clause from scratch. something
like:

Private Sub cmd_Query_Click

Dim strSQL as string
dim varCriteria as variant
Dim qdf as DAO.Querydef

' I make this a variant so that I can concatenate to it and Access will
ignore the variable if it does not already have a value
varCriteria = Null

if NOT isnull(me.txt_receiptNum) then
varCriteria = "[ReceiptNum] = " & me.txt_ReceiptNum
endif

'In the next three If statements, I use the + to concatanate the " AND "
to
'the varCriteria variable. If varCriteria already has a value, you need the
AND
'between query field, but if varCriteria is still NULL, then using the + as
the
'concatenate operator will cause Access to leave the " AND " section out
if NOT isnull(me.txt_Date) then
varCriteria = (varCriteria + " AND ") _
& "[Date] = #" & me.txt_Date & "#"
endif

if NOT isnull(me.txt_BuyerName) then
varCriteria = (varCriteria + " AND ") _
& "[BuyerName] = " & me.txt_BuyerName
endif

if NOT isnull(me.txt_Staff) then
varCriteria = (varCriteria + " AND ") _
& "[Staff] = " & me.txt_BuyerName
endif

'If at least one of the items has data to query on, then modify the
query def of
'the query. If you are using these criteria to open another form, or to
open a
'report, you do not need to use the code in the following section, just use
the
'OpenForm or OpenReport method and pass it varCriteria as the parameter for
the
'WHERE clause. If you actually need to modify the query, say for the data
source
'for a listbox, then you could do the following.
if NOT isnull(varCriteria) then
Set qdf = currentdb.querydefs("yourQueryName")
strSQL = qdf.sql
'if the query already has a WHERE clause, delete it
'this would require additional code of the query contains a Group By
clause
if instr(strSQL, "WHERE") then
strSQL = LEFT(strSQL, instr(strsql, "Where") + 6) _
& "WHERE " & strCriteria
qdf.sql = strSQL
endif
qdf.close
set qdf = nothing
endif

'Do something here (requery a list or control that uses this query)

End sub

HTH
Dale