What is wrong w/ this query?

[Tom Ellison]

Dear R:

I suggest you go over the logic in your WHERE statement. Remove all
the parentheses, most of which were probably added by Access anyway.
Then put in the parens needed to do this logically according to what
you have designed for it to do. I think you're going to have to move
some of the conditions around just so you can associate them together
the way you want them to work.

The amount of logic you have here is almost certainly best NOT DONE in
the design grid. Complex logic with both AND and OR can be
represented there, but the results are often unnecessarily perverted
and difficult to read.

In order to do this, you probably need to understand logic formally.
It's not a subject that's easy for many.

I expect you'd just like for someone to tell you what's wrong. In
order to do that, we would have to know exactly what you wanted it to
do.

So, I begin to guess at what you want:

WHERE DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL

This is a fragment of the WHERE clause you had, making only one of the
tests. In your code, the test for DateEntered IS NULL was associated
with Date() - DateProc > [Forms]![frmMainReport]![JRProdTurn]. I say
that because the OR was between these two things. Probably it makes
no sense that way.

Also, I added a test for DateRec IS NULL, not knowing if this is
logically correct.

Now, to add another test to the above, you must first put the
statement so far inside parens before beginning to AND it with the
next piece:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL)
AND DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]

Now, can DateProd or DateProc be NULL? And, if they can, do you
include or exclude the row of results created? Assuming you include
it, the test would be:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL)
AND (DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]
OR DateProd IS NULL OR DateProc IS NULL)

Another test that may be important is to see if the user has entered a
value in JREntryTurn and in JRProdTurn. If you allow this, and want
to ignore the associated test in that case, then add this:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL
OR Nz([Forms]![frmMainReport]![JREntryTurn, "") = "" )
AND (DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]
OR DateProd IS NULL OR DateProc IS NULL
OR Nz([Forms]![frmMainReport]![JRProdTurn], "") = "" )

My guess is that your logic will have to follow making the
associations between the components being tested. AND and OR are not
commutative nor distributive (that's the precise terminology in formal
logic, which you have to understand at some level to successfully work
with this.) Rather, they are distributive (like addition and
multiplication) meaning that the order you perform the operations has
definite affect on the outcome.

Anyway, my opinion is that having a lot of extra parens added by
Access is a BIG problem here. It's not readable that way. However,
Access is going to come along and add them anyway. So take this thing
to a text editor and do the work there, saving the test in some file
for later reference, in case you need to work with it again.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have 4 calculated fields in a query, and their parameters are set by
unbound controls on a form. However, the criteria don't seem to be
working - the records returned are changing every time I change the criteria
and refresh, but I see ones that definitely should not be there.

The 4 calculated fields (not necessarily named after the field names, this
is just for easy concept description) are:

