Adding a number of days to a date via the user interface

G

Guest

Hi guys,

I am playing with the Issue MDB template. I have 3 dates displayed on a form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user needs to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due Date field and
the date be set to 10 days more than today's date. ie: if today's date is
10th August 2006 and the user enters "+10" in either the Due Date or Closed
Date fields, the date is automatically set to 20th August 2006.

I am sure that there is an easy way to do this but I am stumped!.

Thanks
Regards
Greg
 
A

Allen Browne

You won't be able to use the same text box to enter dates and numbers, since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If
 
G

Guest

Hi Allen

I tried this but it does not work...

Allen Browne said:
You won't be able to use the same text box to enter dates and numbers, since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi guys,

I am playing with the Issue MDB template. I have 3 dates displayed on a
form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user needs to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due Date field
and
the date be set to 10 days more than today's date. ie: if today's date is
10th August 2006 and the user enters "+10" in either the Due Date or
Closed
Date fields, the date is automatically set to 20th August 2006.
Click to expand...
Click to expand...
 
A

Allen Browne

What doesn't work?

Did you enter the code in the code window?

When you choose Compile on the Debug menu (in the code window), does it
compile okay or give an error? What error?

When you run the code, does it give an error?
Which line gives the error?
What is the error message?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi Allen

I tried this but it does not work...

Allen Browne said:
You won't be able to use the same text box to enter dates and numbers,
since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

Lateral said:
Hi guys,

I am playing with the Issue MDB template. I have 3 dates displayed on a
form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user needs
to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due Date
field
and
the date be set to 10 days more than today's date. ie: if today's date
is
10th August 2006 and the user enters "+10" in either the Due Date or
Closed
Date fields, the date is automatically set to 20th August 2006.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Allen

I just redid everything again and it still does not do anything.

There is no compile error in the VBA Code Window. Here is all of the code:

Option Compare Database

Private Sub AddDays()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

End Sub

Private Sub txtInterval_AfterUpdate()

End Sub

The above code is "attached" to the txtInterval text box (unbound) with all
of the other options set as you specified.

I am expecting that when the user enters the value "10" in txtInterval, the
Due Date field will be automatically and immediately updated with the date
equal to todays date plus 10 days.

Is this what I should see?

Does the user need to tab to a new field or something else?

Please note that the "Open Date" field is automatically set to todays date
via the field definition in the table having the default value set to
"=Date()"

What am I missing?

Regards
Greg


Allen Browne said:
What doesn't work?

Did you enter the code in the code window?

When you choose Compile on the Debug menu (in the code window), does it
compile okay or give an error? What error?

When you run the code, does it give an error?
Which line gives the error?
What is the error message?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi Allen

I tried this but it does not work...

Allen Browne said:
You won't be able to use the same text box to enter dates and numbers,
since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

Hi guys,

I am playing with the Issue MDB template. I have 3 dates displayed on a
form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user needs
to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due Date
field
and
the date be set to 10 days more than today's date. ie: if today's date
is
10th August 2006 and the user enters "+10" in either the Due Date or
Closed
Date fields, the date is automatically set to 20th August 2006.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Try moving the code into the txtInterval_AfterUpdate sub:

Private Sub txtInterval_AfterUpdate()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi Allen

I just redid everything again and it still does not do anything.

There is no compile error in the VBA Code Window. Here is all of the code:

Option Compare Database

Private Sub AddDays()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

End Sub

Private Sub txtInterval_AfterUpdate()

End Sub

The above code is "attached" to the txtInterval text box (unbound) with
all
of the other options set as you specified.

I am expecting that when the user enters the value "10" in txtInterval,
the
Due Date field will be automatically and immediately updated with the date
equal to todays date plus 10 days.

Is this what I should see?

Does the user need to tab to a new field or something else?

Please note that the "Open Date" field is automatically set to todays date
via the field definition in the table having the default value set to
"=Date()"

What am I missing?

Regards
Greg


Allen Browne said:
What doesn't work?

Did you enter the code in the code window?

When you choose Compile on the Debug menu (in the code window), does it
compile okay or give an error? What error?

When you run the code, does it give an error?
Which line gives the error?
What is the error message?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi Allen

I tried this but it does not work...

:

You won't be able to use the same text box to enter dates and numbers,
since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

Hi guys,

