Variable Field Type?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I've got a series of date values that I need to capture within a database

E.g.,
DtSen
DtReceive
DtApproved

I'd like to be able to allow users to enter either a date value or "n/a" if a date value is inappropriate for a particular case. Obviously, a field with data type "Date/Time" will not accept a value of "n/a". Conversely, a text field will not recognize dates

It seems (to me, at least) reasonable enough to want to design a database this way, but is there a practical way around the above problem? Essentially, I'm trying to alleviate the ambiguity of blank fields (which could either mean an incomplete entry, or non-applicability in a particular case).

I'm completely stumped at this point, so any direction at all would be greatly appreciated

Demi
 
You can use this piece of code to check and convert a date in a text field
and ignore "n/a":

Public Function MakeDate(strIn As String)
On Error Resume Next

If Not IsDate(strIn) Then
Exit Function
Else
MakeDate = CDate(strIn)
End If

End Function

Now just use it as a wrapper in your calculations around the "date" field.
You will need to write another function to handle the error.

?DateDiff("d", MakeDate("n/a"), Date)
37943

You could use it like this:

Public Function MakeDate(strIn As String)
On Error Resume Next

If Not IsDate(strIn) Then
MakeDate = Null
Exit Function
Else
MakeDate = CDate(strIn)
End If

End Function

And your return would then be Null:

?DateDiff("d", MakeDate("n/a"), Date)
Null
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Matt said:
Hi,

I've got a series of date values that I need to capture within a database:

E.g.,
DtSent
DtReceived
DtApproved

I'd like to be able to allow users to enter either a date value or "n/a"
Click to expand...
if a date value is inappropriate for a particular case. Obviously, a field
with data type "Date/Time" will not accept a value of "n/a". Conversely, a
text field will not recognize dates.
It seems (to me, at least) reasonable enough to want to design a database
Click to expand...
this way, but is there a practical way around the above problem?
Essentially, I'm trying to alleviate the ambiguity of blank fields (which
could either mean an incomplete entry, or non-applicability in a particular
case).
 
Back
Top