Add variable days to date

[Guest]

I have an Access form with a date field (Due Date) that can be either an
arbitrary date entered by the user (easy enough) or a date that is the result
of adding a variable number of days to another date field (call it Expiration
Date) in the table.

Ideally there would be a command button on the form that prompts the user to
enter the desired number of days and this number would be added to the other
date (Expiration) and appear in the Due Date field.

Any ideas, please?

 

[Rick B]

If it were me, I would just add an unbound field and place a label next to
it called something like "Days until due". I'd place code in the On change
event of that field that addes the date field you mentioned plus the number
entered in the unbound field. No need for a button that way. If it were
me, I'd probably disable this field if there was a manual entry in the "due
date" field. I'd probably also limit the valid entries to fall within a
certain number of days.

Hope that helps,

Rick B

 

[Guest]

Great idea(s)! I can do the dimming part (disabling) and control the number
of days entered. But could you give me an idea on how to write the code that
adds the date plus the number of days? Thanks so much.

 

[Rick B]

Ok, here is the code I wrote in a test form. I created a table with a
Customer, Due_Date, and Date_of_sale fields.

I created a form and placed those fields on it. I also made an unbound
field (Text3).

I then added the following code. to handle it. The "current" code blanks
out the Text3 field any time you move to a different record. It also moves
the focus (cursor) to the first field.

The AfterUpdate code on my text box tells it what to do when the user makes
a change to the text3 field. It looks to verify that there is not already
an entry in the Due_Date field. If there is not, then it does the math.

You could further lock the Text3 field if there is an entry in the due date,
but it is not necessary since the code makes sure the field is blank before
changing it.

---------------------------------------

Private Sub Form_Current()
Text3 = ""
Me.customer.SetFocus
End Sub

Private Sub Text3_AfterUpdate()
If IsNull(due_date) Then
Me.due_date = DateAdd("d", Val([Text3]), date_of_sale)
End If
End Sub

-----------------------------------------

I hope that helps,

Rick B

 

[Guest]

Thanks so much, Rick. Works great! Lisa

Ok, here is the code I wrote in a test form. I created a table with a
Customer, Due_Date, and Date_of_sale fields.

I created a form and placed those fields on it. I also made an unbound
field (Text3).

I then added the following code. to handle it. The "current" code blanks
out the Text3 field any time you move to a different record. It also moves
the focus (cursor) to the first field.

The AfterUpdate code on my text box tells it what to do when the user makes
a change to the text3 field. It looks to verify that there is not already
an entry in the Due_Date field. If there is not, then it does the math.

You could further lock the Text3 field if there is an entry in the due date,
but it is not necessary since the code makes sure the field is blank before
changing it.

---------------------------------------

Private Sub Form_Current()
Text3 = ""
Me.customer.SetFocus
End Sub

Private Sub Text3_AfterUpdate()
If IsNull(due_date) Then
Me.due_date = DateAdd("d", Val([Text3]), date_of_sale)
End If
End Sub

-----------------------------------------

I hope that helps,

Rick B

Great idea(s)! I can do the dimming part (disabling) and control the number
of days entered. But could you give me an idea on how to write the code that
adds the date plus the number of days? Thanks so much.