IsDate() Function

[mk62]

I have a table of contacts that have dates in all different formats. Is there
a way to use the IsDate() function in a query to return only dates of a
certain type? Or can this function make all the dates the same format? I know
I can change the Data Type to Date/Time, but I want to leave it "text".

I was given the following example of IsDate() Function:

Sub CheckDate()
Dim strDate As String

strDate = InputBox("Enter string to display as a date.")
' Test variable.
If IsDate(strDate) Then
' If string is date, format and display in dialog.
MsgBox "The date is: " & Format(DateValue(strDate), "Long Date")
Else
MsgBox "The value you entered is not a date."
End If
End Sub

Can this be modified to get what I need? Please give detailed examples; I am
very new at this.
Also, I put the above Sub into a module, but didn't know how to run it. Does
it go into a module?
Thank You.

 

[Allen Browne]

You are wasting your effort by leaving this as a Text field.

The Text field allows entries that are not valid dates, nulls, zero-length
strings, entries that are valid in some scenarios and not in others (e.g. in
one country), and entries that can be misinterpreted as dates. If you really
want to sort through that mess, you're on your own.

OTOH, if you convert the field into a date/time field, you can select and
sort records quickly and reliably, and prevent invalid dates. Internally,
Access stores the dates as a number, where the whole number represents the
date and the fraction the time of day. The format is therefore used for
display and not for storage. This is the reliable solution.

 

[Tim Ferguson]

I have a table of contacts that have dates in all different formats.
Is there a way to use the IsDate() function in a query to return only
dates of a certain type? Or can this function make all the dates the
same format?

This is untested, but uses a pretty standard method:

Public Function CheckDate(SomeThing as Variant) As String
Const c_strCorrectFormat = "yyyy-mm-dd" ' or whatever
Const c_strReturnFormat = "dd mmmm yyyy" ' or whatever

If IsNull(something) then
CheckDate = "NULL"

ElseIf Not IsDate(Something) Then
' rubbish input
CheckDate = ""

' check it's in the right format by converting it into a
' date and then back again
ElseIf Format(CDate(Something),c_strCorrectFormat) _
= Something Then
' It's okay, so return the desired format
CheckDate = Format(CDate(Something), c_strReturnFormat)

Else
' some kind of valid date but in wrong format
CheckDate = ""

End If

End Function

I know I can change the Data Type to Date/Time, but I
want to leave it "text".

As Allen says, this is a really stoopid idea -- Access can do far more
for you with the thing stored as a proper DateTime value, including the
sorting, calculating, coercing etc. What do you think you'll gain by usig
a text field?

All the best


Tim F