IsDate function - Urgent 1

[VBdevil]

Hi,

I have a text box into which the user enters a date (in the dd/mm/yy format), and I check the input with the IsDate function. My problem is that when the user enters the date: 10.4.99 the IsDate function returns false, and when the user enters 10-4-99 the IsDate function returns true. Also when the user enters 10.5.03, for example, the IsDate function returns true. What's the deal with that? I need to solve this in the next few hours, so I would appreciate quick responses...

Thanks!

 

[CCLINT]

What's the short date format in the country settings in the Control panel set at?

 

[VBdevil]

What I want to know is if there is a certain format in which the short date should be entered (dd.mm.yy or dd-mm-yy or dd/mm/yy)? I don't want to rely on the computer settings cause the program might be used on different computers, and I want to have instructions on how the user should enter a date, so the application accepts it.
Maybe there is another way I can check the validity of a date. Does anyone know?

 

[CCLINT]

Deleope your program to adjust to the computer's date and number settings, no matter what they are, no matter in what country, and not forcing the user to enter in a set format.

Otherwise, you, and not the user, will have problems working with the dates, and will need to manually change each input to the setting in the system before you wan use date funtions (such as IsDate, or even using Date variables at all), and then manually format the date back for the user to see.

If a user is in a country where 10.4.99 is the format (the dot being the date seperator) you are going to have a hard time explaining to them why they need to use a "/", or even have to set their system as such.

Imaging someone giving you a program and telling you that you must use a comma for a decimal seperator...

 

[Hypetia]

