Testing for Date Value then Displaying the Date or Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deborah Najm - 01 Dec 2005 01:41 GMT

Hi,

I have a form that currently displays the date of a visit.

Currently the user can only input a date.

I would like to be able to show the text control as a Date OR a value of
'Missed Visit'. The user needs to be able to enter a Date or 'Missed
Visit'.

If the date is entered then the field 'Visit Date' is updated, if 'Missed
Visit' is entered then 'Visit Status' is updated to 'Missed Visit'

I was trying to write a function that looked up the Visit Date, checked the
Visit Status and displayed either one, and a function that would properly
update the fields, but I am hopeless and a novice - any advice?

Deborah

Reply to this Message

Allen Browne - 01 Dec 2005 02:36 GMT

The Date/Time field can contain only a date, or a Null (blank).
"Missed Visit" is text, so that cannot go into a Date/Time field.

You can display the message on a report, by using this Control Source:
=IIf([Date1] Is Null, "Missed Visit", Format([Date1], "Short Date")
replacing Date1 with the name of your field, and making sure the Name of
this text box is not the same as a field name.

You could do that in a form as well, but you could not type into the box. If
that's important, you could place it on top of the real text box (Format |
Bring to Front), and use its Enter event to SetFocus to the real box (which
has its TabStop property set to No.)

Or, you might just use conditional formatting to highlight the blanks with a
different color:
Expression ... [Date1] Is Null

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I have a form that currently displays the date of a visit.
>
[quoted text clipped - 12 lines]
>
> Deborah

Reply to this Message

Deborah Najm - 05 Dec 2005 17:19 GMT

Is it possible to use one control to enter values into two different fields?

If the user enters the text - "Missed Visit" update the field "Visit_Status"
to "Missed Visit", if the value is a date, update the field "Visit_Date" to
the Date value.

Then, to display use: =IIf([Visit_Date] is Null and [Visit_Status] = 'Missed
Visit', "Missed Visit", Format([Visit_Date], "Short Date")

Deborah

> The Date/Time field can contain only a date, or a Null (blank).
> "Missed Visit" is text, so that cannot go into a Date/Time field.
[quoted text clipped - 29 lines]
>>
>> Deborah

Reply to this Message

Allen Browne - 06 Dec 2005 01:26 GMT

If you want to use that kind of interface, you will have to use an unbound
control. In its AfterUpdate event procedure, examine if it IsDate(), and
write it to the appropriate field.

Personally I would not do it that way.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Is it possible to use one control to enter values into two different
> fields?
[quoted text clipped - 39 lines]
>>> the Visit Status and displayed either one, and a function that would
>>> properly update the fields, but I am hopeless and a novice - any advice?

Reply to this Message