My query for start and end dates worked for a minute...then stoppe

[jackie]

Can someone tell me what's wrong with this code. First it works, then it
stops working.
I have 4 unbound boxes on a form. Dodaac, Supply Chain, Start Date and End
Date. It's the date I'm having a problem with.
I can enter a dodaac and supply chain, or a dodaac or a supply chain.
However, when I enter a start date and end date, I get records but not for
the date range i enter. my critieria looks like this:
For the Supply Chain
Like "*" & Forms![QBF_form]![supply_chain] & "*" Or
Forms!QBf_Form![supply_chain] Is Null

For the Dodaac
Like "*" & Forms![QBF_form]![dodaac] & "*" Or Forms!QBf_Form![dodaac] Is Null

For the 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

FYI I've tried it like this:
Between [Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]

Here's the SQL:
SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC,
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.[Profit Center]) Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND ((Jan_08_Orders.DoDAAC) Like "*"
& [Forms]![QBF_form]![dodaac] & "*") AND (([Forms]![QBF_Form]![TxtStartDate]
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 went in and recreated the query for the hundredth time and it worked for a
while, then it stopped working. Any help would be appreciated.

 

[Douglas J. Steele]

You may not need those "Or Forms!QBf_Form![supply_chain] Is Null" bits.

What precedes that

Like "*" & Forms![QBF_form]![supply_chain] & "*"

will evaluate to

Like "**"

if the control is Null, which means that you'll get all non-Null records. It
will not, however, return any records in the table for which the field being
checked in Null. If you don't care about Null records in your table, leave
off that second part.

For the dates, what I usually do is use the Nz function to return a very
early date (the earliest date possible in Access, which is 1 Jan, 100) if
the Begin date is Null, or a very late date (12 Dec, 9999) if the End date
is Null:

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

In other words, you can certainly simplify that query a whole lot. (Sorry I
didn't bring that up yesterday)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can someone tell me what's wrong with this code. First it works, then it
stops working.
I have 4 unbound boxes on a form. Dodaac, Supply Chain, Start Date and End
Date. It's the date I'm having a problem with.
I can enter a dodaac and supply chain, or a dodaac or a supply chain.
However, when I enter a start date and end date, I get records but not for
the date range i enter. my critieria looks like this:
For the Supply Chain
Like "*" & Forms![QBF_form]![supply_chain] & "*" Or
Forms!QBf_Form![supply_chain] Is Null

For the Dodaac
Like "*" & Forms![QBF_form]![dodaac] & "*" Or Forms!QBf_Form![dodaac] Is
Null

For the 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

FYI I've tried it like this:
Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]

Here's the SQL:
SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC,
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.[Profit Center]) Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND ((Jan_08_Orders.DoDAAC) Like
"*"
& [Forms]![QBF_form]![dodaac] & "*") AND
(([Forms]![QBF_Form]![TxtStartDate]
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 went in and recreated the query for the hundredth time and it worked for
a
while, then it stopped working. Any help would be appreciated.