Updating a Date field based on another field

arholly · November 21st, 2006, 12:07 PM

Hi everyone,
I actually have two questions I need to ask and I'll break it up so it's easier to answer.

Question #1: I have a table where I have two date fields. "First_Session_Used" and "Scheduled_Review_Date" are the two fields. I need to create a query which will take the date in the "First_Session_Used" and increase that date by one year, but also change it so that it is the first date in that month. So, if the date in "First_Session_Used" is 6/7/06, the date would need to be 6/1/07 in "Scheduled_Review_Date". So, I need an update query which will help me accomplish this. Incidentally, the table is "Tbl_Book_Holding".

Question #2: Once I get my dates updated in the table using the query above, I need to modify the form so that the "Scheduled_Review_Date" is done in the same way as Question #1. So, suggestions on how to modify the form so that it inputs the information that way.

I appreciate any help anyone can give on this.

Thanks,
Arholly

mmcdonal · November 21st, 2006, 12:33 PM

Hiya,

For the Update Query (assuming a table name of Table1, and using DateStart as the first date field and DateEnd as the updated date field):

UPDATE Table1 SET Table1.DateEnd = DatePart("m",[DateStart]) & "/1/" & (DatePart("yyyy",[DateStart])+1);

Use the same formula on your form on the BEFORE INSERT and BEFORE UPDATE events.

Does this help?

mmcdonal

arholly · November 21st, 2006, 01:13 PM

Hi there,
Thanks for the query. Worked perfectly. I'm going to try the form now. Thanks again for your assistance.

Best Regards,
Arholly

Quote:

quote:Originally posted by mmcdonal
Hiya,

For the Update Query (assuming a table name of Table1, and using DateStart as the first date field and DateEnd as the updated date field):

UPDATE Table1 SET Table1.DateEnd = DatePart("m",[DateStart]) & "/1/" & (DatePart("yyyy",[DateStart])+1);

Use the same formula on your form on the BEFORE INSERT and BEFORE UPDATE events.

Does this help?

mmcdonal

arholly · November 21st, 2006, 01:29 PM

Hi there again,
I'm not getting this to work for the form. Am I missing something?

Best Regards,
Arholly

Quote:

quote:Originally posted by mmcdonal
Hiya,

For the Update Query (assuming a table name of Table1, and using DateStart as the first date field and DateEnd as the updated date field):

UPDATE Table1 SET Table1.DateEnd = DatePart("m",[DateStart]) & "/1/" & (DatePart("yyyy",[DateStart])+1);

Use the same formula on your form on the BEFORE INSERT and BEFORE UPDATE events.

Does this help?

mmcdonal

mmcdonal · November 21st, 2006, 02:07 PM

Sorry, on the Before Insert event you would put:

Dim dtDateEnd As Date

dtDateEnd = DatePart("m",[DateStart]) & "/1/" & (DatePart("yyyy",[DateStart])+1)

Me.DateEnd = dtDateEnd

And the same on the Before Update event.

Did that work?

mmcdonal

arholly · November 21st, 2006, 02:40 PM

Works like a charm. Thanks.

Quote:

quote:Originally posted by mmcdonal
Sorry, on the Before Insert event you would put:

Dim dtDateEnd As Date

dtDateEnd = DatePart("m",[DateStart]) & "/1/" & (DatePart("yyyy",[DateStart])+1)

Me.DateEnd = dtDateEnd

And the same on the Before Update event.

Did that work?

mmcdonal

mmcdonal · November 22nd, 2006, 11:19 AM

FYI: the technical term for this kind of coding is called "Regular Expression." There is an introduction to regular expressions scripting on Microsoft's MSDN site at: http://msdn.microsoft.com/library/de...63906a7353.asp

mmcdonal