checking text date and changing if necessary

[akirekab]

My need is to check an input date, like 01/10/492 and be able to recognize it is not correct date, and then enter a default date in that location. I have read and tried a few things, and am presently trying this

 IIf(DateValue([Field2])=[#error],"01/01/1990",[field2])
 

the idea being if the datevalue function returned error, (it shows #error as return on my query, ) , then I would enter default date, and if not would maintain the same data by using the false argument.

Thank you for any help, I am looking for a user group, cause I want to learn but I am finding the reading very hard.

Ken

[FishVal]

Hi, Ken.

I'm very unsure it is even possible to catch an error within expression.
I would like to recommend you to do it via VBA.

Just write the following simple function in a public code module:

Public Function ConvertDate(varInput As Variant) As Date
 
    On Error GoTo ConvertionFailed
    ConvertDate=......<date function you use>
    Exit Function
ConvertionFailed:
    ConvertDate = #1/1/1990#
 
End Function
 

Then replace the whole IIf() expression in your query with

ConvertDate([The Name of date field you are working with])
 

P.S. Are you still working on that problem ?

[ADezii]

My need is to check an input date, like 01/10/492 and be able to recognize it is not correct date, and then enter a default date in that location. I have read and tried a few things, and am presently trying this

(CODE)
IIf(DateValue([Field2])=[#error],"01/01/1990",[field2])
(/CODE)

the idea being if the datevalue function returned error, (it shows #error as return on my query, ) , then I would enter default date, and if not would maintain the same data by using the false argument.

Thank you for any help, I am looking for a user group, cause I want to learn but I am finding the reading very hard.

Ken

If the Input Value is being entered into a Form Field named txtDate, then:

Private Sub txtDate_AfterUpdate()
If IsNull(Me![txtDate]) Then
  Exit Sub
ElseIf Not IsDate(Me![txtDate]) Then
  Me![txtDate] = #1/1/1990#
Else
  Me![txtDate] = Me![txtDate]
End If
End Sub

[akirekab]

Hi, Ken.

I'm very unsure it is even possible to catch an error within expression.
I would like to recommend you to do it via VBA.

Just write the following simple function in a public code module:

Public Function ConvertDate(varInput As Variant) As Date
 
    On Error GoTo ConvertionFailed
    ConvertDate=......<date function you use>
    Exit Function
ConvertionFailed:
    ConvertDate = #1/1/1990#
 
End Function
 
 
 

Then replace the whole IIf() expression in your query with

ConvertDate([The Name of date field you are working with])
 

P.S. Are you still working on that problem ?

Yes I am still working. I took your advice,and replaced the null and items that evaluatied <0 to get to this last type of input error. Believe it or not, the CDate returns a valid date, for things like 5/5/392. This causes me issues with the person using this data. I will try your VB solution.
My main problem is, I want to convert the table column to Date/Time data type. and when I try it, if there is a mismatch at all, it threatens to remove any row with a bad date. So I am trying to change all the date (in text format) with your help.

[akirekab]

Yes I am still working. I took your advice,and replaced the null and items that evaluatied <0 to get to this last type of input error. Believe it or not, the CDate returns a valid date, for things like 5/5/392. This causes me issues with the person using this data. I will try your VB solution.
My main problem is, I want to convert the table column to Date/Time data type. and when I try it, if there is a mismatch at all, it threatens to remove any row with a bad date. So I am trying to change all the date (in text format) with your help.

Ok Fish, I ran the function, and it ran ok, but it actually changed all dates to the default date, so It thinks all are errors I guess.

I put in CDate(Field2) which is the field containing the dates. Following are dates on the test list.


ID Field2 Field3
1 01/01/1990 00:00 AM
2 7/9/2003 10:15:30 PM
3 7 /9/2003 09:15 AM
4 5/5/192 01:25:15 PM
5 01/01/1990 08:15
6 01/01/1990 00:00 AM
7 8/5/07 00:00 AM
8 10/03/200 07:15
9 03/35/44 08:15
10 3/17/40 00:00 AM

all these ended up with the 1/1/1990 default date. Also if you can address the issue of a an erroneous date like 10/03/200 returning good on a straight CDate conversion.

Thank you for your help.

Ken

[FishVal]

Hi, Ken.

Your code should look like the following:

Public Function ConvertDate(varInput As Variant) As Date
 
    On Error GoTo ConvertionFailed
    ConvertDate = CDate(varInput)
    Exit Function
ConvertionFailed:
    ConvertDate = #1/1/1990#
 
End Function
 

P.S.
Or it may me even more simple (though maybe not so clear):

Public Function ConvertDate(varInput As Variant) As Date
 
    ConvertDate = #1/1/1990#
    On Error Resume Next
    ConvertDate = CDate(varInput)
 
End Function
 

[akirekab]

Hi, Ken.

Your code should look like the following:

Public Function ConvertDate(varInput As Variant) As Date
 
    On Error GoTo ConvertionFailed
    ConvertDate = CDate(varInput)
    Exit Function
ConvertionFailed:
    ConvertDate = #1/1/1990#
 
End Function
 

P.S.
Or it may me even more simple (though maybe not so clear):

Public Function ConvertDate(varInput As Variant) As Date
 
    ConvertDate = #1/1/1990#
    On Error Resume Next
    ConvertDate = CDate(varInput)
 
End Function
 

Hi FishVal, here is what I did.

Orig Data:
ID Field2 Field3
1 01/01/1990 00:00 AM
2 7/9/2003 10:15:30 PM
3 7 /9/2003 09:15 AM
4 5/5/192 01:25:15 PM
5 01/01/1990 08:15
6 01/01/1990 00:00 AM
7 8/5/07 00:00 AM
8 10/03/200 07:15
9 03/35/44 08:15
10 3/17/40 00:00 AM

Here is code in Module:

(CODE)
Public Function ConvertDate(varInput As Variant) As Date
Dim Field2 As String
On Error GoTo ConvertionFailed
ConvertDate = CDate(Field2)
Exit Function
ConvertionFailed:
ConvertDate = #1/1/1990#
End Function
(/CODE)

Results:

ID Field2 Field3 Expr1
1 01/01/1990 00:00 AM 1/1/1990
2 7/9/2003 10:15:30 PM 1/1/1990
3 7 /9/2003 09:15 AM 1/1/1990
4 5/5/192 01:25:15 PM 1/1/1990
5 01/01/1990 08:15 1/1/1990
6 01/01/1990 00:00 AM 1/1/1990
7 8/5/07 00:00 AM 1/1/1990
8 10/03/200 07:15 1/1/1990
9 03/35/44 08:15 1/1/1990
10 3/17/40 00:00 AM 1/1/1990

[FishVal]

Sure, this will not work.
Look at the last variant of code I've posted.

[akirekab]

Sure, this will not work.
Look at the last variant of code I've posted.

Hi Fish,
I am not sure where we are going here. If you mean the new type function you sent. I don't understand it at all.
Ken I thought the first one should have worked. It did , only just a little too well....

[akirekab]

Hi Fish,
I am not sure where we are going here. If you mean the new type function you sent. I don't understand it at all.
Ken I thought the first one should have worked. It did , only just a little too well....

It worked.....yippee
Ken
I chgs the variable back to the variable....varinput...
as you had ...

Results..
ID Field2 Field3 Expr1
1 01/01/1990 00:00 AM 1/1/1990
2 7/9/2003 10:15:30 PM 7/9/2003
3 7 /9/2003 09:15 AM 7/9/2003
4 5/5/192 01:25:15 PM 5/5/192
5 01/01/1990 08:15 1/1/1990
6 01/01/1990 00:00 AM 1/1/1990
7 8/5/07 00:00 AM 8/5/2007
8 10/03/200 07:15 10/3/200
9 03/35/44 08:15 1/1/1990
10 3/17/40 00:00 AM 3/17/1940

This was mainly pointing at case nbr 9 and it worked great...thank you
now if I can get case 4 and 8 it will be over.
Ken

[missinglinq]

To begin with, checking the AfterUpdate status of a textbox control to see if it's Null is a wasted exercise! The AfterUpdate will only fire if a value has been manually entered into the textbox. If the box is entered but a value hasn't been entered, or if a value has been entered and then deleted, the value will always be Null, so checking for Null before proceeding further is, in this particular case, useless.

The standard way of checking to see if data is a date is to use the IsDate() function. What this function actually does is check to see if the data looks like a date! If so, it returns True. Both

IsDate(#12/26/2007#)

and

IsDate("12/26/2007")

return True.

IsDate() is useless, however, to test to see if 01/10/492 is a valid date, for the simple reason that 01/10/492 is a valid date! The year 492 did exist! Used with either IsDate() example given above, 01/10/492 will evaluate as True.

The only way I see to solve this problem is either to use an Input Mask or to check to see if 3 digits have been entered for the year. This code does that, and if 3 digits have been entered for the year, a default date is substituted.

Private Sub txtDate_AfterUpdate()
 If Len(Me.txtDate) - InStrRev(Me.txtDate, "/") = 3 Then
  Me.txtDate = #1/1/1990#
 End If
End Sub

Linq ;0)>

Post script:
The above also explains why the code used in the last post doesn't work in cases #4 and #8.

[akirekab]

To begin with, checking the AfterUpdate status of a textbox control to see if it's Null is a wasted exercise! The AfterUpdate will only fire if a value has been manually entered into the textbox. If the box is entered but a value hasn't been entered, or if a value has been entered and then deleted, the value will always be Null, so checking for Null before proceeding further is, in this particular case, useless.

The standard way of checking to see if data is a date is to use the IsDate() function. What this function actually does is check to see if the data looks like a date! If so, it returns True. Both

IsDate(#12/26/2007#)

and

IsDate("12/26/2007")

return True.

IsDate() is useless, however, to test to see if 01/10/492 is a valid date, for the simple reason that 01/10/492 is a valid date! The year 492 did exist! Used with either IsDate() example given above, 01/10/492 will evaluate as True.

The only way I see to solve this problem is either to use an Input Mask or to check to see if 3 digits have been entered for the year. This code does that, and if 3 digits have been entered for the year, a default date is substituted.

Private Sub txtDate_AfterUpdate()
 If Len(Me.txtDate) - InStrRev(Me.txtDate, "/") = 3 Then
  Me.txtDate = #1/1/1990#
 End If
End Sub

Linq ;0)>

Post script:
The above also explains why the code used in the last post doesn't work in cases #4 and #8.

Thank you very much for the insight. As I am still working on this I will try to understand and use what you have here. Thanks for taking the time. Ken