Solved

Date Verifier in VB

Posted on 2001-08-14
12
411 Views
Last Modified: 2010-08-05
In SQL there is a command ISDATE(YYMMDD) which returns 1 if the parameter is a date and 0 if it is not a valid date.  

I am looking for a built in Visual Basic function which will allow me to pass in a 6 character field and return either true or false depending on whether or not the string is a valid date.

Here are a few examples
"770705"  => True, This is July 5, 1977
"840010"  => False, there is no Month
"000101"  => True, This is January 1, 2000
"01B0pH"  => False, This is garbage data


If any one can tell me the VB equivalant to the SQL isdate command, I will reward them the points.  It is that simple...
Thanks


0
Comment
Question by:ifgtest001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 6384039
There is an isdate function in VB. However it does require that there is some seperator in the expression to be evaluated so:

IsDate("770705") = False
IsDate("77-07-05") = True

But all is not lost, you can easily convert the 6 character string into an acceptable date format using:

IsDate(format("770705","yy-mm-dd")) = True
0
 
LVL 6

Expert Comment

by:VK
ID: 6384054
try that:

exaple

isdate(format("980505","yy/dd/mm")) = true !
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6384090
Beat you VK! It doesn't actually matter what the seperator is as long as it is a valid date seperator somewhere.
0
Industry Leaders: 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!

 
LVL 22

Expert Comment

by:ture
ID: 6384447
Is this really correct??? I'm trying this in Excel VBA, but it really doesn't work the way you imply.

Please correct me if I'm wrong...

Try this in the immediate window:
?format("980505","yyyy/dd/mm")

It really doesn't show us the date 1998-05-05!
It shows us the date that is 980505 days from January 1 1900 - which is 4584-11-07.

To verify that "980505" is a date, you must insert separators and check if it's a date. This will work for a 6-digit YYMMDD date:

function isitadate(d as string) as boolean
  isitadate = isdate(mid(d,1,2) & "-" & mid(d,3,2) & "-" & mid(d,5,2))
end function

Ture Magnusson
Karlstad, Sweden
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6384517
>In SQL there is a command ISDATE(YYMMDD) which returns 1
>if the parameter is a date and 0 if it is not a valid date.

You might want to clarify which SQL environment you are speaking. The syntax, keywords, and functions differ from one DBMS to another. You don't do things exactly the same way in MS Access SQL as you do in MS SQL Server, or in Oracle SQL.

Ture is correct in the fact that the first couple of suggestions would return a date, but not the one you expected.

-Dennis Borg
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6384547
I didn't actually test that the date determined was what it should have been, thanks ture for the extra mile.
0
 
LVL 2

Expert Comment

by:AndrewK
ID: 6385016
... or the extra instruction: FormatDateTime(mydate, date_format) where date_format would be vbShortDate in the above examples.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6385326
Adding to previous comments, Try this:

Private Function CheckIfDate(MyDate As String)
Dim sDate As String

MyDate = "770705"

sDate = Left(MyDate,2) & "-" & Mid(MyDate,3,2) & "-" & Right(MyDate, 2)

CheckIfDate = IsDate(sDate)
End Sub

'Hope this helps.
0
 
LVL 15

Expert Comment

by:ameba
ID: 6385626
? IsDate(Format("981005", "@@/@@/@@"))
True

' insert  /  characters
?Format("981005", "@@\/@@\/@@")
98/10/05

' insert date separators
?Format("981005", "@@/@@/@@")
98/10/05
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6385641
ifgtest001:

Here is an example illustrating how you can perform your check:


   IsDate(Format("770705","00/00/00"))

This will return either true or false

The only "thing" would be if you had dates such as the following:

   "010203"

Which should it be:

    Jan 02, 2003
    Feb 01, 2003
    Feb 03, 2001
    etc.

It is a little ambiguous, but doesn't change the fact of whether or not it is a valid date.


-Dennis Borg
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7199948
Hi ifgtest001,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Save as PAQ -- No Refund.
    *** Too many excellent answers; too few points to split

ifgtest001, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7213439
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

707 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