checking date format

[congelator]

Hi everybody,

I have a field in wich users will have to write a date. I would like to check if they use a correct format (dd.mm.yyyy). If it's not the right format, show a msgbox and come back to the active field and have the wrong date highlited.

If anyone hase a suggestion... thanks in advance

Ced
Lausanne / Switzerland

 

[ca8msm]

There are several ways you could validate this:

1) Use the IsDate function
2) Use the Format Function

Do you actually want the user to input the date and if it is in the wrong format inform them? Wouldn't it be easier to change what they have entered into a suitable format that you can use? This way the user can enter a date in any format and you can just accept what they type in.

 

[AndiOliver]

example of format

TransformedData = Format(UserData, "m,d,yyyy")

 

[congelator]

Hi ca8msm

thanks's for your quick response. You had a great idea with changing date automatically, I didn't thought about it. Here is the actual code I wrote :

If Not IsDate(tbox_date_aud) Then
MsgBox "VOUS AVEZ ENTRE UN MAUVAIS FORMAT DE DATE", 48, "ATTENTION !"
Close
End If


What would you suggest me ?

Ced
Lausanne / Switzerland

 

[AndyWatt]

Sub tbox_date_aud_Validate(Cancel As Boolean)
' Format contents of TextBox
' This will convert single digit days/months too
Text1.Text = Format(Text1.Text, "dd.mm.yyyy&quot
' Is this a date?
If Not IsDate(tbox_date_aud) Then
' No - shout at user
MsgBox "Vous avez entre un mauvais format de date!", 48, "Attention !"
Cancel = True
End If
End Sub

Hope this helps


Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"Why does my program keep showing error messages every time something goes wrong?"

 

[congelator]

Thank's AndyWatt,
I paste and adapted your code and... and... It works..!!! Now my code is (if that could be helpfull to someone else...)

Private Sub tbox_date_aud_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim Date_correcte As String
' Vérification si la date existante est déjà correcte
If IsDate(tbox_date_aud.Text) Then
' La date saisie est correcte
Else
' La date saisie n'est pas formatée
If IsNumeric(tbox_date_aud.Text) Then
' La date est au format jjmmaa
If Len(tbox_date_aud.Text) = 6 Then
Date_correcte = Left(tbox_date_aud, 2) & "." & Mid(tbox_date_aud, 3, 2) & ".20" & Right(tbox_date_aud, 2)
End If
' La date est au format jjmmaaaa
If Len(tbox_date_aud.Text) = 8 Then
Date_correcte = Left(tbox_date_aud, 2) & "." & Mid(tbox_date_aud, 3, 2) & "." & Right(tbox_date_aud, 4)
End If
End If
' Vérification que la date formatée est correcte
If IsDate(Date_correcte) Then
tbox_date_aud.Text = Date_correcte
Else
' Dans les cas ou la saisie n'est pas correcte
MsgBox &quot;VOUS N'AVEZ PAS ENTRE LE BON FORMAT DE DATE ! &quot; & Chr(13) & &quot; => &quot; & tbox_date_aud.Text + &quot; <=&quot;, 48, &quot;ATTENTION !&quot;
'MsgBox &quot;La date est incorrecte : &quot; & tbox_date_aud.Text
tbox_date_aud.SetFocus
End If
End If
End Sub

Thank's to everyone !

Ced
Lausanne / Switzerland