You can also convert the dots in a date string to slashes before assigning the value to a date variable.
___[tt]
Dim S As String, D As Date
S = "10.4.99"
Debug.Print IsDate(S) 'False
S = Replace$(S, ".", "/&quot 'Replace "." with "/"
Debug.Print IsDate(S) 'True
D = S
[/tt]

 

[VBdevil]

Is there a certain format for the IsDate function (slashes, commas, dots etc), or is it just according to the computer settings?

 

[CCLINT]

>or is it just according to the computer settings

A hyphen (-) should always work, as well as a slash. A period, or whatever, will only work if the system is set as such.

But the format, with respect to the date parts, needs to be as in the system settings.

Replacing the date seperator will work as far as the date seprator is concerened, but not as far as the date format is concerned.
You can force a certain format like this:

Format$(Date,"dd\/mm\/yyyy&quot

but it will not work correctly with the VB functions which need a date entered in the system format.

 

[VBdevil]

Are you 200% sure that a slash or a hyphen will always work?

 

[jshanoo]

Try DTPicker control.
Is will solve the problem,
In this you ab define teh date format also
John

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[CCLINT]

If you have doubt, then test it...use different settings in the country settings date dialog and try some different formats and date functions in your immediate window...

A comma should work also.

These will convert to the local date seperator. But, into the local format as well.

So if the system is set as DD.MM.YYYY the user can enter as DD/MM/YYYY, but not as MM/DD/YYYY, and you can still expect the date functions to work (unless you first convert them as a string, or use the DateSerial function, back and forth).

If the system is set as MM/DD/YYYY the user can input as DD/MM/YYYY or MM.DD.YYYY, but you will need to use the DateSerial to format the date correctly before using it with any date functions or variables.

I don't understand what you are trying to get at.
The user enters the date in the format as the system is set at with respect to the date parts, and users the same date seperators, or, if desired, a hyphen, comma, or slash, you stick them into the date variables or functions and compute with them, not as a literal date, but as a date variable, unless you are passing the date in a SQL function in which case you need to format the date to US format, and then pass the results back to the user.

You can use the DTPicker as jshanoo suggests, even though this is nothing to use for speed data entry programs, or the MaskEdit control, or create four own validation.
This is not the problem which you first presented.
The problem is a recognized date by a date function.

Your problem is that the IsDate function recognizes 10.5.03 as a date. Well, that is correct. But, because you have looked at it wrong. It is actually recognizing it as a TIME and not a date.
Because date and time is actually stored as a number, it sees 10.05.03 as a decimal number and computs it as:
10.5 + 0.03
This, is the number equivalent of the time: 10:05:03
To see this use: Debug.Print CDate("10.5.03&quot

I think I'm done here now.

 

[CCLINT]

>This, is the number equivalent of the time: 10:05:03

That was incorrect. It sees the dot as also being a time seperator. But the results are the same.

(I got too involved)

 

[tsh73]

CCLINT
>Imaging someone giving you a program and telling you that you must use a comma for a decimal seperator...

That's EXACTLY the thing Microsoft did at Russia!
So Excel wants comma...
And all VB conversion routines need comma...
But VB (and all other languages, I guess) uses a dot!

Can you imagine that?

 

[VBdevil]

My problem is very simple - I just want to tell the user which separator to use when entering dates into a text box. As I understand the dot separator can cause problems, but the hyphen and slash separators should allways work. Right?
Thanks for everyone's help!

 

[CCLINT]

tsh73: And this had nothing to do with the settings for the Date in the Country Settings?

>Imagine someone giving you a program and telling you that you must use a comma for a decimal seperator...

That is, if your system is set to something like US format, or whatever. Just to clear up what I meant.

VBdevil: The dot seperator will cause problems, of course, if the system is set with another seperator, but not if this is the seperator set in the country settings.

What I do, is query the system as to what seperator is used and then base the user's input on that, using a variable instead of a literal when checking the format in the KeyPress, Change or Validation event of an edit control. Then you do not have to worry about it.

The hyphen and slash should always work.

 

[TomSwingruber]

there are holes in isdate
this is what we wrap around isdate
with slight mods this can cure your seperator problem
just add a step that feeds isdate '/'s
Public Function isAWdate(date_tocheck As String) As Boolean

Dim rv As Boolean


rv = False

If Left(Right(date_tocheck, 2), 1) = "/" Or _
Left(Right(date_tocheck, 3), 1) = "/" Or _
Left(Right(date_tocheck, 4), 1) = "/" _
Then GoTo isAWdate_xit

If IsDate(date_tocheck) = False Or _
Val(Left(date_tocheck, 2)) > 12 _
Then GoTo isAWdate_xit

rv = True

isAWdate_xit:
isAWdate = rv

End Function
tom

 

[kel1981b]

CCLINT is absolutely right "/" always works. May be it's too late by try to use this

Text1 = Format(Replace(Text1, ".", "/"), "dd/mm/yy")

 

[CCLINT]

TomSwingruber and kel1981b: Look at my previous post (4 posts ago) and you would see that you could just do this:

Format$(Text1.Text,"dd\/mm\/yyyy&quot

TomSwingruber - You do not know what order the date parts are in, or even if they are 2/4 digit, and not 1/2 digit.
This may help you to solve this easier.
The function you made is strictly a function that may work on a system set in US format.
But, maybe this is all that is needed for you application.

The IsDate function will work properly if used properly.

kel1981b - Using this will reduce the needed code down to one function, removing the Replace function.
Hope this helps.

VBdevil: I must admit that I didn't understand where you were coming from in the initial question, and had thought that I did.
I had assumed that the problem was that the user was using a different date format in the country settings and you were having problems with that.

Now I see that your problem is when the date format is set to a date seperator other than a dot (such as a slash), the user may just enter a date using a dot, and it may then be interpeted as time, causing the IsDate to return true (10.5.03), and this is not what you are looking for.

I should have set my system to US format and then I would have been reminded of this.

There, yes, you will need to limit the input of a date part seperator to a slash or hyphen.
There are plenty of examples in this forum for allowing the input to be limited only to certain characters, such as using InStr() in the KeyDown/KeyPress event of an edit control, or using an exact pattern verification (with-out the use of the DatePicker of MaskEditBox).

You can still check if the input is date and not time by using:

========================================================
DATE ONLY
Checks the date to see if it is a valid date and not just time:

Dim bIsDateOnly As Boolean
bIsDateOnly = IsDate(TheDate) And DateValue(TheDate) > 0

Returns only the Date part with-out the time, if a valid date, as a string

Dim bIsDateOnly As Boolean
bIsDateOnly= = IsDate(TheDate) And DateValue(TheDate)>0
If bIsDateOnlyThen
Return = DateValue(TheDate) 'Or Int(CDate(TheDate))
Else
Return = vbNullString
End If

========================================================
TIME ONLY
Checks the Time to see if it is a valid Time and not just Date:

Dim bIsTimeOnly As Boolean
bIsTimeOnly = IsDate(TheTime) And TimeValue(TheTime) > 0

Returns only the Time part:

Dim bIsTimeOnly As Boolean
bIsTimeOnly = IsDate(TheTime) And TimeValue(TheTime)>0
If bIsTimeOnly Then
Return = TimeValue(TheTime)
Else
Return = vbNullString
End If

 

[kel1981b]

CCLINT I did read your previous post. Did you try your code? It(

 Format$(Text1.Text, "dd\/mm\/yyyy")

) doesn't work. Try

Text1 = Format$(Text1.Text, "dd\/mm\/yyyy")
If IsDate(Text1) Then 
   MsgBox "OK"
Else
   msagbox "Wrong Date"
End if

If Text1 value will inclide dots like 12.31.2003 (as I remember the question was about dots),I guarantee, you will get message "Wrong Date"

 

[CCLINT]

That's because it is a wrong date....
IsDate("12.31.2003&quot is neither a date nor a time.

So why are you allowing dots to be entered if the country setting for the date seperator is a slash? Read my last posts closer.

You need to verify it with the IsDate function, and your edit box should only accept a seperator supported by your system.

If you fulfill the criteria correctly, it will work.
Criteria being:
Is the entry in the correct format using the system settings date part order (MM/DD/yyyy or M/D/YY vs.DD/MM/YYYY or D/M/YY, etc) and are you allowing the user to enter only a slash or hyphen, if you are only interested in a date and not a time.

So, if the user can enter a slash or hyphen, you can format it using the format function I presented to return the date using slashes.

And this is my point:
1. Query the system to find out the date seperator used.

2. User enters the date.

The date needs to be entered in the datepart order as the country settings for dates.
You code the edit control to allow only the seperator mention above to be used (optionally maybe a hyphen).
Optionally, you can make sure that the exact format desired is adhered to prior to checking if it is a valid date (see the LIKE function).
This is done in the KeyDown/Keypress and Change events.

3. You verify the date using the IsDate function in the Validation event.

4. You want to (optionally) make sure the date is presented in a certain format, then use my mentioned format function.

If a system set with a slash as a date divider, the edit control accepts a slash or possibly a hyphen only. You do not need to worry about the dots.

On a German system, the date seperator is a dot and the format is dd.mm.yyyy
If you stick to the outline above, your program will be very happy to accept this format, and your programmers, and clients, will be happy campers.

This will work in any country using any date settings in the control panel, everytime (as long as MS date logic and functions remains consistant, and bug free).

Your function, will not work on systems with the date format set different.