It doesn't help that Access insists on inserting all sorts of extraneous
parentheses.
Let's look at that SQL. You've got 5 separate subclauses in the criteria:
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));
Presumably the second one is an error: I think you forgot to remove the
reference to [Forms]![QBF_Form]![TxtEndDate] since the first looks at
[Forms]![QBF_form]![supply_chain] and [Forms]![QBF_form]![dodaac]
regardless
of what [Forms]![QBF_Form]![TxtEndDate] contains.
You never did answer whether you can have Null values for [Profit Center]
or
DoDAAC. If you can't, then the following SQL should be all you need:
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] & "*"))
As I said before, if, for example, [Forms]![QBF_form]![dodaac] is Null,
the
second part of the Where clause reduces to
((Jan_08_Orders.DoDAAC) Like "**"))
which will return all values.
If those fields can be Null, and you want the Null values returned when
the
appropriate control on the form is Null, try:
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] & "*") OR
([Forms]![QBF_form]![supply_chain] IS NULL))
AND ((Jan_08_Orders.DoDAAC Like "*" & [Forms]![QBF_form]![dodaac] & "*")
OR
([Forms]![QBF_form]![dodaac] IS NULL))
To add the Dates back in, add the following at the end of whichever of
the
above you use:
AND (Jan_08_Orders.CreatedOn Between
Nz([Forms]![QBF_Form]![TxtStartDate],
#1/1/100#) And Nz([Forms]![QBF_Form]![TxtEndDate], #12/31/9999#))
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
jackie said:
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.
:
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
(no e-mails, please!)
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
:
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
(no private e-mails, please)
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.
:
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
(no e-mails, please!)
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
