Solved

VB3, VB4, VB5 date functions are buggy

Posted on 1998-03-02
15
443 Views
Last Modified: 2008-02-01
Try isdate("4 5") in VB3. It correctly returns false
Try isdate("4 5") in VB4 or VB5. It incorrectly returns true.

Try isdate("2/13/98") in Australia where it should return false. VB3 returns false, VB4/5 incorrectly return true instead.
Try isdate("13/2/98") in Australia where it should return false. It returns true instead.

Try format("2/13/98","dd/mm/yyyy") in Australia. VB3 incorrectly accepts it and returns 2/13/1998. VB4/5 incorrectly return 13/2/1998.
Try format("13/2/98","dd/mm/yyyy") in Australia. Result is correctly 13/2/1998.

THE OPERATING SYSTEM COUNTRY SETTING IS ENGLISH(AUSTRALIA).

The list is unending. Can anyone reproduce this behaviour and provide the quick system level solution?

Only fixes accepted as an answer.
0
Comment
Question by:ozphil
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 4

Author Comment

by:ozphil
ID: 1458248
Adjusted points to 100
0
 
LVL 4

Author Comment

by:ozphil
ID: 1458249
Edited text of question
0
 
LVL 4

Author Comment

by:ozphil
ID: 1458250
Edited text of question
0
 
LVL 2

Expert Comment

by:Veroland
ID: 1458251
Have you tried using something like format(variable,"dd/mmmm/yyyy")
Using the Month name instead of the number.
This usually works for me but there is still errors. Like if your regional settings is Afrikaans this doesn't work.
There is API calls to change the regional settings but its very confusing and tuf to use.
0
 
LVL 4

Author Comment

by:ozphil
ID: 1458252
Nope. thats not the answer.
0
 
LVL 2

Expert Comment

by:Veroland
ID: 1458253
try Another : Format(date,"Mmmm dd yyyy")
0
 
LVL 4

Author Comment

by:ozphil
ID: 1458254
the format HAS to be dd/mm/yyyy.

What i would like is the undocumented bug info anyone may have. I have written many date/time routines. So the question is put because i havent been able to crack this one.

If you provide an answer/comment could you please indicate if youve tried my statements and the result youve obtained.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

Expert Comment

by:abel
ID: 1458255
The problem with the IsDate and the CDate functions is that it returns True or a valid date if "the expression is a date or can be converted to a valid date" (from the helpfile). Further investigation tells me that the functions find something convertable when it's in the format of the international standard for date-notation or when it's in a format supported by your locale settings. That can be a problem when you want to make sure that a date can only be entered in only one format. In my humble opinion the only way is to do a check on the string by yourself and return FALSE if the date is not passed in the correct format. When it IS in the correct format you can easily use CDate and Format and you will get the correct results according to your locale settings.

If you want such a routine, I have one ready, so I can post it as an answer to you.

For illustration, and since you asked, here is a list of dates validated with IsDate (my locale is dd/mm/yyyy):

The value is everytime passed as string to IsDate.

 Date: 15 4 is:   TRUE
 Date: 15/4 is:   TRUE
 Date: 15-4 is:   TRUE
 Date: 4 15 is:   TRUE
 Date: 4/15 is:   TRUE
 Date: 4-15 is:   TRUE
 Date: 15/4/96 is:  TRUE
 Date: 4/15/96 is:  TRUE
 Date: 96/4/15 is:  TRUE
 Date: 15-4-96 is:  TRUE
 Date: 4-15-96 is:  TRUE
 Date: 96-4-15 is:  TRUE
 Date: 15 4 96 is:  TRUE
 Date: 4 15 96 is:  TRUE
 Date: 96 4 15 is:  TRUE
 Date: 1504 is:   FALSE
 Date: 15\4 is:   FALSE
 Date: 0415 is:   FALSE
 Date: 4\15 is:   FALSE
 Date: 150496 is:   FALSE
 Date: 041596 is:   FALSE
 Date: 961504 is:   FALSE
 Date: 960415 is:   FALSE
 Date: 04/96/15 is:   FALSE
 Date: 15/96/04 is:   FALSE
 Date: 96/15/4 is:  FALSE
 Date: 04-96-15 is:   FALSE
 Date: 15-96-04 is:   FALSE
 Date: 96-15-4 is:  FALSE
 Date: 04 96 15 is:   FALSE
 Date: 15 96 04 is:   FALSE
 Date: 96 15 4 is:  FALSE


