From: Iram on
Hello,

For every record in a table I need to update one of four Yes/No check box
fields based upon date span.

If the "In Date" is in the same month as "Serve D" or "Return D" field place
a check in the "DispodWithinSameMonth" yes/no field.

If the "In Date" is within 30 days of the "Serve D" or "Return D" field
place a check in the "DispodWithin30Days" yes/no field.

If the "In Date" is within 60 days of the "Serve D" or "Return D" field
place a check in the "DispodWithin60Days" yes/no field.

If the "In Date" is within 90 days of the "Serve D" or "Return D" field
place a check in the "DispodWithin90Days" yes/no field.

There are seven fields total
In Date
Return D
Serve D
DispodWithinSameMonth
DispodWithin30Days
DispodWithin60Days
DispodWithin90Days

Could you help me understand how I would write the update query?
I am not that great with Update Queries and any detail will be greatly
appreciated!


Thanks.
Iram/mcp

From: John W. Vinson on
On Wed, 3 Mar 2010 16:53:01 -0800, Iram <Iram(a)discussions.microsoft.com>
wrote:

>Hello,
>
>For every record in a table I need to update one of four Yes/No check box
>fields based upon date span.
>
>If the "In Date" is in the same month as "Serve D" or "Return D" field place
>a check in the "DispodWithinSameMonth" yes/no field.
>
>If the "In Date" is within 30 days of the "Serve D" or "Return D" field
>place a check in the "DispodWithin30Days" yes/no field.
>
>If the "In Date" is within 60 days of the "Serve D" or "Return D" field
>place a check in the "DispodWithin60Days" yes/no field.
>
>If the "In Date" is within 90 days of the "Serve D" or "Return D" field
>place a check in the "DispodWithin90Days" yes/no field.
>
>There are seven fields total
>In Date
>Return D
>Serve D
>DispodWithinSameMonth
>DispodWithin30Days
>DispodWithin60Days
>DispodWithin90Days

I think you're misguided here.

If you store a Yes or a No in one of these yes/no fields in 100 records in
your table... you can be ABSOLUTELY CERTAIN that every one of those records
will contain *incorrect* data within 30 days. The Dispod fields *should simply
not exist* in your table.

These yes/no fields should instead be calculated on the fly, dynamically, from
the date, and NOT stored in your table. You can do so with calculated fields
using the IIF() and DateDiff() functions.

There are anomalies in your rules though! If Serve D is 1/31/2010 and Return D
is 2/1/2010 they're just a day apart... but not within the same month. Not all
months are thirty days. Some months are 31 days, so it could be within the
same month but not within 30 days. Is that OK?

Just an example of how to do this:

DispodWithin30Days: (DateDiff("d", [In Date], [Serve D]) <= 30
OR DateDiff("d", [In Date], [Return Date]) <= 30)

This expression will be TRUE if either of the date ranges is 30 or less, FALSE
if neither expression is true.

This will fail if any of the three date fields is NULL, you'll need to use the
NZ function to convert nulls to a reasonable date.

Could you explain the real-life situation you're modeling?


--

John W. Vinson [MVP]
From: Iram on
Hi John,
Btw, thanks for your time!

This database is used to track a Process Servers Job. The In Date is the
date that he receives the papers to serve. He either serves the papers and
records the "Serve D" or he returns the papers back to his boss and records
the "Returned D". His boss wants to be able to run a report that showed which
cases were served or returned within the same month, within 30 days, 60 days
or 90 days, (new one) greater than 90 days.

As for saving the calculated data into the table, you are right. I'll just
run the calculated data off a form or report from a query.

As for your question, Yes that is ok!

Your Awesome John!

Iram/mcp



"John W. Vinson" wrote:

