Dates: "Yesterday" vs "Today"

[Ken Curtis]

I have a form which is used to record all "contacts" (i.e. date and content
of phone, fax and email contacts) which are made to our agency daily. The
first field is "Date of Contact", its input mask is set for short date. The
information comes from notes hand written in a general log by day staff, and
is entered into the data base form by overnight staff.

To speed up data entry, I would like the "Date of Contact" field to fill
itself in automatically AFTER Tab is hit and the field looses focus.
However, because overnight staff is entering data from a previous day (i.e.
they enter contacts from January 1 on January 2) the date entered
automatically must be "yesterdays" date, not "today's" date.

ALSO ... and very important ...

Even with the field set to automatically display (hopefully !!)
"yesterday's" date, there is the issue of backlogging: Sometimes it is
necessary to enter a contact out of sequence (i.e. a missed contact from
December 30 needs to be entered on January 2) which means that the field must
allow manual input (using the input mask) even though it has been set to
enter "Yesterday's" date when Tab is hit.

How can I possibly tell the field all this?

Thanks for your thoughts / help.
Ken Curtis

 

[Allen Browne]

Use the AfterUpdate event procedure of the text box to assign the newly
entered date as the default value for the control.

Assuming a text box named Text23:

Private Sub Text23_AfterUpdate()
With Me.Text23
If Not IsNull(.Value) Then
.DefaultValue = Format$(.Value, "\#mm/dd/yyyy\#")
End If
End With
End Sub

 

[Marshall Barton]

I have a form which is used to record all "contacts" (i.e. date and content
of phone, fax and email contacts) which are made to our agency daily. The
first field is "Date of Contact", its input mask is set for short date. The
information comes from notes hand written in a general log by day staff, and
is entered into the data base form by overnight staff.

To speed up data entry, I would like the "Date of Contact" field to fill
itself in automatically AFTER Tab is hit and the field looses focus.
However, because overnight staff is entering data from a previous day (i.e.
they enter contacts from January 1 on January 2) the date entered
automatically must be "yesterdays" date, not "today's" date.

ALSO ... and very important ...

Even with the field set to automatically display (hopefully !!)
"yesterday's" date, there is the issue of backlogging: Sometimes it is
necessary to enter a contact out of sequence (i.e. a missed contact from
December 30 needs to be entered on January 2) which means that the field must
allow manual input (using the input mask) even though it has been set to
enter "Yesterday's" date when Tab is hit.

Sounds very confusing to me. Why not just set the text
box's DefaultValue property to DateAdd("d", -1, Date())

If you really need what I think you said, try using:

If Not IsNull(Me.[Date of Contact]) Then
Me.[Date of Contact] = DateAdd("d", -1, Date())
End If

 

[Ken Curtis]

This almost works, Marshall Barton. And it's remarkably brief! However, if I
enter no date into the field (i.e. if I just Tab out of it to the next
field), it gives me todays date and not yesterdays date (and I need
yesterday's date there, not today's date; not the date it is being filled in,
the date when the contact was made ... which is usually yesterday).

Thoughts
--
Ken Curtis

I have a form which is used to record all "contacts" (i.e. date and content
of phone, fax and email contacts) which are made to our agency daily. The
first field is "Date of Contact", its input mask is set for short date. The
information comes from notes hand written in a general log by day staff, and
is entered into the data base form by overnight staff.

To speed up data entry, I would like the "Date of Contact" field to fill
itself in automatically AFTER Tab is hit and the field looses focus.
However, because overnight staff is entering data from a previous day (i.e.
they enter contacts from January 1 on January 2) the date entered
automatically must be "yesterdays" date, not "today's" date.

ALSO ... and very important ...

Even with the field set to automatically display (hopefully !!)
"yesterday's" date, there is the issue of backlogging: Sometimes it is
necessary to enter a contact out of sequence (i.e. a missed contact from
December 30 needs to be entered on January 2) which means that the field must
allow manual input (using the input mask) even though it has been set to
enter "Yesterday's" date when Tab is hit.

Sounds very confusing to me. Why not just set the text
box's DefaultValue property to DateAdd("d", -1, Date())

If you really need what I think you said, try using:

If Not IsNull(Me.[Date of Contact]) Then
Me.[Date of Contact] = DateAdd("d", -1, Date())
End If

 

[Ken Curtis]

Wow ... Ok. Question, Allen: Do I just paste your creation into the
AfterUpdate spot (after I change "Text23" to the actual name of the control?
Is that it?

Regards

 

[Allen Browne]

Try it, Ken.

(I'm assuming that whatever date you enter should be the default for new
records, until you enter a different date.)

 

[Ken Curtis]

Thanks, Allen!
Regards
--
Ken Curtis

Try it, Ken.

(I'm assuming that whatever date you enter should be the default for new
records, until you enter a different date.)

 

[Marshall Barton]

This almost works, Marshall Barton. And it's remarkably brief! However, if I
enter no date into the field (i.e. if I just Tab out of it to the next
field), it gives me todays date and not yesterdays date (and I need
yesterday's date there, not today's date; not the date it is being filled in,
the date when the contact was made ... which is usually yesterday).

DateAdd("d", -1, Date()) is the day before the day the
expression is evaluated/executed.

I suggested two alternate ideas. Which one did you try?
And please include a Copy/Paste of whatever you used so I
can double check it.

 

[tina]

Marsh, did you mean to say

If IsNull(Me.[Date of Contact]) Then
Me.[Date of Contact] = DateAdd("d", -1, Date())
End If

otherwise, Not IsNull(... is going to overwrite any other date entered
manually in the control.

hth

I have a form which is used to record all "contacts" (i.e. date and content
of phone, fax and email contacts) which are made to our agency daily. The
first field is "Date of Contact", its input mask is set for short date. The
information comes from notes hand written in a general log by day staff, and
is entered into the data base form by overnight staff.

To speed up data entry, I would like the "Date of Contact" field to fill
itself in automatically AFTER Tab is hit and the field looses focus.
However, because overnight staff is entering data from a previous day (i.e.
they enter contacts from January 1 on January 2) the date entered
automatically must be "yesterdays" date, not "today's" date.

ALSO ... and very important ...

Even with the field set to automatically display (hopefully !!)
"yesterday's" date, there is the issue of backlogging: Sometimes it is
necessary to enter a contact out of sequence (i.e. a missed contact from
December 30 needs to be entered on January 2) which means that the field must
allow manual input (using the input mask) even though it has been set to
enter "Yesterday's" date when Tab is hit.

Sounds very confusing to me. Why not just set the text
box's DefaultValue property to DateAdd("d", -1, Date())

If you really need what I think you said, try using:

If Not IsNull(Me.[Date of Contact]) Then
Me.[Date of Contact] = DateAdd("d", -1, Date())
End If

 

[Marshall Barton]

Marsh, did you mean to say

If IsNull(Me.[Date of Contact]) Then
Me.[Date of Contact] = DateAdd("d", -1, Date())
End If

otherwise, Not IsNull(... is going to overwrite any other date entered
manually in the control.

Arrggghhh!

Yes, Tina, that is what I meant. Good catch, I could have
stared at that all day without seeing it :-(

 

[tina]

lol, that sounds just like me! <g>

Marsh, did you mean to say

If IsNull(Me.[Date of Contact]) Then
Me.[Date of Contact] = DateAdd("d", -1, Date())
End If

otherwise, Not IsNull(... is going to overwrite any other date entered
manually in the control.

Arrggghhh!

Yes, Tina, that is what I meant. Good catch, I could have
stared at that all day without seeing it :-(