Regards, Abel
0
 
LVL 4

Author Comment

by:ozphil
ID: 1458256
Abel. in VB3 a lot of those are correctly false. But in VB4/% theyv become true.

What about this:
1 45 is true!!
60 2 is true!!

I know i have to write a customised parsing routine - ive already done a very comprehensive date entry routine. But because of bugs in isdate() and format() functions, the routines will become inflexible and unwieldy.

I'll forward this URL http://www.experts-exchange.com/Q.10041221 to Microsoft if i get no answer on a way to get their functions working correctly. If you have info from Microsoft that the fucntions are buggy then ill award points.




0
 
LVL 2

Expert Comment

by:chris_a
ID: 1458257
I wonder if they work if you are in the USA, for my part in the UK this is a never ending problem, and once you crack the UI, just try working out what date ends up in a database!
0
 
LVL 39

Accepted Solution

by:
abel earned 100 total points
ID: 1458258
Ozphil,

We aren't talking about bugs here, but about features. If Microsoft says: "if the expression is a date or can be converted to a valid date then IsDate returns TRUE" then they give that function a lot of 'features'. For Microsoft it's easy. When you say 25/3 or 3/25 then that means the 25th day of the third month of the current (system's) year. When you say 31/4 or 30/4 Microsoft thinks you mean in the first case the first day of the fourth month of 1931 (since April has no 31 days) and in the second case is means the 30th day of the fourth month of the current (system's) year. You probably wonder WHY VB/Microsoft does so, and the answer is easy: Win95 has a possibility of storing a date like MM/YY or MM/YYYY. IsDate and CDate must have the possibility to convert dates given in that format, so they do. Of course, it would be nice if you could give IsDate and CDate some rules, but unfortunately there's no possibility in doing so.

