VB3, VB4, VB5 date functions are buggy

Posted on 1998-03-02
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 list is unending. Can anyone reproduce this behaviour and provide the quick system level solution?

Only fixes accepted as an answer.
Question by:ozphil
  • 8
  • 4
  • 2
  • +1

Author Comment

ID: 1458248
Adjusted points to 100

Author Comment

ID: 1458249
Edited text of question

Author Comment

ID: 1458250
Edited text of question
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

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.

Author Comment

ID: 1458252
Nope. thats not the answer.

Expert Comment

ID: 1458253
try Another : Format(date,"Mmmm dd yyyy")

Author Comment

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.
LVL 39

Expert Comment

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

Author Comment

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 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.


Expert Comment

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!
LVL 39

Accepted Solution

abel earned 100 total points
ID: 1458258

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
'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 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_FLAGS = 1004&

        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

LVL 39

Expert Comment

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

Author Comment

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
=  ( 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.
LVL 39

Expert Comment

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.


Author Comment

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.

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA Shell can't Find Word document 11 130
Sub or Function is not defined 6 54
Error with a code discussed on this page 5 38
Set WorkSheet  not Working 9 51
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

749 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