Re: I know this quesion has been asked a million times but.....

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Through the graphical query designer, it's a case of ensuring that the Date
criteria exists on every row of criteria.

When you have multiple rows of criteria in the builder, the criteria in a
single row are And'ed together, and each row is Or'ed together.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jackie" <jackie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:632E8224-8BB0-463E-9DE4-2B50436BD4E9@xxxxxxxxxxxxxxxx
Douglas,
Sorry for the multitude of emails. I recreated the query yet again, and
this
time it worked. Thanks for your help. By reading your code, I was able to
better understand what was going on in the query designer. Thanks Again

"Douglas J. Steele" wrote:

Assuming I didn't make any mistakes with parentheses, either

SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC,
Jan_08_Orders.[Material Group], [material group] & " " & [niin] AS NSN,
Jan_08_Orders.CreatedOn, Jan_08_Orders.[Order Qty],
Jan_08_Orders.[Shipped
Qty], Jan_08_Orders.[Net Value]
FROM Jan_08_Orders
WHERE ((Jan_08_Orders.[Profit Center] Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND (Jan_08_Orders.DoDAAC Like
"*"
& [Forms]![QBF_form]![dodaac] & "*") AND (Jan_08_Orders.CreatedOn Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]))
OR
((Jan_08_Orders.DoDAAC Like "*" & [Forms]![QBF_form]![dodaac] & "*") AND
([Forms]![QBf_Form]![supply_chain] Is Null) AND (Jan_08_Orders.CreatedOn
Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]))
OR ((Jan_08_Orders.[Profit Center] Like "*" &
[Forms]![QBF_form]![supply_chain] & "*")
AND ([Forms]![QBf_Form]![dodaac] Is Null) AND (Jan_08_Orders.CreatedOn
Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]))
OR
(([Forms]![QBf_Form]![supply_chain] Is Null) AND
([Forms]![QBf_Form]![dodaac] Is Null) AND (Jan_08_Orders.CreatedOn
Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]))
ORDER BY Jan_08_Orders.[Profit Center], Jan_08_Orders.[Order Qty] DESC;

or

SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC,
Jan_08_Orders.[Material Group], [material group] & " " & [niin] AS NSN,
Jan_08_Orders.CreatedOn, Jan_08_Orders.[Order Qty],
Jan_08_Orders.[Shipped
Qty], Jan_08_Orders.[Net Value]
FROM Jan_08_Orders
WHERE (Jan_08_Orders.CreatedOn Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate])
AND
(((Jan_08_Orders.[Profit Center] Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND (Jan_08_Orders.DoDAAC Like
"*"
& [Forms]![QBF_form]![dodaac] & "*")) OR
((Jan_08_Orders.DoDAAC Like "*" & [Forms]![QBF_form]![dodaac] & "*") AND
([Forms]![QBf_Form]![supply_chain] Is Null)) OR ((Jan_08_Orders.[Profit
Center] Like "*" & [Forms]![QBF_form]![supply_chain] & "*") AND
([Forms]![QBf_Form]![dodaac] Is Null)) OR
(([Forms]![QBf_Form]![supply_chain] Is Null) AND
([Forms]![QBf_Form]![dodaac] Is Null)))
ORDER BY Jan_08_Orders.[Profit Center], Jan_08_Orders.[Order Qty] DESC;


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"jackie" <jackie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:37EFF856-25B3-49AC-9E89-092421558DF8@xxxxxxxxxxxxxxxx
I'm sorry, but I don't understand how I should change the code. Can you
cut
and paste the way it should look? I tried putting it at the end, but
nothing
changed. Thanks.

"Douglas J. Steele" wrote:

You've only got the part related to date in one section of the Where
clause.

You're going to get all rows where

(((Jan_08_Orders.DoDAAC) Like "*" & [Forms]![QBF_form]![dodaac] & "*")
AND
(([Forms]![QBf_Form]![supply_chain]) Is Null)) OR
(((Jan_08_Orders.[Profit
Center]) Like "*" & [Forms]![QBF_form]![supply_chain] & "*") AND
(([Forms]![QBf_Form]![dodaac]) Is Null)) OR
((([Forms]![QBf_Form]![supply_chain]) Is Null) AND
(([Forms]![QBf_Form]![dodaac]) Is Null))

regardless of when they occur.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"jackie" <jackie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D2AB52AB-F7E9-4AC2-B6E2-B83852B7B4D5@xxxxxxxxxxxxxxxx
To answer the other question, I get results, but the results are not
filtered. For example if I ask for supply Chain "PA" and enter a
date
range,
I get all of new york, but not for the date range I am requesting.
Here's
the
SQL.


SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC,
Jan_08_Orders.[Material Group], [material group] & " " & [niin] AS
NSN,
Jan_08_Orders.CreatedOn, Jan_08_Orders.[Order Qty],
Jan_08_Orders.[Shipped
Qty], Jan_08_Orders.[Net Value]
FROM Jan_08_Orders
WHERE (((Jan_08_Orders.[Profit Center]) Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND ((Jan_08_Orders.DoDAAC)
Like
"*"
& [Forms]![QBF_form]![dodaac] & "*") AND ((Jan_08_Orders.CreatedOn)
Between
[Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]))
OR
(((Jan_08_Orders.DoDAAC) Like "*" & [Forms]![QBF_form]![dodaac] &
"*")
AND
(([Forms]![QBf_Form]![supply_chain]) Is Null)) OR
(((Jan_08_Orders.[Profit
Center]) Like "*" & [Forms]![QBF_form]![supply_chain] & "*") AND
(([Forms]![QBf_Form]![dodaac]) Is Null)) OR
((([Forms]![QBf_Form]![supply_chain]) Is Null) AND
(([Forms]![QBf_Form]![dodaac]) Is Null))
ORDER BY Jan_08_Orders.[Profit Center], Jan_08_Orders.[Order Qty]
DESC;



"fredg" wrote:

On Wed, 12 Mar 2008 07:36:03 -0700, jackie wrote:

the answers that I have found havne't worked.
It's simple. I have query that has a date field. I have a form
with
two
unbound tex boxes, one called txtstartdate and txtenddate.

I also have other text boxes that work. One allows you to enter a
location,
and the other allows you to enter a customer. They are and/or.
You
don't need
one to search by the other, but you can search by both if you
want
to.

The code for the startdate and enddate txt boxes is:
Between Forms!QBF_Form!txtStartDate and Forms!QBF_Form!txtEndDate

It does not filter for a date. What am I doing wrong.


Please post the complete query SQL, and also whether or not the
date
field includes a Time component.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail









.

Relevant Pages