A small list for explanation and you got the picture (remember, we're talking about FEATURES here, like Microsoft has lots of 'FEATURES' which we never asked for...):

1/1 = 01-jan-1998
1/32 = 01-jan-1932
32/32 = ERROR
65/1 = 01-jan-1965
30/1/100 = 30-jan-100
100/12 = 01-dec-100


You see? If you use enough fantasy (like Microsoft does) almost anything can be a date. So the final conclusion is easy: don't use that function, it does not perform a useful check (although it DOES perform a check).
You probably were already aware of this, so for your convenience I wrote a function which works around this problem by using API's.

I assume in this function that the correct format you want to get TRUE for is dd/mm/yy with a valid day, month and year. I also assume the year is between 1900 and 1999. The function can be easily converted to a more specific check.
I use the GetDateFormat API, which does in a better way and in a single call what IsDate and CDate do.

If I can be of any more help to you, let me know. I think the following function is quite easy to read and implement, but if you need more explanation, I'll be happy to post that to you.

Regards, Abel

Here it goes:

'iIsDate validates and formats a date correctly
'Parameters:
'DateStr    Date to be validated and formatted in the format dd/mm/yy
'
'Return values
'FUNCTION   Returns TRUE if date is correct, FALSE otherwise
'DateStr         Formatted date if function is TRUE
'
Function iIsDate(ByRef DateStr As String) As Boolean
  Dim Ret As Integer
  Dim sDate As SYSTEMTIME
  Dim tDateStr As String
  Dim pos As Integer
 
  'Only accept good lengths
  'We assume the date is between 1/1/1 and 31/12/99
  If Len(DateStr) > 8 Then iIsDate = False: Exit Function
  If Len(DateStr) < 5 Then iIsDate = False: Exit Function
 
  'Get the date in sDate
  With sDate
    pos = InStr(1, DateStr, "/")
    If pos = 0 Then iIsDate = False: Exit Function
    .wDay = Val(Left(DateStr, pos - 1))
    .wMonth = Val(Mid(DateStr, pos + 1, 2))
    pos = InStr(pos + 1, DateStr, "/")
    If pos = 0 Then iIsDate = False: Exit Function
    .wYear = Val(Mid(DateStr, pos + 1, 2)) + 1900
  End With
   
  'Increase/decrease this when you want another return format
  tDateStr = Space(10)  
  Ret = GetDateFormat(GetUserDefaultLCID, 0, sDate, _
        "dd'/'MM'/'yyyy", tDateStr, LenB(tDateStr))
  If Ret = 0 Then
    'Check here on Err.LastDLLError if you like and
    'use the constants in the declaration section.
    'INVALID_PARAMETER means wrong date.
    iIsDate = False
    Exit Function
  End If
  DateStr = tDateStr
  iIsDate = True
End Function


'Put the following code in the declaration section:

Public Const ERROR_INVALID_PARAMETER = 87
Public Const ERROR_INVALID_FLAGS = 1004&
Public Const ERROR_INSUFFICIENT_BUFFER = 122

Type SYSTEMTIME
        wYear As Integer
        wMonth As Integer
        wDayOfWeek As Integer
        wDay As Integer
        wHour As Integer
        wMinute As Integer
        wSecond As Integer
        wMilliseconds As Integer
End Type
Declare Function GetDateFormat Lib "kernel32" Alias _  "GetDateFormatA" (ByVal Locale As Long, ByVal dwFlags As Long, _  lpDate As SYSTEMTIME, ByVal lpFormat As String, _
 ByVal lpDateStr As String, ByVal cchDate As Long) As Long

Declare Function GetSystemDefaultLCID Lib "kernel32" () As Long
Declare Function GetUserDefaultLCID Lib "kernel32" () As Long

0
 
LVL 39

Expert Comment

by:abel
ID: 1458259
Sorry, the underscores in Declare Function GetDateFormat are not correct. It should be:

Declare Function GetDateFormat Lib "kernel32" Alias _
  "GetDateFormatA" (ByVal Locale As Long, ByVal dwFlags As Long, _
   lpDate As SYSTEMTIME, ByVal lpFormat As String, _
   ByVal lpDateStr As String, ByVal cchDate As Long) As Long
0
 
LVL 4

Author Comment

by:ozphil
ID: 1458260
You may feel that the bugs are features, but i believe they are bugs. If there is no official documentation to explain these 'features' then I can only assume that they are bugs MS has withheld and waiting for our submission to them as problems for them to fix.

The user in my application has to be able to enter a date in any valid format that the client or business or end user would expect, including for example:
13 4
13 4 98
13 4 2000
13.4
13/4
=  ( i translate this to current date)

When they tab out of date entry box their entry is converted to dd/mm/yyyy.

They will not accept:
13 4
45 1
and i am not going to make an idiot of myself on Microsoft's behalf by explaining these as features. They will not accept it, even if we did.

If the isdate() and format() functions worked properly there would be no problem.

I bet the reason they dont work is that Microsoft found it too hard and complex a programming exercise, which it is, hoping we would accept the bugs as features.

Well we dont accept these functions as they are. So i would expect a statement from them explaining in full detail the original input/ouptut specs for these functions. And then a promise of a fix.

Thanks for your efforts in the workaround API, but i want to give the users a lot of flexibility.

I can provide a lot of extra parsing myself, but the point is that that should have been done in the isdate() function itself.
0
 
LVL 39

Expert Comment

by:abel
ID: 1458261
I feel the same about the "features", I only mentioned it that way since Microsoft usually sees such things as features. I think it's foolish to program a function that returns TRUE on anything that MIGHT be a date somehow. If you do make such a function, you should implement a possibility for the user for how exact you want the test. And Microsoft doesn't.

Anyway, you can use the above function to make quite a suitable and flexible way of testing for correct date values. Making it suitable for other separators for example, or giving it the possibility to differentiate between two-digit and four-digit years (the year 2000-problem).

Unfortunately, I can't provide an answer from Microsoft about the IsDate function. When you look at the helpfile, you see that the function does what the explanation says, although you (and I) do not agree with that. If you think about a lot of flexibility for your users, try the MaskedEdit control, which has quite a lot of possibilities in making entering a date very easy and with much less errors. It was a great help to me.

Regards
0
 
LVL 4

Author Comment

by:ozphil
ID: 1458262
The API solution is not quite what i wanted, but i suppose with a heap of parsing code it will provide a workaround.

Thanks Abel.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now