Testing for Date Value then Displaying the Date or Text

  • Thread starter Thread starter Deborah Najm
  • Start date Start date
D

Deborah Najm

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
 
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
 
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
Allen Browne said:
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

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

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

Deborah Najm said:
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
Click to expand...
Click to expand...
 
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.

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

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

Deborah Najm said:
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
Allen Browne said:
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

Deborah Najm said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top