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

 

[Pat Hartman]

Do NOT use the change event. It fires once for EACH character typed. Use the BeforeUpdate event instead.

 

[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.

 

[Pat Hartman]

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

He didn't say that. If you want to accommodate two types of data entry, you have to use the method that works for both. Technically, you might be able to use the Change event but the problem is with the Isdate() function. It is way too flexible so partial strings will validate as dates and if you use more rigid criteria, you still run into problems because you apply the criteria at every character and can't really tell whey the user has finished typing the date. That trigger is moving to a different control.

 

[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

 

[Pat Hartman]

Access makes assumptions when dealing with dates. If the date doesn't logically conform to your system date format but it could be a date when looked at in a different common format, then it accepts the date. Since 31.2.23 makes sense as the common format yyyy/mm/dd, then it is accepted.

Only the last expression raises an error

But - print isdate(#31/23/2#) raises an error because yy/dd/mm is not a "standard" field order. The three standards are m/d/y, d/m/y, and y/m/d

There are two potential solutions.
1. Always do sanity checks on dates. For example DOB and DOD should never be > today's date. Employee DOB should be > (date) - 18 years, etc.
2. Add the short date format to the control to specify the field order for data entry.

 

[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.

 

[Pat Hartman]

I should add that I always use 4-digit year unless I am really strapped for space on a form. It avoids part of the ambiguity problem. We are also getting close to the Y2K assumption date which was something around 32 and which seems to have now been changed to 50. so if you enter 1/1/50, you get 1/1/1950 but if you enter 1/1/49, you get 1/1/2049. If you enter my DOB using a 2 digit year, you get the wrong century since I was born before 1950. That is where the sanity check will come in helpful since I can't have been born in 2050.