Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Dates
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Dates
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. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Dates
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. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing dates | Excel Worksheet Functions | |||
Comparing dates | Excel Worksheet Functions | |||
Comparing 2 Dates | Excel Worksheet Functions | |||
Comparing dates | Excel Programming | |||
Comparing dates | Excel Worksheet Functions |