Logic problem

  • Thread starter Thread starter Claire
  • Start date Start date
C

Claire

Hi everybody, I am using Access 2000.

I have a select query that is testing to see if an
inspection report is overdue. After this query runs I use
a crosstab query to count how many reports are overdue for
various areas.

The query criteria is simply: [dtmConditionSentDate] >
[dtmConditionDueDate]

However, now I need to also include the fact that if there
is a value in [dtmConditionDelayDate], then the original
[dtmConditionDueDate] is no longer part of the equation.

For example:

Sent Date Due Date Delay Date Overdue?
Mar 15 2004 Mar 10 2004 Yes - as
of March 11
Mar 15 2004 Mar 10 2004 Apr 1 2004 Not until
April 2

I can think of the logic for this in my head, but I am
having no luck getting this to work in Access. Any help
would be greatly appreciated.

Thanks,
Claire
 
Assuming that dtmConditionDelayDate is Null if it is not
being used, try

nz(dtmConditionDelayDate,dtmConditionDueDate)

This will return a the DelayDate if it exists - otherwise
it will return the original due date.
 
Hi Pat, thanks for your response.

How and where do I use the nz statement that you provided?

I thought that I would have to use an IF statement in here
somewhere because if there is a dtmConditionDelayDate
value that is in the future, then the report is not
overdue and should not be counted.

Am I way out of whack here?

Thanks
Claire
-----Original Message-----
Assuming that dtmConditionDelayDate is Null if it is not
being used, try

nz(dtmConditionDelayDate,dtmConditionDueDate)

This will return a the DelayDate if it exists - otherwise
it will return the original due date.

-----Original Message-----
Hi everybody, I am using Access 2000.

I have a select query that is testing to see if an
inspection report is overdue. After this query runs I use
a crosstab query to count how many reports are overdue for
various areas.

The query criteria is simply: [dtmConditionSentDate] >
[dtmConditionDueDate]

However, now I need to also include the fact that if there
is a value in [dtmConditionDelayDate], then the original
[dtmConditionDueDate] is no longer part of the equation.

For example:

Sent Date Due Date Delay Date Overdue?
Mar 15 2004 Mar 10 2004 Yes - as
of March 11
Mar 15 2004 Mar 10 2004 Apr 1 2004 Not until
April 2

I can think of the logic for this in my head, but I am
having no luck getting this to work in Access. Any help
would be greatly appreciated.

Thanks,
Claire

.
Click to expand...
.
Click to expand...
 
nz is a function equivalent to:

if dtmConditionDelayDate is not null then use it
else use dtmConditionDueDate

if you create a new field in your query:

UseDate: nz(dtmConditionDelayDate,dtmConditionDueDate)

Then you can use 'UseDate' to calculate if it is overdue:

OverDue: iff(UseDate > date(),True,False)

or putting in all in one step...

OverDue:
iff(nz(dtmConditionDelayDate,dtmConditionDueDate)
date(),True,False)
Click to expand...

