Check if date is in future and empty textbox

[Bandito1]

Hey all,

I use this small code to check if the date entered is in the future or not.
If it is the user gets a messagebox and the cell should empty.

Private Sub txtStDate_Change()If CDate(txtStDate.Value) > Date Then
    MsgBox "The date of observation is in the future." & vbCrLf & "This is not possible, please correct!"
    txtStDate.Value = ""
    txtStDate.SetFocus
End If
End Sub

Without txtStDate.Value = "" it works. When i added i get error: Type mismatch.
I think because the code runs again when the cells gets emptied.

Someone knows how i can fix this?

 

[dmt32]

Hey Guys,

The date that is showed in the textbox is mm/dd/yyyy. While we use dd/mm/yyyy. Guess here is the problem right?
@dmt32
That doesn't resolve the problem. Guess the problem is what i typed above.

DateValue recognizes the order for month, day, and year according to the Short Date format that you specified for your system

So if you enter 10/13/2019 in your textbox, DateValue should, I would have thought, changed it to 13/10/2019.

e.g.

MsgBox DateValue("10/13/2019") & Chr(10) & Date

maybe another can offer insight to your issue

Dave

 

[Yongle]

Please explain exactly how the value gets into textbox txtStDate

 

[Bandito1]

I added the control monthview in my Toolbox

https://imgur.com/7Dnq8gF

Then i added it to my form and looks like this:

https://imgur.com/hxDe7Fx

I can just click the date and the date is added in the textbox txtStDate in this format: mm/dd/yyyy (which is causing the problem)

 

[Bandito1]

I thought maybe changing the format of txtStDate maybe works but so far no luck;

txtStDate.Value = Format(txtStDate.Value, "dd/mm/yyyy")
    T = CDate(txtStDate.Value)

 

[Bandito1]

That did the trick!

Instead of

[COLOR=#574123]T = CDate(TxtStDate.Value)[/COLOR]

I changed it into

T = Format(CDate(Format(txtStDate.Value, "mm/dd/yyyy")))

It seems to work now.

Only the textbox is still showing the "wrong" format (mm/dd/yyyy while it should be dd/mm/yyyy)

 

[Bandito1]

I added the following line of code;

Me.txtStDate.Text = Format(Me.txtStDate.Text, "dd/mm/yyyy")

The strange thing is that it works only with the date of today; it shows 13-10-2019 but when i press 12-10-2019 it displays as 10-12-2019

Why?

 

[Yongle]

another option which should work provided that your textbox format is consistently mm/dd/yyyy


call the function like this

    txtStDate.Value = ReverseDate(txtStDate.Text)

Private Function ReverseDate(dateText As String)
    Dim d As String, m As String, y As String
    d = Format(Mid(dateText, 4, 2), "00")
    m = Format(Left(dateText, 2), "00")
    y = Format(Right(dateText, 4), "0000")
    ReverseDate = d & "/" & m & "/" & y
End Function

Edit
- this should also give the correct result for y

    y = Right(dateText, 4)