If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query
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 |
#2
|
|||
|
|||
Update Query
On Wed, 3 Mar 2010 16:53:01 -0800, Iram
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] |
#3
|
|||
|
|||
Update Query
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 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] . |
#4
|
|||
|
|||
Update Query
On Thu, 4 Mar 2010 10:24:02 -0800, Iram
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] |
#5
|
|||
|
|||
Update Query
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 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] . |
#6
|
|||
|
|||
Update Query
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 Iram wrote: 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 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] . |
#7
|
|||
|
|||
Update Query
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 "John Spencer" wrote: 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 Iram wrote: 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 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] . . |
#8
|
|||
|
|||
Update Query
On Fri, 5 Mar 2010 14:16:17 -0800, Iram
wrote: 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 [MVP] |
#9
|
|||
|
|||
Update Query
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 "John W. Vinson" wrote: On Fri, 5 Mar 2010 14:16:17 -0800, Iram wrote: 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 [MVP] . |
#10
|
|||
|
|||
Update Query
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 "Iram" wrote: 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 "John W. Vinson" wrote: On Fri, 5 Mar 2010 14:16:17 -0800, Iram wrote: 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 [MVP] . |
|
Thread Tools | |
Display Modes | |
|
|