HI mangesh_yadav

After extensive testing I seem to have a problem with validation to
ensure the dates are correct as it should be

Textbox1<textbox2<textbox3

however if the users puts the 31/05/05 and then in textbox2 puts in
01/06/05 is says that the dates are not in order as I think it is
picking up the 31 and the 01 and is not likeing it any ideas?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=368692

Hi funkymonk,

Assuming you are using this code:

dt1 = DateValue(Mid(TextBox1, 4, 2) & "/" & Left(TextBox1, 2) & "/" &
Right(TextBox1, 4))
dt2 = DateValue(Mid(TextBox2, 4, 2) & "/" & Left(TextBox2, 2) & "/" &
Right(TextBox2, 4))


Textbox1<textbox2<textbox3

however if the users puts the 31/05/05 and then in textbox2 puts in
01/06/05 is says that the dates are not in order as I think it is
picking up the 31 and the 01 and is not likeing it any ideas?


I tried the folloing code:

Sub test()

textbox1 = "31/05/05"
textbox2 = "01/06/05"

dt1 = DateValue(Mid(textbox1, 4, 2) & "/" & Left(textbox1, 2) & "/" &
Right(textbox1, 2))
dt2 = DateValue(Mid(textbox2, 4, 2) & "/" & Left(textbox2, 2) & "/" &
Right(textbox2, 2))

If dt1 dt2 Then MsgBox "not in order"

End Sub


And it does not give error. Which means it is working. Please check if
this is how it should work and let me know.

Another point: The code I gave you earlier, has 2 conditions, I hope
the other one is not falling apart.


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=368692

this is the code that I am using.

The below is the validation code which i am using this is run when a
command botton is press. Please ntoe I have 4 textboxs or which 3 I use
the validation eg. textbox5,7,8. Textbox 6 is not validated in this
code.

dt1 = DateValue(Mid(TextBox5, 4, 2) & "/" & Left(TextBox5, 2) & "/" &
Right(TextBox5, 4))
dt2 = DateValue(Mid(TextBox7, 4, 2) & "/" & Left(TextBox7, 2) & "/" &
Right(TextBox7, 4))
dt3 = DateValue(Mid(TextBox8, 4, 2) & "/" & Left(TextBox8, 2) & "/" &
Right(TextBox8, 4))

If dt1 dt2 Or dt2 dt3 Then
MsgBox "Dates are not in order please correct Section 5. Date of."
GoTo 1
End If

The below is the code that I am using while the user is inputting. I am
using the below code for all 4 textboxes.

Private Sub Textbox6_change()
TextBox6.MaxLength = 10

If Len(TextBox6) = 2 Then
If Right(TextBox6, 2) 31 Then
MsgBox "Invalid date. Please enter correct value for day"
TextBox6.SetFocus
Exit Sub
Else
TextBox6 = TextBox6 & "/"
End If
End If

If Len(TextBox6) = 5 Then
If Right(TextBox6, 2) 12 Then
MsgBox "Invalid date. Please enter correct value for
month"
TextBox6.SetFocus
Exit Sub
Else
TextBox6 = TextBox6 & "/"
End If
End If
End Sub

the user inputs as dd/mm/yyyy, would you code not be working due to you
having dd/mm/yy. i think it is saying while nothing can be higher than
31 so therefore if the date is 31 and the next date is 1 then it says
it is not in order.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=368692

Hi Funkymonk,

Is it giving the error "not in order" when you are entering the second
date halfway through. Then could you post the other code also where you
check which dates are greater/lesser.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=368692

i change the code the code which i submitted just now to the below.

dt1 = DateValue(Left(TextBox5, 2) & "/" & Mid(TextBox5, 4, 2) & "/" &
Right(TextBox5, 4))
dt2 = DateValue(Left(TextBox7, 2) & "/" & Mid(TextBox7, 4, 2) & "/" &
Right(TextBox7, 4))
dt3 = DateValue(Left(TextBox8, 2) & "/" & Mid(TextBox8, 4, 2) & "/" &
Right(TextBox8, 4))

If dt1 dt2 Or dt2 dt3 Then
MsgBox "Dates are not in order please correct Section 5. Date of."
GoTo 1
End If

I looked at the code and realized that the data was putting into
mm/dd/yyyy instead of what the user was entering as dd/mm/yyyy so i
move the left(textbox5,2) which refers to the date as well as the
mid(textbox5,2) around as that was refering to month, now it seems to
be working 100%

Thanks for your help :)


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=368692

So I presume that your problem is solved. If so, its nice to know.
Thanks for the feedback.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=368692