By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

checking text date and changing if necessary

P: 47
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

Expand|Select|Wrap|Line Numbers
  1.  IIf(DateValue([Field2])=[#error],"01/01/1990",[field2])
  2.  
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
Dec 26 '07 #1
Share this Question
Share on Google+
11 Replies


FishVal
Expert 2.5K+
P: 2,653
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function ConvertDate(varInput As Variant) As Date
  2.  
  3.     On Error GoTo ConvertionFailed
  4.     ConvertDate=......<date function you use>
  5.     Exit Function
  6. ConvertionFailed:
  7.     ConvertDate = #1/1/1990#
  8.  
  9. End Function
  10.  
Then replace the whole IIf() expression in your query with
Expand|Select|Wrap|Line Numbers
  1. ConvertDate([The Name of date field you are working with])
  2.  
P.S. Are you still working on that problem ?
Dec 26 '07 #2

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDate_AfterUpdate()
  2. If IsNull(Me![txtDate]) Then
  3.   Exit Sub
  4. ElseIf Not IsDate(Me![txtDate]) Then
  5.   Me![txtDate] = #1/1/1990#
  6. Else
  7.   Me![txtDate] = Me![txtDate]
  8. End If
  9. End Sub
Dec 26 '07 #3

P: 47
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function ConvertDate(varInput As Variant) As Date
  2.  
  3.     On Error GoTo ConvertionFailed
  4.     ConvertDate=......<date function you use>
  5.     Exit Function
  6. ConvertionFailed:
  7.     ConvertDate = #1/1/1990#
  8.  
  9. End Function
  10.  
  11.  
  12.  
Then replace the whole IIf() expression in your query with
Expand|Select|Wrap|Line Numbers
  1. ConvertDate([The Name of date field you are working with])
  2.  
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.
Dec 26 '07 #4

P: 47
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
Dec 26 '07 #5

FishVal
Expert 2.5K+
P: 2,653
Hi, Ken.

Your code should look like the following:
Expand|Select|Wrap|Line Numbers
  1. Public Function ConvertDate(varInput As Variant) As Date
  2.  
  3.     On Error GoTo ConvertionFailed
  4.     ConvertDate = CDate(varInput)
  5.     Exit Function
  6. ConvertionFailed:
  7.     ConvertDate = #1/1/1990#
  8.  
  9. End Function
  10.  
P.S.
Or it may me even more simple (though maybe not so clear):
Expand|Select|Wrap|Line Numbers
  1. Public Function ConvertDate(varInput As Variant) As Date
  2.  
  3.     ConvertDate = #1/1/1990#
  4.     On Error Resume Next
  5.     ConvertDate = CDate(varInput)
  6.  
  7. End Function
  8.  
Dec 26 '07 #6

P: 47
Hi, Ken.

Your code should look like the following:
Expand|Select|Wrap|Line Numbers
  1. Public Function ConvertDate(varInput As Variant) As Date
  2.  
  3.     On Error GoTo ConvertionFailed
  4.     ConvertDate = CDate(varInput)
  5.     Exit Function
  6. ConvertionFailed:
  7.     ConvertDate = #1/1/1990#
  8.  
  9. End Function
  10.  
P.S.
Or it may me even more simple (though maybe not so clear):
Expand|Select|Wrap|Line Numbers
  1. Public Function ConvertDate(varInput As Variant) As Date
  2.  
  3.     ConvertDate = #1/1/1990#
  4.     On Error Resume Next
  5.     ConvertDate = CDate(varInput)
  6.  
  7. End Function
  8.  
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
Dec 26 '07 #7

FishVal
Expert 2.5K+
P: 2,653
Sure, this will not work.
Look at the last variant of code I've posted.
Dec 26 '07 #8

P: 47
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....
Dec 26 '07 #9

P: 47
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
Dec 26 '07 #10

missinglinq
Expert 2.5K+
P: 3,532
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDate_AfterUpdate()
  2.  If Len(Me.txtDate) - InStrRev(Me.txtDate, "/") = 3 Then
  3.   Me.txtDate = #1/1/1990#
  4.  End If
  5. 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.
Dec 27 '07 #11

P: 47
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDate_AfterUpdate()
  2.  If Len(Me.txtDate) - InStrRev(Me.txtDate, "/") = 3 Then
  3.   Me.txtDate = #1/1/1990#
  4.  End If
  5. 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
Dec 28 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.