Comparing Dates

[jayklmno]

I am trying to compare two fields... one is a textbox and the other is a cell
range.

textbox : tbDateOOCreated.Value
range : Range("Db_Updated").Value

I am loading them into date variables and making a comparison in an if
statement

CreateDt = tbDateOOCreated.Value
UpdateDt = Range("Db_Updated").Value

If CreateDt < UpdateDt Then
:: Code ::
end if

The problem I am having is, if they are dates, and the textbox value is
empty, I get a type mismatch error.

if I use a function to fix the value...

CreateDt = FixDate(tbDateOOCreated.Value)
UpdateDt = FixDate(Range("Db_Updated").Value)

Function...
Public Function FixDate(DumbDate As Variant) As Variant

Dim dAns As Variant

If Not IsDate(DumbDate) Then
dAns = Null
ElseIf CStr(DumbDate) = "12:00:00 AM" Then
dAns = Null
Else
dAns = CDate(DumbDate)
End If

FixDate = dAns

End Function


I tried using a Function to fix the date and load a Null, but I get an
invalid use of null.

How can I compare these two fields as dates?

Thanks for the help!

 

[OssieMac]

I feel that you probably should be heading down a different track.

A text box is exactly that. It holds text. Therefore the entries in the
textbox are text even if entered in a date type format. If you want to
validate the dates in a text box then you need an extensive validation
process that incorporates the following minimum criteria:-
Looks for the delimiters (usually slashes)
Looks for valid entries in each of the 3 sections of the date.
Test for the max number of days for particular month.
Then you might want to test for dates that fall within a particular period
or before or after another previously entered date.

DateValue is usually used to convert the date from text to date. Using
DateValue before validating the initial entry does not work because it can
create a valid date from an entry that is invalid for the purpose. For
example if you enter 32/5/09 instead of 31/5/09 then it returns 9 May 1932.

I usually find it easier to use a ComboBox and provide a date list for the
user to pick from. Unfortunately if the ComboBox is on a worksheet then the
MatchRequired property does not work and you still need to test if the date
is in the valid list using Vlookup in conjunction with On Error in the
LostFocus event.

If using a ComboBox and date list then the date list can be made dynamic to
meet certain criteria depending on the spec for the project. Example: as
specific number of days before and after today or maybe after another date
entry. It is important to note that the date list for the ComboBox must be
Text. If using a list formatted as a date then the TextBox will display the
serial number of the date. However, all this can be handled.

Perhaps you can think about what will work for you and if you need more help
in achieving it then post some info on your preferred method (TextBox or
ComboBox) together with some criteria for the validation process.

 

[OssieMac]

I forgot to add before that selecting More Controls in the Control ToolBox
and selecting the Calendar Control and using that to enter dates should be
considered as an option.