Dates in Query

  • Thread starter Thread starter depawl
  • Start date Start date
D

depawl

I am trying to design a query that will return "overdue" report dates
from 2 different fields. The code I'm using goes like this:
SELECT IIf([REPORT1]<Now(),[REPORT1],Null) AS Report1OverDue,
IIf([REPORT2]<Now(),[REPORT2],Null) AS Report2OverDue
FROM MyTable
WHERE (IsNull([REPORT1RECEIVED]) Or IsNull(REPORT2RECEIVED]));

What I wish this code did is to return any REPORT1 overdue dates (unless
REPORT1RECEIVED is true) or any REPORT2 overdue dates (unless
REPORT2RECEIVED is true).
But what is does is return all REPORT1 dates (overdue or not) for
REPORT2 overdue dates.
I don't seem to be able to limit it to just overdue dates for both reports.
Thanks.
 
I am trying to design a query that will return "overdue" report dates
from 2 different fields. The code I'm using goes like this:
SELECT IIf([REPORT1]<Now(),[REPORT1],Null) AS Report1OverDue,
IIf([REPORT2]<Now(),[REPORT2],Null) AS Report2OverDue
FROM MyTable
WHERE (IsNull([REPORT1RECEIVED]) Or IsNull(REPORT2RECEIVED]));

What I wish this code did is to return any REPORT1 overdue dates (unless
REPORT1RECEIVED is true) or any REPORT2 overdue dates (unless
REPORT2RECEIVED is true).
But what is does is return all REPORT1 dates (overdue or not) for
REPORT2 overdue dates.
I don't seem to be able to limit it to just overdue dates for both reports.
Thanks.
Click to expand...

Part of the problem may be because you have a one to many (well, one
to two) relationship embedded within each record. Will you ever have a
Report3, and what will you do with it?

That said, parenthesis nesting and multiple criteria will help here: a
criterion of

SELECT IIF([REPORT1] < Date() AND IsNull([Report1Recieved], [Report1],
Null) AS Report1Overdue,
IIF([REPORT2] < Date() AND IsNull([Report2Recieved], [Report2], Null)
AS Report1Overdue) FROM MyTable WHERE
(IsNull([REPORT1RECEIVED] AND [Report1] < Date())
Or
(IsNull(REPORT2RECEIVED]) AND [Report2] < Date());
 
That code gives an error:
Wrong number of arguments used with function in query expression
'IIf([Report1] < Date() And IsNull([Report1Received], [Report1], Null))


John said:
I am trying to design a query that will return "overdue" report dates


from 2 different fields. The code I'm using goes like this:

SELECT IIf([REPORT1]<Now(),[REPORT1],Null) AS Report1OverDue,
IIf([REPORT2]<Now(),[REPORT2],Null) AS Report2OverDue


FROM MyTable

WHERE (IsNull([REPORT1RECEIVED]) Or IsNull(REPORT2RECEIVED]));

What I wish this code did is to return any REPORT1 overdue dates (unless
REPORT1RECEIVED is true) or any REPORT2 overdue dates (unless
REPORT2RECEIVED is true).
But what is does is return all REPORT1 dates (overdue or not) for
REPORT2 overdue dates.
I don't seem to be able to limit it to just overdue dates for both reports.
Thanks.
Click to expand...

Part of the problem may be because you have a one to many (well, one
to two) relationship embedded within each record. Will you ever have a
Report3, and what will you do with it?

That said, parenthesis nesting and multiple criteria will help here: a
criterion of

SELECT IIF([REPORT1] < Date() AND IsNull([Report1Recieved], [Report1],
Null) AS Report1Overdue,
IIF([REPORT2] < Date() AND IsNull([Report2Recieved], [Report2], Null)
AS Report1Overdue) FROM MyTable WHERE
(IsNull([REPORT1RECEIVED] AND [Report1] < Date())
Or
(IsNull(REPORT2RECEIVED]) AND [Report2] < Date());
Click to expand...
 
That code gives an error:
Wrong number of arguments used with function in query expression
'IIf([Report1] < Date() And IsNull([Report1Received], [Report1], Null))
Click to expand...

Sorry. Parenthesis nesting error (which a little study could have
found):

IIf([Report1] < Date() And IsNull([Report1Received]), [Report1], Null)
 
Thanks John, that did it. Been struggling with this one for quite a while.

John said:
That code gives an error:
Wrong number of arguments used with function in query expression
'IIf([Report1] < Date() And IsNull([Report1Received], [Report1], Null))
Click to expand...

Sorry. Parenthesis nesting error (which a little study could have
found):

IIf([Report1] < Date() And IsNull([Report1Received]), [Report1], Null)
Click to expand...
 
Back
Top