I am playing with the Issue MDB template. I have 3 dates displayed
on a
form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user
needs
to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due Date
field
and
the date be set to 10 days more than today's date. ie: if today's
date
is
10th August 2006 and the user enters "+10" in either the Due Date or
Closed
Date fields, the date is automatically set to 20th August 2006.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Allen

It is now updating the Due Date field but only if Due Date already contains
a date. We are getting closer...

Regards
Greg

Allen Browne said:
Try moving the code into the txtInterval_AfterUpdate sub:

Private Sub txtInterval_AfterUpdate()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi Allen

I just redid everything again and it still does not do anything.

There is no compile error in the VBA Code Window. Here is all of the code:

Option Compare Database

Private Sub AddDays()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

End Sub

Private Sub txtInterval_AfterUpdate()

End Sub

The above code is "attached" to the txtInterval text box (unbound) with
all
of the other options set as you specified.

I am expecting that when the user enters the value "10" in txtInterval,
the
Due Date field will be automatically and immediately updated with the date
equal to todays date plus 10 days.

Is this what I should see?

Does the user need to tab to a new field or something else?

Please note that the "Open Date" field is automatically set to todays date
via the field definition in the table having the default value set to
"=Date()"

What am I missing?

Regards
Greg


Allen Browne said:
What doesn't work?

Did you enter the code in the code window?

When you choose Compile on the Debug menu (in the code window), does it
compile okay or give an error? What error?

When you run the code, does it give an error?
Which line gives the error?
What is the error message?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I tried this but it does not work...

:

You won't be able to use the same text box to enter dates and numbers,
since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

Hi guys,

I am playing with the Issue MDB template. I have 3 dates displayed
on a
form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user
needs
to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due Date
field
and
the date be set to 10 days more than today's date. ie: if today's
date
is
10th August 2006 and the user enters "+10" in either the Due Date or
Closed
Date fields, the date is automatically set to 20th August 2006.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

It should update it unless:
- [Opened Date] is null, or
- txtInterval is null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi Allen

It is now updating the Due Date field but only if Due Date already
contains
a date. We are getting closer...

Regards
Greg

Allen Browne said:
Try moving the code into the txtInterval_AfterUpdate sub:

Private Sub txtInterval_AfterUpdate()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If
End Sub

Lateral said:
Hi Allen

I just redid everything again and it still does not do anything.

There is no compile error in the VBA Code Window. Here is all of the
code:

Option Compare Database

Private Sub AddDays()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

End Sub

Private Sub txtInterval_AfterUpdate()

End Sub

The above code is "attached" to the txtInterval text box (unbound) with
all
of the other options set as you specified.

I am expecting that when the user enters the value "10" in txtInterval,
the
Due Date field will be automatically and immediately updated with the
date
equal to todays date plus 10 days.

Is this what I should see?

Does the user need to tab to a new field or something else?

Please note that the "Open Date" field is automatically set to todays
date
via the field definition in the table having the default value set to
"=Date()"

What am I missing?

Regards
Greg


:

What doesn't work?

Did you enter the code in the code window?

When you choose Compile on the Debug menu (in the code window), does
it
compile okay or give an error? What error?

When you run the code, does it give an error?
Which line gives the error?
What is the error message?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I tried this but it does not work...

:

You won't be able to use the same text box to enter dates and
numbers,
since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter
the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval))
Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened
Date])
End If

Hi guys,

I am playing with the Issue MDB template. I have 3 dates
displayed
on a
form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user
needs
to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due
Date
field
and
the date be set to 10 days more than today's date. ie: if today's
date
is
10th August 2006 and the user enters "+10" in either the Due Date
or
Closed
Date fields, the date is automatically set to 20th August 2006.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Allen,

I will have a play around with it and let you know what happens.

Regards
Greg

Allen Browne said:
It should update it unless:
- [Opened Date] is null, or
- txtInterval is null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi Allen

It is now updating the Due Date field but only if Due Date already
contains
a date. We are getting closer...

Regards
Greg

Allen Browne said:
Try moving the code into the txtInterval_AfterUpdate sub:

Private Sub txtInterval_AfterUpdate()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If
End Sub

Hi Allen

I just redid everything again and it still does not do anything.

There is no compile error in the VBA Code Window. Here is all of the
code:

Option Compare Database

