Hiding a specific value from the user in a form

[ascagnel]

Hello...

On a form, I have a field that lists when an employee was hired and
fired. Since these fields are queried with an SQL BETWEEN statment,
the fired date must be filled in for a proper response, and the date
of 9/9/9999 is filled in by default.

On the form for this table, I would like to set up the text box so
that it displays as empty if the date is 9/9/9999. However, I can't
figure out on what event to put the changes, and since there's no way
to modify just the displayed value, only the underlying value, using
the default VBA properties, I'm stuck as to what to do.

So far, I've only been able to set the value to blank by using the "On
Current" event.

 

[Klatuu]

A better solution would be to leave the field in the table Null rather than
filling bogus data. As you are seeing, then you have to code around it every
time you use it. Then when you need to use it in your BETWEEN
BETWEEN HireDate AND Nz(FireDate, Date())

So if the FireDate is Null, it will use the current date as the upper limit.

 

[NKTower]

Let me assume that the date fired is table column named "date_terminated".

1) Make the bound control associated with date_terminated Visible=False

2) Put an UNBOUND text control on the form, let's call it
"txt_DateTerminatedDisplay"

3) In From's OnCurrent event

If date_terminated = "9/9/9999" Then
txt_DateTerminatedDisplay = Null
Else
txt_DateTerminatedDisplay = date_terminated
End If

--- That takes care of data coming in off the database. Now to handle user
input which can either be to delete the termination date or to enter/change
value...

Private Sub txt_DateTerminatedDisplay_AfterUpate()
' Handle clearing of fired date
If IsNull(txt_DateTerminatedDisplay) AND
date_terminated <> "9/9/9999' Then
date_terminated = Null
exit sub
End If
' Handle putting in or revising a value
date_terminated = txt_DateTerminatedDisplay
End Sub


I think that will do it.

 

[John W. Vinson/MVP]

Hello...

On a form, I have a field that lists when an employee was hired and
fired. Since these fields are queried with an SQL BETWEEN statment,
the fired date must be filled in for a proper response, and the date
of 9/9/9999 is filled in by default.

On the form for this table, I would like to set up the text box so
that it displays as empty if the date is 9/9/9999. However, I can't
figure out on what event to put the changes, and since there's no way
to modify just the displayed value, only the underlying value, using
the default VBA properties, I'm stuck as to what to do.

So far, I've only been able to set the value to blank by using the "On
Current" event.

Ummm...

Why?

Revise the query so that it correctly handles a NULL date, rather than
putting in a non-null phony date. Perhaps you could post the SQL of
the query; you may need to use >= and <= rather than BETWEEN, or use
the NZ() function - but your assertion that the "fired date must be
filled in" is almost certainly not the case.

 

[ascagnel]

Ummm...

Why?

Revise the query so that it correctly handles a NULL date, rather than
putting in a non-null phony date. Perhaps you could post the SQL of
the query; you may need to use >= and <= rather than BETWEEN, or use
the NZ() function - but your assertion that the "fired date must be
filled in" is almost certainly not the case.

--

John W. Vinson/MVP- Hide quoted text -

- Show quoted text -

Thanks to Klatuu and John W., I ended up using an Nz() statement in
the SQL, although my next try was using a CASE, which ended up
breaking the query since its within a BETWEEN statement.

 

[John W. Vinson/MVP]

Thanks to Klatuu and John W., I ended up using an Nz() statement in
the SQL, although my next try was using a CASE, which ended up
breaking the query since its within a BETWEEN statement.

It breaks anyway - CASE is supported in SQL/Server's dialect of SQL
but not in Access/JET syntax.