> On Wed, 3 Mar 2010 16:53:01 -0800, Iram <Iram(a)discussions.microsoft.com>
> wrote:
>
> >Hello,
> >
> >For every record in a table I need to update one of four Yes/No check box
> >fields based upon date span.
> >
> >If the "In Date" is in the same month as "Serve D" or "Return D" field place
> >a check in the "DispodWithinSameMonth" yes/no field.
> >
> >If the "In Date" is within 30 days of the "Serve D" or "Return D" field
> >place a check in the "DispodWithin30Days" yes/no field.
> >
> >If the "In Date" is within 60 days of the "Serve D" or "Return D" field
> >place a check in the "DispodWithin60Days" yes/no field.
> >
> >If the "In Date" is within 90 days of the "Serve D" or "Return D" field
> >place a check in the "DispodWithin90Days" yes/no field.
> >
> >There are seven fields total
> >In Date
> >Return D
> >Serve D
> >DispodWithinSameMonth
> >DispodWithin30Days
> >DispodWithin60Days
> >DispodWithin90Days
>
> I think you're misguided here.
>
> If you store a Yes or a No in one of these yes/no fields in 100 records in
> your table... you can be ABSOLUTELY CERTAIN that every one of those records
> will contain *incorrect* data within 30 days. The Dispod fields *should simply
> not exist* in your table.
>
> These yes/no fields should instead be calculated on the fly, dynamically, from
> the date, and NOT stored in your table. You can do so with calculated fields
> using the IIF() and DateDiff() functions.
>
> There are anomalies in your rules though! If Serve D is 1/31/2010 and Return D
> is 2/1/2010 they're just a day apart... but not within the same month. Not all
> months are thirty days. Some months are 31 days, so it could be within the
> same month but not within 30 days. Is that OK?
>
> Just an example of how to do this:
>
> DispodWithin30Days: (DateDiff("d", [In Date], [Serve D]) <= 30
> OR DateDiff("d", [In Date], [Return Date]) <= 30)
>
> This expression will be TRUE if either of the date ranges is 30 or less, FALSE
> if neither expression is true.
>
> This will fail if any of the three date fields is NULL, you'll need to use the
> NZ function to convert nulls to a reasonable date.
>
> Could you explain the real-life situation you're modeling?
>
>
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on
On Thu, 4 Mar 2010 10:24:02 -0800, Iram <Iram(a)discussions.microsoft.com>
wrote:

>Hi John,
>Btw, thanks for your time!
>
>This database is used to track a Process Servers Job. The In Date is the
>date that he receives the papers to serve. He either serves the papers and
>records the "Serve D" or he returns the papers back to his boss and records
>the "Returned D". His boss wants to be able to run a report that showed which
>cases were served or returned within the same month, within 30 days, 60 days
>or 90 days, (new one) greater than 90 days.

If only one of Serve D and Returned D will be NULL you might be able to use an
expression like

DispodWithin30Days: (DateDiff("d", [In Date], NZ([Serve D], [Returned D])) <=
30

You'll still want some sort of criterion to find those cases where the paper
has neither been served nor returned: it's not clear what you want to do in
that case.
--

John W. Vinson [MVP]
From: Iram on
For cases that don't have neither a Served or Returned date can we create a
calculated field that counts them as well?

"John W. Vinson" wrote:

> On Thu, 4 Mar 2010 10:24:02 -0800, Iram <Iram(a)discussions.microsoft.com>
> wrote:
>
> >Hi John,
> >Btw, thanks for your time!
> >
> >This database is used to track a Process Servers Job. The In Date is the
> >date that he receives the papers to serve. He either serves the papers and
> >records the "Serve D" or he returns the papers back to his boss and records
> >the "Returned D". His boss wants to be able to run a report that showed which
> >cases were served or returned within the same month, within 30 days, 60 days
> >or 90 days, (new one) greater than 90 days.
>
> If only one of Serve D and Returned D will be NULL you might be able to use an
> expression like
>
> DispodWithin30Days: (DateDiff("d", [In Date], NZ([Serve D], [Returned D])) <=
> 30
>
> You'll still want some sort of criterion to find those cases where the paper
> has neither been served nor returned: it's not clear what you want to do in
> that case.
> --
>
> John W. Vinson [MVP]
> .
>