Private Sub AddDays()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

End Sub

Private Sub txtInterval_AfterUpdate()

End Sub

The above code is "attached" to the txtInterval text box (unbound) with
all
of the other options set as you specified.

I am expecting that when the user enters the value "10" in txtInterval,
the
Due Date field will be automatically and immediately updated with the
date
equal to todays date plus 10 days.

Is this what I should see?

Does the user need to tab to a new field or something else?

Please note that the "Open Date" field is automatically set to todays
date
via the field definition in the table having the default value set to
"=Date()"

What am I missing?

Regards
Greg


:

What doesn't work?

Did you enter the code in the code window?

When you choose Compile on the Debug menu (in the code window), does
it
compile okay or give an error? What error?

When you run the code, does it give an error?
Which line gives the error?
What is the error message?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I tried this but it does not work...

:

You won't be able to use the same text box to enter dates and
numbers,
since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter
the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval))
Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened
Date])
End If

Hi guys,

I am playing with the Issue MDB template. I have 3 dates
displayed
on a
form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user
needs
to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due
Date
field
and
the date be set to 10 days more than today's date. ie: if today's
date
is
10th August 2006 and the user enters "+10" in either the Due Date
or
Closed
Date fields, the date is automatically set to 20th August 2006.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Allen

It is working. It required me to first enter the interval and then click the
Due Date field.

Thanks for your help.

Regards
Greg

Allen Browne said:
It should update it unless:
- [Opened Date] is null, or
- txtInterval is null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Lateral said:
Hi Allen

It is now updating the Due Date field but only if Due Date already
contains
a date. We are getting closer...

Regards
Greg

Allen Browne said:
Try moving the code into the txtInterval_AfterUpdate sub:

Private Sub txtInterval_AfterUpdate()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If
End Sub

Hi Allen

I just redid everything again and it still does not do anything.

There is no compile error in the VBA Code Window. Here is all of the
code:

Option Compare Database

Private Sub AddDays()
If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval)) Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened Date])
End If

End Sub

Private Sub txtInterval_AfterUpdate()

End Sub

The above code is "attached" to the txtInterval text box (unbound) with
all
of the other options set as you specified.

I am expecting that when the user enters the value "10" in txtInterval,
the
Due Date field will be automatically and immediately updated with the
date
equal to todays date plus 10 days.

Is this what I should see?

Does the user need to tab to a new field or something else?

Please note that the "Open Date" field is automatically set to todays
date
via the field definition in the table having the default value set to
"=Date()"

What am I missing?

Regards
Greg


:

What doesn't work?

Did you enter the code in the code window?

When you choose Compile on the Debug menu (in the code window), does
it
compile okay or give an error? What error?

When you run the code, does it give an error?
Which line gives the error?
What is the error message?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

I tried this but it does not work...

:

You won't be able to use the same text box to enter dates and
numbers,
since
these are different data types in Access.

Instead, provide another unbound text box where the user can enter
the
interval. Set these properties:
Name txtInterval
Format General Number
After Update [Event Procedure]

Then click the Build button (...) beside the Afterudpate property.
Access opens the code window.
Between the "Private Sub..." and "End Sub" lines, enter:

If Not (IsNull(Me.[Opened Date]) Or IsNull(Me.txtInterval))
Then
Me.[Due Date] = DateAdd("d", Me.txtInterval, Me.[Opened
Date])
End If

Hi guys,

I am playing with the Issue MDB template. I have 3 dates
displayed
on a
form:

Opened Date
Due Date
Closed Date

The Opened Date is automatically set to today's date and the user
needs
to
manually enter dates into the Due Date and Closed Date fields.

I want the user to be able to simply type in "+10" in the Due
Date
field
and
the date be set to 10 days more than today's date. ie: if today's
date
is
10th August 2006 and the user enters "+10" in either the Due Date
or
Closed
Date fields, the date is automatically set to 20th August 2006.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Vba to change displayed year automatically. 14
Specific steps to determine # of days ( today's date and other dat 1
Excel How to get the number of days an item was opened from the today's date. 14
Outlook Macro to set Reminder on a Contact tomorro 9:00 4
Autopopulate a Weekdate from a Recieved date 5
Setting fields to automatically fill in a date. 3
Number of Days formula 3
Add 14 days to Dispatch Date 10

Top