OverDue will then be equal to True when you want it to be
(calculated from today's date) if I understand
correctly. Since you say you're wanting to count, you
might use 1 and 0 instead of True and False and then sum
the result however you need to group it to get the total
number overdue.

-----Original Message-----
Hi Pat, thanks for your response.

How and where do I use the nz statement that you provided?

I thought that I would have to use an IF statement in here
somewhere because if there is a dtmConditionDelayDate
value that is in the future, then the report is not
overdue and should not be counted.

Am I way out of whack here?

Thanks
Claire
-----Original Message-----
Assuming that dtmConditionDelayDate is Null if it is not
being used, try

nz(dtmConditionDelayDate,dtmConditionDueDate)

This will return a the DelayDate if it exists - otherwise
it will return the original due date.

-----Original Message-----
Hi everybody, I am using Access 2000.

I have a select query that is testing to see if an
inspection report is overdue. After this query runs I use
a crosstab query to count how many reports are overdue for
various areas.

The query criteria is simply: [dtmConditionSentDate]
[dtmConditionDueDate]

However, now I need to also include the fact that if there
is a value in [dtmConditionDelayDate], then the original
[dtmConditionDueDate] is no longer part of the equation.

For example:

Sent Date Due Date Delay Date Overdue?
Mar 15 2004 Mar 10 2004 Yes - as
of March 11
Mar 15 2004 Mar 10 2004 Apr 1 2004 Not until
April 2

I can think of the logic for this in my head, but I am
having no luck getting this to work in Access. Any help
would be greatly appreciated.

Thanks,
Claire

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Sorry - I made a typo. Anytime you see 'iff' it should
be 'iif'.
-----Original Message-----
nz is a function equivalent to:

if dtmConditionDelayDate is not null then use it
else use dtmConditionDueDate

if you create a new field in your query:

UseDate: nz(dtmConditionDelayDate,dtmConditionDueDate)

Then you can use 'UseDate' to calculate if it is overdue:

OverDue: iff(UseDate > date(),True,False)

or putting in all in one step...

OverDue:
iff(nz(dtmConditionDelayDate,dtmConditionDueDate)
date(),True,False)
Click to expand...

OverDue will then be equal to True when you want it to be
(calculated from today's date) if I understand
correctly. Since you say you're wanting to count, you
might use 1 and 0 instead of True and False and then sum
the result however you need to group it to get the total
number overdue.

-----Original Message-----
Hi Pat, thanks for your response.

How and where do I use the nz statement that you provided?

I thought that I would have to use an IF statement in here
somewhere because if there is a dtmConditionDelayDate
value that is in the future, then the report is not
overdue and should not be counted.

Am I way out of whack here?

Thanks
Claire
-----Original Message-----
Assuming that dtmConditionDelayDate is Null if it is not
being used, try

nz(dtmConditionDelayDate,dtmConditionDueDate)

This will return a the DelayDate if it exists - otherwise
it will return the original due date.


-----Original Message-----
Hi everybody, I am using Access 2000.

I have a select query that is testing to see if an
inspection report is overdue. After this query runs I
use
a crosstab query to count how many reports are overdue
for
various areas.

The query criteria is simply:
Click to expand...
[dtmConditionSentDate]
[dtmConditionDueDate]

However, now I need to also include the fact that if
there
is a value in [dtmConditionDelayDate], then the original
[dtmConditionDueDate] is no longer part of the equation.

For example:

Sent Date Due Date Delay Date Overdue?
Mar 15 2004 Mar 10 2004 Yes - as
of March 11
Mar 15 2004 Mar 10 2004 Apr 1 2004 Not until
April 2

I can think of the logic for this in my head, but I am
having no luck getting this to work in Access. Any help
would be greatly appreciated.

Thanks,
Claire

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Brilliant! That is what I was trying to squeeze out of my
head.......maybe I was squeezing too hard!

Exactly what I had in mind, thanks a bunch Pat.

Cheers
Claire
-----Original Message-----
nz is a function equivalent to:

if dtmConditionDelayDate is not null then use it
else use dtmConditionDueDate

if you create a new field in your query:

UseDate: nz(dtmConditionDelayDate,dtmConditionDueDate)

Then you can use 'UseDate' to calculate if it is overdue:

OverDue: iff(UseDate > date(),True,False)

or putting in all in one step...

OverDue:
iff(nz(dtmConditionDelayDate,dtmConditionDueDate)
date(),True,False)
Click to expand...

OverDue will then be equal to True when you want it to be
(calculated from today's date) if I understand
correctly. Since you say you're wanting to count, you
might use 1 and 0 instead of True and False and then sum
the result however you need to group it to get the total
number overdue.

-----Original Message-----
Hi Pat, thanks for your response.

How and where do I use the nz statement that you provided?

I thought that I would have to use an IF statement in here
somewhere because if there is a dtmConditionDelayDate
value that is in the future, then the report is not
overdue and should not be counted.

Am I way out of whack here?

Thanks
Claire
-----Original Message-----
Assuming that dtmConditionDelayDate is Null if it is not
being used, try

nz(dtmConditionDelayDate,dtmConditionDueDate)

This will return a the DelayDate if it exists - otherwise
it will return the original due date.


-----Original Message-----
Hi everybody, I am using Access 2000.

I have a select query that is testing to see if an
inspection report is overdue. After this query runs I
use
a crosstab query to count how many reports are overdue
for
various areas.

The query criteria is simply: [dtmConditionSentDate]
Click to expand...
[dtmConditionDueDate]

However, now I need to also include the fact that if
there
is a value in [dtmConditionDelayDate], then the original
[dtmConditionDueDate] is no longer part of the equation.

For example:

Sent Date Due Date Delay Date Overdue?
Mar 15 2004 Mar 10 2004 Yes - as
of March 11
Mar 15 2004 Mar 10 2004 Apr 1 2004 Not until
April 2

I can think of the logic for this in my head, but I am
having no luck getting this to work in Access. Any help
would be greatly appreciated.

Thanks,
Claire

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top