DTPicker on Form

[Mister-B]

I have a Date Picker on my form to jump to the record that corresponds to the date in the Date Picker using the change event

As long as I select the date with the date picker, everything is Ok. If I wish to select a date by typing it in manually, I get an error message because the change event is firing before the completed date is typed in. How can I get around this?

Kind regards,

 

[561414]

Don't let it fire until conditions are met. Example:

Private Sub MyDateTextbox_Change()
    If Len(Me.MyDateTextbox.Text) > 9 Then MsgBox "fire"
End Sub

 

[arnelgp]

if you also want to type a date, you also add code to the AfterUpdate event of your textbox to "Jump to record"

Dim bolKeyed As Boolean

Private Sub DateTextbox_AfterUpdate()
' code to Jump to record here
End Sub

Private Sub DateTextbox_Change()
If Not bolKeyed Then
    'code to Jump to record here
End If
bolKeyed = False
End Sub

Private Sub DateTextbox_KeyPress(KeyAscii As Integer)
bolKeyed = True
End Sub

 

[CJ_London]

Put some code in the change event to check it is a valid date? Something like

If isdate(mycontrol.text) then

 

[arnelgp]

If isdate(mycontrol.text) then

IsDate() most of the time is forgiving:

?IsDate("1/1")
True

?IsDate("60/1")
True

 

[CJ_London]

good point, so perhaps use cDate with some criteria to validate

?cdate("6")
05/01/1900

?cdate("60/2")
01/02/1960

?cdate("6")>date()-30
False

?cdate("6/12/22")>date()-300
True

 

[CJ_London]

before update event doesn't fire until the control loses the focus - which as described by the OP may not be what is wanted. But it is an alternative

 

[561414]

You have to account for too many things with date data types, string validation is simpler, more predictable and if you tell the user to use a certain format, the user will definitely understand the format expected more than having that same user learn how many ways dates can be handled and their differences.

 

[Mister-B]

Thank you for the many tips. I finally got it to work by inserting the code

If Len(Me.txtSuchen) <> 10 Then Exit Sub

into the module.

Thanks again.

 

[EL_Gringo]

Put some code in the change event to check it is a valid date? Something like

If isdate(mycontrol.text) then

isdate() even changes dates to something really unwanted: e.g. 31.2.2023 to 23.2.31 or something like that. In my opinion, isdate() is of quite limited usefullness.

 

[CJ_London]

isdate() even changes dates to something really unwanted:

admittedly anything that looks like a date is valid but isDate doesn't change anything, it returns a boolean value.

Perhaps you didn't read the following posts, suggest provide some examples where it changes the value

 

[EL_Gringo]

admittedly anything that looks like a date is valid but isDate doesn't change anything, it returns a boolean value.

Perhaps you didn't read the following posts, suggest provide some examples where it changes the value

You are right and wrong. IsDate does change the date for testing purpose. To my surprise, the Format function does as well:
Computer set to European date system (DD.MM.YY). I have the textboxes "NewDate" and Me!NewDate2 on a form, short date format. I type in 31.2.23, Enter. The AfterUpdate event:

Private Sub NewDate_AfterUpdate()
    If IsDate(NewDate) = True Then
        MsgBox Format(NewDate, "DD.MM.YY") & " is a date"
        Me!NewDate2 = Me!NewDate
    Else
        MsgBox Format(NewDate, "DD.MM.YY") & " is not a date"
    End If
    Me!NewDate.Requery
End Sub

And voilá, Msg shows "23.02.31 is a date" which certainly is true, but I tested 31.2.23, which is not a valid date. NewDate2 shows "31.2.23". Consequence: I cannot rely on the IsDate() function.

 

[CJ_London]

IsDate does change the date for testing purpose

you still have not demonstrated that. You are in a different country where the date format uses a decimal rather than a / but for example

?isdate("31/2/2023")
False

and using the format function - a valid date is formatted OK, whilst an invalid date generates a compile error

 

[GPGeorge]

One of the basic tenets of software development to which I adhere is this:

Ambiguity is required in humor, poetry and politics.
Ambiguity is disastrous in databases.

For that reason, when confronted with options that really on implicit evaluations which can be resolved in more than one way and explicit evaluations which can be resolved in one and only one way, I want to go with the explicit evaluation.

Access is famously tolerant of ambiguous dates. As Pat demonstrates, it's dangerous to rely on Access "guessing right" 100% of the time.

Use unambiguous dates. Use unambiguous functions to evaluate those dates. Write poetry in your spare time.