Update Query

[Iram]

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

 

[John W. Vinson]

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?

 

[Iram]

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

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]

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.

 

[Iram]

For cases that don't have neither a Served or Returned date can we create a
calculated field that counts them as well?

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 Spencer]

Pending: IIF(Serve D] is Null and [Returned d] is Null, 1,0)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

For cases that don't have neither a Served or Returned date can we create a
calculated field that counts them as well?

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.

 

[Iram]

Hello fellas,
I added the below calculated fields to the query and adjusted the names.
DispodWithin30Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=30)
DispodWithin60Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=60)
DispodWithin90Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=90)

Problem, if there is a Serve D or a Return D within one month of the In Date
than all three calculated fields become -1. This is because the Serve or
Return date is less than 90 days and less than 60 and less than 30. How can I
fix this?

When I added the below calculation get an error...
Pending: IIF(Serve D] is Null and [Return D] is Null, 1,0)

Error:
The expression you entered contains invalid syntax
You may have entered an operand without an operator.


Your help is greatly appreciated!
Thanks.
Iram/mcp

Pending: IIF(Serve D] is Null and [Returned d] is Null, 1,0)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

For cases that don't have neither a Served or Returned date can we create a
calculated field that counts them as well?

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]

Problem, if there is a Serve D or a Return D within one month of the In Date
than all three calculated fields become -1. This is because the Serve or
Return date is less than 90 days and less than 60 and less than 30. How can I
fix this?

When I added the below calculation get an error...
Pending: IIF(Serve D] is Null and [Return D] is Null, 1,0)

Error:
The expression you entered contains invalid syntax
You may have entered an operand without an operator.

Use more stringent criteria, and change the syntax of the IIF:

DispodWithin30Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=30)
DispodWithin60Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>30 AND
DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=60)
DispodWithin90Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>60 AND
DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=90)

Pending: IIF(IsNull([Serve D]) AND IsNull( [Return D]),-1,0)

You're not explicitly catching records that never get returned at all... you
might want to add a new one checking for > 90.

 

[Iram]

Thank you John V. the calculated fields worked perfectly!
Question: Why can't I see these calculated fields in a new repot which
inlcude the calculated fields? In the query I see -1's and 0's but in the
report I don't see these -1's and 0's, nothing is appearing. The report
wizard made text boxes boxes for the calculated fields so then I removed them
and place check boxes in there places and still nothing. Any suggestions?


Thanks.
Iram/mcp

Problem, if there is a Serve D or a Return D within one month of the In Date
than all three calculated fields become -1. This is because the Serve or
Return date is less than 90 days and less than 60 and less than 30. How can I
fix this?

When I added the below calculation get an error...
Pending: IIF(Serve D] is Null and [Return D] is Null, 1,0)

Error:
The expression you entered contains invalid syntax
You may have entered an operand without an operator.

Use more stringent criteria, and change the syntax of the IIF:

DispodWithin30Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=30)
DispodWithin60Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>30 AND
DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=60)
DispodWithin90Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>60 AND
DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=90)

Pending: IIF(IsNull([Serve D]) AND IsNull( [Return D]),-1,0)

You're not explicitly catching records that never get returned at all... you
might want to add a new one checking for > 90.

 

[Iram]

John, some how the -1's and 0's started to appear in the reports, all is good!

I don't know what I would do with out you guys!
I am really happy and appreciative of your guys efforts!

Thanks John W. Vinson and John Spencer


Iram/mcp

Thank you John V. the calculated fields worked perfectly!
Question: Why can't I see these calculated fields in a new repot which
inlcude the calculated fields? In the query I see -1's and 0's but in the
report I don't see these -1's and 0's, nothing is appearing. The report
wizard made text boxes boxes for the calculated fields so then I removed them
and place check boxes in there places and still nothing. Any suggestions?


Thanks.
Iram/mcp

Problem, if there is a Serve D or a Return D within one month of the In Date
than all three calculated fields become -1. This is because the Serve or
Return date is less than 90 days and less than 60 and less than 30. How can I
fix this?

When I added the below calculation get an error...
Pending: IIF(Serve D] is Null and [Return D] is Null, 1,0)

Error:
The expression you entered contains invalid syntax
You may have entered an operand without an operator.

Use more stringent criteria, and change the syntax of the IIF:

DispodWithin30Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=30)
DispodWithin60Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>30 AND
DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=60)
DispodWithin90Days: (DateDiff("d",[In Date],NZ([Serve D],[Return D]))>60 AND
DateDiff("d",[In Date],NZ([Serve D],[Return D]))<=90)

Pending: IIF(IsNull([Serve D]) AND IsNull( [Return D]),-1,0)

You're not explicitly catching records that never get returned at all... you
might want to add a new one checking for > 90.

 

[John W. Vinson]

John, some how the -1's and 0's started to appear in the reports, all is good!

You can set the Format property of the textbox to

"";"Yes";"No";""
"";"True";"False";""

if you want the -1 to display as Yes/True, 0 as No/False. This rather obscure
four-value format applies four different formats to numeric fields that are
positive, negative, zero and NULL respectively. Since a yes/no field is either
0 or -1, the other options aren't needed.