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

Also, I tried to fix the code. What's wrong with this?
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 (((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]) Is
Null)) OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![dodaac]) Is Null))
OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![supply_chain]) Is
Null)) OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate])Is Null 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]) Is Null))
ORDER BY Jan_08_Orders.[Profit Center], Jan_08_Orders.[Order Qty] DESC;



"jackie" wrote:

Hi Douglas,
Thanks for your reply. I'm kind of in a pickle right now because they're
asking for this database.
The forst code you wrote didn't work, but the second code did. However, what
it gave me was all of the orders in the database between the two dates. I
need the filter to also include the profit center. For example, they may
enter the profit center 'medical', and the dates 1/2/2008 thru 1/5/2008. They
would get all of the orders in medical for those dates. They should also be
able to enter a dodaac.
Currently the user can enter a profit center and a dodaac, or just a dodaac,
or just a profit center. I want to add date to the mix so that the user can
search for a profit center and a dodaac and if they choose, a specific date
range.

Thank you for your help. When I get this completed, I would like to
understand the code better.

Jackie

"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" wrote in message
...
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" wrote in message
news 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

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" wrote in message
...
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" wrote in message
news 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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
news 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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
news 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

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
http://I.Am/DougSteele
(no e-mails, please!)


"jackie" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
news 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

Thanks for your response.
Will this code allow the user to search by profit center or dodaac or
startdate/enddate, or any combination of the three?

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))

AND (Jan_08_Orders.CreatedOn Between Nz([Forms]![QBF_Form]![TxtStartDate],
#1/1/100#) And Nz([Forms]![QBF_Form]![TxtEndDate], #12/31/9999#))


Thank you.


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)


"jackie" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
news 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

Yes. (Of course, it would have been faster for you to just try it than to
wait for me to get back on-line and reply...)

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


"jackie" wrote in message
...
Thanks for your response.
Will this code allow the user to search by profit center or dodaac or
startdate/enddate, or any combination of the three?

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))

AND (Jan_08_Orders.CreatedOn Between Nz([Forms]![QBF_Form]![TxtStartDate],
#1/1/100#) And Nz([Forms]![QBF_Form]![TxtEndDate], #12/31/9999#))


Thank you.


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)


"jackie" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
news 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