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

Hi again.
I am so frustrated. I keep getting it and losing it. The problem is when I
can't get the date to work with all of the boxes, so when it works with one
box, something else goes awry. Now it's a mess. I've recreated the query a
lot of times. I understood what you said about the and and the or but I still
can't make it work.
And I've been all over this forum looking for help.
I have 4 boxes. dodaac, profit center startdate and enddate.
I have this criteria:

for profit center:
Like "*" & Forms![QBF_form]![dodaac] & "*" Or Forms!QBf_Form![dodaac] Is Null

For Dodaac:
Like "*" & Forms![QBF_form]![supply_chain] & "*" Or
Forms!QBf_Form![supply_chain] Is Null
"supply chain is the name of the box.

For created on date:
Between Forms![QBF_Form]![TxtStartDate] And Forms![QBF_Form]![TxtEndDate]
Or Forms![QBF_Form]![TxtStartDate] And Forms![QBF_Form]![TxtEndDate] Is Null


If I enter the criteria for the dodaac and the profit center I can search by
one or both forh sales, backorders, and orders.

If I enter the criteria for createdOn date, I can search by sales,
backorders and orders, sales with a dodaac, and backorders with a dodaac,
but I can't search orders with a dodaac.
The date portion doesn't work at all.
I tried going into the designer and making sure every combination of OR was
there but all that did was confuse me and corrupt the query.


Here's the SQL

SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC,
Jan_08_Orders.CreatedOn
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] & "*")) OR (((Jan_08_Orders.[Profit Center])
Like "*" & [Forms]![QBF_form]![supply_chain] & "*") AND
((Jan_08_Orders.DoDAAC) Like "*" & [Forms]![QBF_form]![dodaac] & "*") AND
(([Forms]![QBF_Form]![TxtEndDate]) Is Null)) 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));

I'm lost. I've done all I know how. It's very hard for me to read the SQL
with all the parens everywhere and figure out the code, although I did
attempt to this morning. I'd appreciate any help Thanks.






"Douglas J. Steele" wrote:

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










.