Hi,
You can't accomplish that by having the expression in the text box
control source.
You would have to move the code to the Current event of the form.
From the form's property sheet click on the elipsis (...) beside the Current event.
This will bring you into the VB editor.
Place this code there (replace "YourTextBoxName" with the actual name of your control)
If Me.NewRecord = True Then
Me.YourTextBoxName = Switch(([DisclosureExpiration]<Date()) Or _
([ConfidentialityExpiration]<Date()),"Expired", _
([DisclosureExpiration] Is Null) And _
([ConfidentialityExpiration] Is _
Null),"",True,"Active")
Else
Me.YourTextBoxName = Switch(([DisclosureExpiration]<Date()) Or _
([ConfidentialityExpiration]<Date()),"Expired", _
([DisclosureExpiration] Is Null) And _
([ConfidentialityExpiration] Is _
Null),"Unknown",True,"Active")
End If
I haven't tested the above so let me know if you have problems with it.
--
HTH
Dan Artuso, Access MVP
ND said:
Thanks, but if I do that then on a valid record containing
no values in [DisclosureExpiration] &
[ConfidentialExpiration]it will also be blank. I only want
it to be blank when it's the new(empty) record line.
-----Original Message-----
Hi,
I would imagine that you could just delete the word Unknown:
=Switch(([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),"Expired",
([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is
Null),"",True,"Active")
HTH
Dan Artuso, MVP
"ND" <
[email protected]> wrote in
message news:
[email protected]...
Someone in this newsgroup helped with the following code
and it works great. The only problem is that on a new
record line it displays Unknown when I would rather it
just be blank. How can I accomplish that? thanks for the
help.
=Switch(([DisclosureExpiration]<Date()) Or
([ConfidentialityExpiration]<Date()),"Expired",
([DisclosureExpiration] Is Null) And
([ConfidentialityExpiration] Is
Null),"Unknown",True,"Active")
.