- EntryTurn (# of days to enter order after it was received)
- ProcessTurn (# of days to process order after it was entered)
- ProductionTurn (# of days to make order after it was processed)
- TotalTurn (# of days from receipt of order to Production of order)

The SQL view of what I THOUGHT would work is below. Note that I included an
OR in all the calculated fields that uses Is Null and Date() minus the
PREVIOUS corresponding date field to allow for orders that had not been
Entered, Processed, or Produced yet, since turnaround still must show for
those (ie. if an order has not been processed yet, we still need to see that
it's been at least [formcontrolsvalue] days since it was Entered, etc.

SELECT ProductionData.EnteredBy, EmpList.EmpName, ProductionData.DateRec,
ProductionData.DateEntered, ProductionData.OrderPartNum,
ProductionData.PSNum, ProductionData.CustNum, CustomerList.CustName,
ProductionData.Status, OrderStatusList.Status2, ProductionData.DateProc,
ProductionData.SqFoot, ProductionData.AutoProc, ProductionData.ProcBy,
EmpList_1.EmpName, ProductionData.Notes, OrderData.OrderNum,
OrderData.DateProd, OrderData.Vendor, VendorList.VendorName, OrderData.Wall,
OrderData.Floor, OrderData.Bead, OrderData.MachineNum, OrderData.ShiftProd,
OrderData.OperatorNum, EmpList_2.EmpName, OrderData.PartnerNum,
EmpList_3.EmpName, OrderData.Inspector1Num, EmpList_4.EmpName,
OrderData.Inspector2Num, EmpList_5.EmpName, OrderData.[2ndInspector1Num],
OrderData.[2ndInspector2Num], OrderData.TurnaroundDays, OrderData.Notes,
[DateEntered]-[DateRec] AS EntryTurn, [DateProc]-[DateEntered] AS
ProcignTurn, [DateProd]-[DateProc] AS ProdTurn, [DateProd]-[DateEntered] AS
TotalTurn
FROM EmpList AS EmpList_4 RIGHT JOIN (EmpList AS EmpList_3 RIGHT JOIN
(CustomerList RIGHT JOIN (OrderStatusList RIGHT JOIN (VendorList RIGHT JOIN
(EmpList AS EmpList_5 RIGHT JOIN (EmpList AS EmpList_2 RIGHT JOIN (EmpList
AS EmpList_1 RIGHT JOIN (EmpList RIGHT JOIN (OrderData RIGHT JOIN
ProductionData ON OrderData.PSNum = ProductionData.PSNum) ON EmpList.EmpID =
ProductionData.EnteredBy) ON EmpList_1.EmpID = ProductionData.ProcBy) ON
EmpList_2.EmpID = OrderData.OperatorNum) ON EmpList_5.EmpID =
OrderData.Inspector2Num) ON VendorList.VendorID = OrderData.Vendor) ON
OrderStatusList.ID = ProductionData.Status) ON CustomerList.CustID =
ProductionData.CustNum) ON EmpList_3.EmpID = OrderData.PartnerNum) ON
EmpList_4.EmpID = OrderData.Inspector1Num
WHERE ((([DateEntered]-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn])
AND (([DateProc]-[DateEntered])>=[Forms]![frmMainReport]![JRProcTurn]) AND
(([DateProd]-[DateProc])>=[Forms]![frmMainReport]![JRProdTurn]) AND
(([DateProd]-[DateEntered])>=[Forms]![frmMainReport]![JRTotalTurn])) OR
(((OrderData.DateProd) Is Null) AND
((Date()-[DateProc])>=[Forms]![frmMainReport]![JRProdTurn])) OR
(((ProductionData.DateEntered) Is Null) AND
((Date()-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn])) OR
(((ProductionData.DateProc) Is Null) AND
((Date()-[DateEntered])>=[Forms]![frmMainReport]![JRProcTurn]));

However, when I'm changing these values in the form controls and
re-querying, I'm seeing records that certainly shouldn't be shown. For
instance, when I put all of them at 0 except EntryTurn (days between receipt
and entry of order), which I put at 2, let's say, I'm still seeing a bunch
of 0's in the EntryTurn of records returned.

Can someone see what's wrong just be looking at the above? I can email
someone the DB also, if you prefer to see the design grid.

Thanks for any help, I'm struggling pretty hard with this crap

 

[rgrantz]

I have 4 calculated fields in a query, and their parameters are set by
unbound controls on a form. However, the criteria don't seem to be
working - the records returned are changing every time I change the criteria
and refresh, but I see ones that definitely should not be there.

The 4 calculated fields (not necessarily named after the field names, this
is just for easy concept description) are:

- EntryTurn (# of days to enter order after it was received)
- ProcessTurn (# of days to process order after it was entered)
- ProductionTurn (# of days to make order after it was processed)
- TotalTurn (# of days from receipt of order to Production of order)

The SQL view of what I THOUGHT would work is below. Note that I included an
OR in all the calculated fields that uses Is Null and Date() minus the
PREVIOUS corresponding date field to allow for orders that had not been
Entered, Processed, or Produced yet, since turnaround still must show for
those (ie. if an order has not been processed yet, we still need to see that
it's been at least [formcontrolsvalue] days since it was Entered, etc.

SELECT ProductionData.EnteredBy, EmpList.EmpName, ProductionData.DateRec,
ProductionData.DateEntered, ProductionData.OrderPartNum,
ProductionData.PSNum, ProductionData.CustNum, CustomerList.CustName,
ProductionData.Status, OrderStatusList.Status2, ProductionData.DateProc,
ProductionData.SqFoot, ProductionData.AutoProc, ProductionData.ProcBy,
EmpList_1.EmpName, ProductionData.Notes, OrderData.OrderNum,
OrderData.DateProd, OrderData.Vendor, VendorList.VendorName, OrderData.Wall,
OrderData.Floor, OrderData.Bead, OrderData.MachineNum, OrderData.ShiftProd,
OrderData.OperatorNum, EmpList_2.EmpName, OrderData.PartnerNum,
EmpList_3.EmpName, OrderData.Inspector1Num, EmpList_4.EmpName,
OrderData.Inspector2Num, EmpList_5.EmpName, OrderData.[2ndInspector1Num],
OrderData.[2ndInspector2Num], OrderData.TurnaroundDays, OrderData.Notes,
[DateEntered]-[DateRec] AS EntryTurn, [DateProc]-[DateEntered] AS
ProcignTurn, [DateProd]-[DateProc] AS ProdTurn, [DateProd]-[DateEntered] AS
TotalTurn
FROM EmpList AS EmpList_4 RIGHT JOIN (EmpList AS EmpList_3 RIGHT JOIN
(CustomerList RIGHT JOIN (OrderStatusList RIGHT JOIN (VendorList RIGHT JOIN
(EmpList AS EmpList_5 RIGHT JOIN (EmpList AS EmpList_2 RIGHT JOIN (EmpList
AS EmpList_1 RIGHT JOIN (EmpList RIGHT JOIN (OrderData RIGHT JOIN
ProductionData ON OrderData.PSNum = ProductionData.PSNum) ON EmpList.EmpID =
ProductionData.EnteredBy) ON EmpList_1.EmpID = ProductionData.ProcBy) ON
EmpList_2.EmpID = OrderData.OperatorNum) ON EmpList_5.EmpID =
OrderData.Inspector2Num) ON VendorList.VendorID = OrderData.Vendor) ON
OrderStatusList.ID = ProductionData.Status) ON CustomerList.CustID =
ProductionData.CustNum) ON EmpList_3.EmpID = OrderData.PartnerNum) ON
EmpList_4.EmpID = OrderData.Inspector1Num
WHERE ((([DateEntered]-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn])
AND (([DateProc]-[DateEntered])>=[Forms]![frmMainReport]![JRProcTurn]) AND
(([DateProd]-[DateProc])>=[Forms]![frmMainReport]![JRProdTurn]) AND
(([DateProd]-[DateEntered])>=[Forms]![frmMainReport]![JRTotalTurn])) OR
(((OrderData.DateProd) Is Null) AND
((Date()-[DateProc])>=[Forms]![frmMainReport]![JRProdTurn])) OR
(((ProductionData.DateEntered) Is Null) AND
((Date()-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn])) OR
(((ProductionData.DateProc) Is Null) AND
((Date()-[DateEntered])>=[Forms]![frmMainReport]![JRProcTurn]));

However, when I'm changing these values in the form controls and
re-querying, I'm seeing records that certainly shouldn't be shown. For
instance, when I put all of them at 0 except EntryTurn (days between receipt
and entry of order), which I put at 2, let's say, I'm still seeing a bunch
of 0's in the EntryTurn of records returned.

Can someone see what's wrong just be looking at the above? I can email
someone the DB also, if you prefer to see the design grid.

Thanks for any help, I'm struggling pretty hard with this crap

 

[rgrantz]

Tom:

Thanks for taking the time to give some feedback on this. You said:

- - - -
So, I begin to guess at what you want:

WHERE DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL
- - - - - -

Well, I have the null test only because this whole thing is for turnaround,
or number of days it takes for each process. So, if you want to see how
many orders took more than 3 days to enter after we received them, you enter
3 in the EntryTurn unbound control on the form. However, since an order
that's been received and still has not been entered might be over 3 days as
well, I put in the (Or OrderEntered is null, AND Date()-[DateRec]

[Forms]![Blahblah]![EntryTurn]). Thus, orders that have been received and

still have not been entered, and it's been at least 3 days since they were
received (Today's Date minus the date it was received), will show up as
well.

This is to applied to each phase of the process: Turnaround between order
entry and order processing, turnaround between processing and production,
and finally, turnaround of the whole shebang (receipt of order to production
of it).

The unbound controls on the form are for the user to continuously filter
these results (I already did away with the possibility of the unbound
control being null to avoid complications in the query; I have made their
AfterUpdate subs replace nulls with 0's.). However, the test for the nulls
in the table data is not just for any null, but only those nulls where
today's date (date()) minus the PREVIOUS process' date is more than the
number put in the control.

So, if they enter 5 in the unbound control ProcTurn and 5 in TotalTurn
(TotalTurn, obviously, has to be at LEAST all the other criteria added up,
or it wouldn't make sense), and leave 0 in all the others, he should see all
orders where the turnaround between Entry and Processing is at least 5 days,
OR they were entered at least 5 days ago and still don't have a date entered
in DateProcessed in the table (and turnaround for all other processes is 0).
If they then put 3 in the ProdTurn control (and thus need to put 8 in
TotalTurn), the new records should show all the orders that took at least 5
days to process (or it's been 5 days since it was received and there's still
no date in DateProc), and of those all the orders that took 3 days to
produce (or it's been at least 3 days since it was processed and there's
still no date in DateProd).

Etc. etc.

I realize that this is a continuously drilling-down criteria set in that
when you are only looking at orders that took 5 days to enter, you may not
be seeing the ones that took 3 days to Process, since they have to meet ALL
the turnaround criteria. But that's cool, that's what they want to do.

It's very similar to the Excel Autofilter function; when you choose a
criteria in one column, you can only choose crtieria from another column
that exists in the shown records that comply with the first column criteria.
This is exactly what we're trying to do, only in an Access form rather than
using Excel's Autofilter.

What seems to be happening is that the "OR"s for all parameters are showing
up and messing with the results. I want the "OR" check for the (is null and
Date()-[PreviousPhaseDate]>=[UnboundControlValue) to only apply to THAT
field in the recordset. For instance, when I have 5 in EntryTurn, 3 in
ProcTurn, 3 in ProdTurn, and 12 in TotalTurn, I only want to see order
that:

Took at least 5 days to enter (or it's been 5 days since they've been
received and there's still no date in [DateEntered]
AND
Took at least 3 days to Process (or it's been 3 days since they've been
entered and there's still no date in [DateProc]
AND
Took at least 3 days to Produce (or it's been 3 days since they've been
processed and there's still no date in [DateProd]
AND
Took at least 12 days to go from Receipt to Production (or it's been 12 days
since they've been received and there's still no date in [DateProc]

I THINK the problem I'm having is figuring out how to enter these OR's in
the design grid (or word the SQL right) to make it so the OR tests are only
applied to THAT field in the recordset and with all the other criteria.
Like, it seems to me that when I put 5 in EntryTurn and 3 in ProcTurn, the
records include orders that did NOT take 5 days to enter, just because they
happen to have null values in ProcTurn that are at least 3 days since they
were entered.

So basically, again, all I'm trying to do is use Excel's Autofilter
capability, only using unbound controls to enter criteria rather than
Excel's little drop-down arrows in the Autofilter.

Is this easier than I'm trying to make it seem, or it much harder than I
think it is?

Thanks again, your time is very much appreciated.

Dear R:

I suggest you go over the logic in your WHERE statement. Remove all
the parentheses, most of which were probably added by Access anyway.
Then put in the parens needed to do this logically according to what
you have designed for it to do. I think you're going to have to move
some of the conditions around just so you can associate them together
the way you want them to work.

The amount of logic you have here is almost certainly best NOT DONE in
the design grid. Complex logic with both AND and OR can be
represented there, but the results are often unnecessarily perverted
and difficult to read.

In order to do this, you probably need to understand logic formally.
It's not a subject that's easy for many.

I expect you'd just like for someone to tell you what's wrong. In
order to do that, we would have to know exactly what you wanted it to
do.

So, I begin to guess at what you want:

WHERE DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL

This is a fragment of the WHERE clause you had, making only one of the
tests. In your code, the test for DateEntered IS NULL was associated
with Date() - DateProc > [Forms]![frmMainReport]![JRProdTurn]. I say
that because the OR was between these two things. Probably it makes
no sense that way.

Also, I added a test for DateRec IS NULL, not knowing if this is
logically correct.

Now, to add another test to the above, you must first put the
statement so far inside parens before beginning to AND it with the
next piece:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL)
AND DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]

Now, can DateProd or DateProc be NULL? And, if they can, do you
include or exclude the row of results created? Assuming you include
it, the test would be:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL)
AND (DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]
OR DateProd IS NULL OR DateProc IS NULL)

Another test that may be important is to see if the user has entered a
value in JREntryTurn and in JRProdTurn. If you allow this, and want
to ignore the associated test in that case, then add this:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL
OR Nz([Forms]![frmMainReport]![JREntryTurn, "") = "" )
AND (DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]
OR DateProd IS NULL OR DateProc IS NULL
OR Nz([Forms]![frmMainReport]![JRProdTurn], "") = "" )

My guess is that your logic will have to follow making the
associations between the components being tested. AND and OR are not
commutative nor distributive (that's the precise terminology in formal
logic, which you have to understand at some level to successfully work
with this.) Rather, they are distributive (like addition and
multiplication) meaning that the order you perform the operations has
definite affect on the outcome.

Anyway, my opinion is that having a lot of extra parens added by
Access is a BIG problem here. It's not readable that way. However,
Access is going to come along and add them anyway. So take this thing
to a text editor and do the work there, saving the test in some file
for later reference, in case you need to work with it again.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have 4 calculated fields in a query, and their parameters are set by
unbound controls on a form. However, the criteria don't seem to be
working - the records returned are changing every time I change the criteria
and refresh, but I see ones that definitely should not be there.

The 4 calculated fields (not necessarily named after the field names, this
is just for easy concept description) are:

- EntryTurn (# of days to enter order after it was received)
- ProcessTurn (# of days to process order after it was entered)
- ProductionTurn (# of days to make order after it was processed)
- TotalTurn (# of days from receipt of order to Production of order)

The SQL view of what I THOUGHT would work is below. Note that I included an
OR in all the calculated fields that uses Is Null and Date() minus the
PREVIOUS corresponding date field to allow for orders that had not been
Entered, Processed, or Produced yet, since turnaround still must show for
those (ie. if an order has not been processed yet, we still need to see that
it's been at least [formcontrolsvalue] days since it was Entered, etc.

SELECT ProductionData.EnteredBy, EmpList.EmpName, ProductionData.DateRec,
ProductionData.DateEntered, ProductionData.OrderPartNum,
ProductionData.PSNum, ProductionData.CustNum, CustomerList.CustName,
ProductionData.Status, OrderStatusList.Status2, ProductionData.DateProc,
ProductionData.SqFoot, ProductionData.AutoProc, ProductionData.ProcBy,
EmpList_1.EmpName, ProductionData.Notes, OrderData.OrderNum,
OrderData.DateProd, OrderData.Vendor, VendorList.VendorName, OrderData.Wall,
OrderData.Floor, OrderData.Bead, OrderData.MachineNum, OrderData.ShiftProd,
OrderData.OperatorNum, EmpList_2.EmpName, OrderData.PartnerNum,
EmpList_3.EmpName, OrderData.Inspector1Num, EmpList_4.EmpName,
OrderData.Inspector2Num, EmpList_5.EmpName, OrderData.[2ndInspector1Num],
OrderData.[2ndInspector2Num], OrderData.TurnaroundDays, OrderData.Notes,
[DateEntered]-[DateRec] AS EntryTurn, [DateProc]-[DateEntered] AS
ProcignTurn, [DateProd]-[DateProc] AS ProdTurn, [DateProd]-[DateEntered] AS
TotalTurn
FROM EmpList AS EmpList_4 RIGHT JOIN (EmpList AS EmpList_3 RIGHT JOIN
(CustomerList RIGHT JOIN (OrderStatusList RIGHT JOIN (VendorList RIGHT JOIN
(EmpList AS EmpList_5 RIGHT JOIN (EmpList AS EmpList_2 RIGHT JOIN (EmpList
AS EmpList_1 RIGHT JOIN (EmpList RIGHT JOIN (OrderData RIGHT JOIN
ProductionData ON OrderData.PSNum = ProductionData.PSNum) ON EmpList.EmpID =
ProductionData.EnteredBy) ON EmpList_1.EmpID = ProductionData.ProcBy) ON
EmpList_2.EmpID = OrderData.OperatorNum) ON EmpList_5.EmpID =
OrderData.Inspector2Num) ON VendorList.VendorID = OrderData.Vendor) ON
OrderStatusList.ID = ProductionData.Status) ON CustomerList.CustID =
ProductionData.CustNum) ON EmpList_3.EmpID = OrderData.PartnerNum) ON
EmpList_4.EmpID = OrderData.Inspector1Num
WHERE ((([DateEntered]-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn])
AND (([DateProc]-[DateEntered])>=[Forms]![frmMainReport]![JRProcTurn]) AND
(([DateProd]-[DateProc])>=[Forms]![frmMainReport]![JRProdTurn]) AND
(([DateProd]-[DateEntered])>=[Forms]![frmMainReport]![JRTotalTurn])) OR
(((OrderData.DateProd) Is Null) AND
((Date()-[DateProc])>=[Forms]![frmMainReport]![JRProdTurn])) OR
(((ProductionData.DateEntered) Is Null) AND
((Date()-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn])) OR
(((ProductionData.DateProc) Is Null) AND
((Date()-[DateEntered])>=[Forms]![frmMainReport]![JRProcTurn]));

However, when I'm changing these values in the form controls and
re-querying, I'm seeing records that certainly shouldn't be shown. For
instance, when I put all of them at 0 except EntryTurn (days between receipt
and entry of order), which I put at 2, let's say, I'm still seeing a bunch
of 0's in the EntryTurn of records returned.

Can someone see what's wrong just be looking at the above? I can email
someone the DB also, if you prefer to see the design grid.

Thanks for any help, I'm struggling pretty hard with this crap

 

[rgrantz]

I just thought I'd add that Access's right click-->apply filter function
does exactly what I want as well, in that it continually applies filters to
EXISTING records. I just need to add the ability to include the "is null
and Date()-[DateofWhateverPreviousProcessWas]>criteria", and have it be
unbound controlss doing the criteria, rather than the user needing to use
rightclick-->filter.

I just thought I'd add that, and it might simplify what I'm looking for. I
take pages and pages to explain it, but really it's pretty much Excel's
Autofilter, or Access's Apply Filter, with the addition of checking each
particualr filter for the whole "is null and
Date()-[DateofWhateverPreviousProcessWas]>criteria" thing.


Sorry about all the trouble, I imagine I'll feel like a bonehead once I
actually get to the solution for all this.

Tom:

Thanks for taking the time to give some feedback on this. You said:

- - - -
So, I begin to guess at what you want:

WHERE DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL
- - - - - -

Well, I have the null test only because this whole thing is for turnaround,
or number of days it takes for each process. So, if you want to see how
many orders took more than 3 days to enter after we received them, you enter
3 in the EntryTurn unbound control on the form. However, since an order
that's been received and still has not been entered might be over 3 days as
well, I put in the (Or OrderEntered is null, AND Date()-[DateRec]

[Forms]![Blahblah]![EntryTurn]). Thus, orders that have been received

and
still have not been entered, and it's been at least 3 days since they were
received (Today's Date minus the date it was received), will show up as
well.

This is to applied to each phase of the process: Turnaround between order
entry and order processing, turnaround between processing and production,
and finally, turnaround of the whole shebang (receipt of order to production
of it).

The unbound controls on the form are for the user to continuously filter
these results (I already did away with the possibility of the unbound
control being null to avoid complications in the query; I have made their
AfterUpdate subs replace nulls with 0's.). However, the test for the nulls
in the table data is not just for any null, but only those nulls where
today's date (date()) minus the PREVIOUS process' date is more than the
number put in the control.

So, if they enter 5 in the unbound control ProcTurn and 5 in TotalTurn
(TotalTurn, obviously, has to be at LEAST all the other criteria added up,
or it wouldn't make sense), and leave 0 in all the others, he should see all
orders where the turnaround between Entry and Processing is at least 5 days,
OR they were entered at least 5 days ago and still don't have a date entered
in DateProcessed in the table (and turnaround for all other processes is 0).
If they then put 3 in the ProdTurn control (and thus need to put 8 in
TotalTurn), the new records should show all the orders that took at least 5
days to process (or it's been 5 days since it was received and there's still
no date in DateProc), and of those all the orders that took 3 days to
produce (or it's been at least 3 days since it was processed and there's
still no date in DateProd).

Etc. etc.

I realize that this is a continuously drilling-down criteria set in that
when you are only looking at orders that took 5 days to enter, you may not
be seeing the ones that took 3 days to Process, since they have to meet ALL
the turnaround criteria. But that's cool, that's what they want to do.

It's very similar to the Excel Autofilter function; when you choose a
criteria in one column, you can only choose crtieria from another column
that exists in the shown records that comply with the first column criteria.
This is exactly what we're trying to do, only in an Access form rather than
using Excel's Autofilter.

What seems to be happening is that the "OR"s for all parameters are showing
up and messing with the results. I want the "OR" check for the (is null and
Date()-[PreviousPhaseDate]>=[UnboundControlValue) to only apply to THAT
field in the recordset. For instance, when I have 5 in EntryTurn, 3 in
ProcTurn, 3 in ProdTurn, and 12 in TotalTurn, I only want to see order
that:

Took at least 5 days to enter (or it's been 5 days since they've been
received and there's still no date in [DateEntered]
AND
Took at least 3 days to Process (or it's been 3 days since they've been
entered and there's still no date in [DateProc]
AND
Took at least 3 days to Produce (or it's been 3 days since they've been
processed and there's still no date in [DateProd]
AND
Took at least 12 days to go from Receipt to Production (or it's been 12 days
since they've been received and there's still no date in [DateProc]

I THINK the problem I'm having is figuring out how to enter these OR's in
the design grid (or word the SQL right) to make it so the OR tests are only
applied to THAT field in the recordset and with all the other criteria.
Like, it seems to me that when I put 5 in EntryTurn and 3 in ProcTurn, the
records include orders that did NOT take 5 days to enter, just because they
happen to have null values in ProcTurn that are at least 3 days since they
were entered.

So basically, again, all I'm trying to do is use Excel's Autofilter
capability, only using unbound controls to enter criteria rather than
Excel's little drop-down arrows in the Autofilter.

Is this easier than I'm trying to make it seem, or it much harder than I
think it is?

Thanks again, your time is very much appreciated.

Dear R:

I suggest you go over the logic in your WHERE statement. Remove all
the parentheses, most of which were probably added by Access anyway.
Then put in the parens needed to do this logically according to what
you have designed for it to do. I think you're going to have to move
some of the conditions around just so you can associate them together
the way you want them to work.

The amount of logic you have here is almost certainly best NOT DONE in
the design grid. Complex logic with both AND and OR can be
represented there, but the results are often unnecessarily perverted
and difficult to read.

In order to do this, you probably need to understand logic formally.
It's not a subject that's easy for many.

I expect you'd just like for someone to tell you what's wrong. In
order to do that, we would have to know exactly what you wanted it to
do.

So, I begin to guess at what you want:

WHERE DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL

This is a fragment of the WHERE clause you had, making only one of the
tests. In your code, the test for DateEntered IS NULL was associated
with Date() - DateProc > [Forms]![frmMainReport]![JRProdTurn]. I say
that because the OR was between these two things. Probably it makes
no sense that way.

Also, I added a test for DateRec IS NULL, not knowing if this is
logically correct.

Now, to add another test to the above, you must first put the
statement so far inside parens before beginning to AND it with the
next piece:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL)
AND DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]

Now, can DateProd or DateProc be NULL? And, if they can, do you
include or exclude the row of results created? Assuming you include
it, the test would be:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL)
AND (DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]
OR DateProd IS NULL OR DateProc IS NULL)

Another test that may be important is to see if the user has entered a
value in JREntryTurn and in JRProdTurn. If you allow this, and want
to ignore the associated test in that case, then add this:

WHERE (DateEntered - DateRec >= [Forms]![frmMainReport]![JREntryTurn]
OR DateEntered IS NULL OR DateRec IS NULL
OR Nz([Forms]![frmMainReport]![JREntryTurn, "") = "" )
AND (DateProd - DateProc >= [Forms]![frmMainReport]![JRProdTurn]
OR DateProd IS NULL OR DateProc IS NULL
OR Nz([Forms]![frmMainReport]![JRProdTurn], "") = "" )

My guess is that your logic will have to follow making the
associations between the components being tested. AND and OR are not
commutative nor distributive (that's the precise terminology in formal
logic, which you have to understand at some level to successfully work
with this.) Rather, they are distributive (like addition and
multiplication) meaning that the order you perform the operations has
definite affect on the outcome.

Anyway, my opinion is that having a lot of extra parens added by
Access is a BIG problem here. It's not readable that way. However,
Access is going to come along and add them anyway. So take this thing
to a text editor and do the work there, saving the test in some file
for later reference, in case you need to work with it again.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have 4 calculated fields in a query, and their parameters are set by
unbound controls on a form. However, the criteria don't seem to be
working - the records returned are changing every time I change the criteria
and refresh, but I see ones that definitely should not be there.

The 4 calculated fields (not necessarily named after the field names, this
is just for easy concept description) are:

- EntryTurn (# of days to enter order after it was received)
- ProcessTurn (# of days to process order after it was entered)
- ProductionTurn (# of days to make order after it was processed)
- TotalTurn (# of days from receipt of order to Production of order)

The SQL view of what I THOUGHT would work is below. Note that I

included

an

OR in all the calculated fields that uses Is Null and Date() minus the
PREVIOUS corresponding date field to allow for orders that had not been
Entered, Processed, or Produced yet, since turnaround still must show for
those (ie. if an order has not been processed yet, we still need to see that
it's been at least [formcontrolsvalue] days since it was Entered, etc.

SELECT ProductionData.EnteredBy, EmpList.EmpName, ProductionData.DateRec,
ProductionData.DateEntered, ProductionData.OrderPartNum,
ProductionData.PSNum, ProductionData.CustNum, CustomerList.CustName,
ProductionData.Status, OrderStatusList.Status2, ProductionData.DateProc,
ProductionData.SqFoot, ProductionData.AutoProc, ProductionData.ProcBy,
EmpList_1.EmpName, ProductionData.Notes, OrderData.OrderNum,
OrderData.DateProd, OrderData.Vendor, VendorList.VendorName, OrderData.Wall,
OrderData.Floor, OrderData.Bead, OrderData.MachineNum, OrderData.ShiftProd,
OrderData.OperatorNum, EmpList_2.EmpName, OrderData.PartnerNum,
EmpList_3.EmpName, OrderData.Inspector1Num, EmpList_4.EmpName,
OrderData.Inspector2Num, EmpList_5.EmpName, OrderData.[2ndInspector1Num],
OrderData.[2ndInspector2Num], OrderData.TurnaroundDays, OrderData.Notes,
[DateEntered]-[DateRec] AS EntryTurn, [DateProc]-[DateEntered] AS
ProcignTurn, [DateProd]-[DateProc] AS ProdTurn,

[DateProd]-[DateEntered]

AS

TotalTurn
FROM EmpList AS EmpList_4 RIGHT JOIN (EmpList AS EmpList_3 RIGHT JOIN
(CustomerList RIGHT JOIN (OrderStatusList RIGHT JOIN (VendorList RIGHT JOIN
(EmpList AS EmpList_5 RIGHT JOIN (EmpList AS EmpList_2 RIGHT JOIN (EmpList
AS EmpList_1 RIGHT JOIN (EmpList RIGHT JOIN (OrderData RIGHT JOIN
ProductionData ON OrderData.PSNum = ProductionData.PSNum) ON EmpList.EmpID =
ProductionData.EnteredBy) ON EmpList_1.EmpID = ProductionData.ProcBy) ON
EmpList_2.EmpID = OrderData.OperatorNum) ON EmpList_5.EmpID =
OrderData.Inspector2Num) ON VendorList.VendorID = OrderData.Vendor) ON
OrderStatusList.ID = ProductionData.Status) ON CustomerList.CustID =
ProductionData.CustNum) ON EmpList_3.EmpID = OrderData.PartnerNum) ON
EmpList_4.EmpID = OrderData.Inspector1Num
WHERE ((([DateEntered]-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn])
AND (([DateProc]-[DateEntered])>=[Forms]![frmMainReport]![JRProcTurn]) AND
(([DateProd]-[DateProc])>=[Forms]![frmMainReport]![JRProdTurn]) AND
(([DateProd]-[DateEntered])>=[Forms]![frmMainReport]![JRTotalTurn])) OR
(((OrderData.DateProd) Is Null) AND
((Date()-[DateProc])>=[Forms]![frmMainReport]![JRProdTurn])) OR
(((ProductionData.DateEntered) Is Null) AND
((Date()-[DateRec])>=[Forms]![frmMainReport]![JREntryTurn])) OR
(((ProductionData.DateProc) Is Null) AND
((Date()-[DateEntered])>=[Forms]![frmMainReport]![JRProcTurn]));

However, when I'm changing these values in the form controls and
re-querying, I'm seeing records that certainly shouldn't be shown. For
instance, when I put all of them at 0 except EntryTurn (days between receipt
and entry of order), which I put at 2, let's say, I'm still seeing a bunch
of 0's in the EntryTurn of records returned.

Can someone see what's wrong just be looking at the above? I can email
someone the DB also, if you prefer to see the design grid.

Thanks for any help, I'm struggling pretty hard with this crap