Auto calculate date in form

  • Thread starter Thread starter celarsen
  • Start date Start date
C

celarsen

I need to auto calculate a date to be entered into "Field 2" based on "Field
1 + 20 days"

I looked up DataAdd function and came up with the following but I get a
syntax error.

If field1 exists then DateAdd ("d", 20, "field1") else Null

I used the expression builder on filed 2 but when I clicked on finish I
received he syntax error.
 
celarsen said:
I need to auto calculate a date to be entered into "Field 2" based on "Field
1 + 20 days"

I looked up DataAdd function and came up with the following but I get a
syntax error.

If field1 exists then DateAdd ("d", 20, "field1") else Null

I used the expression builder on filed 2 but when I clicked on finish I
received he syntax error.
Click to expand...


The problem with the code (expression?) builder is that you
can point and click your way to a "sensible", but completely
illegal result. Try this instead:

If IsNull(field1) Then
field2 = Null
Else
Field2 = DateAdd ("d", 20, field1)
End If
 
I put in the code with code builder. When I opened the form and put in a date
in field1, nothing filled in in field2. I put the code on field2.

Here is the code:

Private Sub Default_File_Date_BeforeUpdate(Cancel As Integer)
If IsNull(SC_Served) Then
Default_File_Date = Null
Else
Default_File_Date = DateAdd("d", 20, SC_Served)
End If
End Sub

Both fields are currently set as date/time fields in the table. Is this
correct?
 
Use the AfterUpdate event instead.

It's not at all clear what you are referring to when you use
field1 and field2.

It seems illogicical to use a Default_File_Date event to set
Default_File_Date's value. I think the code should be in
the SC_Served text box's AfterUpdate event.
 
I'll try what you suggest explain more.

I have a field that is to contain a date when paperwork was delivered to a
person. I need to automatically populate a followup date field that is 20
days after the day the papers were delivered. Field1 would be the day the
papers were delivered and field2 is the calculated date.

I hope this clears this up more.

I'll let you know if the code works.

Marshall Barton said:
Use the AfterUpdate event instead.

It's not at all clear what you are referring to when you use
field1 and field2.

It seems illogicical to use a Default_File_Date event to set
Default_File_Date's value. I think the code should be in
the SC_Served text box's AfterUpdate event.
--
Marsh
MVP [MS Access]

I put in the code with code builder. When I opened the form and put in a date
in field1, nothing filled in in field2. I put the code on field2.

Here is the code:

Private Sub Default_File_Date_BeforeUpdate(Cancel As Integer)
If IsNull(SC_Served) Then
Default_File_Date = Null
Else
Default_File_Date = DateAdd("d", 20, SC_Served)
End If
End Sub

Both fields are currently set as date/time fields in the table. Is this
correct?
Click to expand...
Click to expand...
 
celarsen said:
I'll try what you suggest explain more.

I have a field that is to contain a date when paperwork was delivered to a
person. I need to automatically populate a followup date field that is 20
days after the day the papers were delivered. Field1 would be the day the
papers were delivered and field2 is the calculated date.

I hope this clears this up more.
Click to expand...


If have figured out what you mean by the vague phrases "a
field", "field1" and "field2", then I believe I provided a
useful reply.

If I have not interpreted those terms correctly, then you
have completely lost me.
 
Your code worked. Thanks for the help.

When I said "Field" I meant a field in a table. Field 1 , Field 2, ETC.,
represented different fields in the table.

Thanks again.
 
Back
Top