date values -- international settings

[jfp]

On my Win 2000 system, (Control Panel / Regional) i have the short date
format set to "yyyy-MM-dd" (This is one of the standard choices). So,
for example, today will appear as "2003-12-03" instead of the more
traditional "12/3/2003"

On a form i have an unbound control with format set to "Short date". In
the BeforeUpdate method, i call a validation function to make sure that
the value entered is not later than today (using the Date function).

If i type "2/22/2003" into the control:
Access correctly interprets date as Feb 22,
the validation function is satisfied,
Access reformats the date as "2003-02-22" to conform with my settings,
and that is what appears.
All this is good.

Now, if i type "2003-02-22" into the control, things are still OK.

But, if i programmatically put this into the control,
Access somehow interprets it as some date later than today and the
validation function gives me an error.

(In the code, i remember the previous value of a control and
subsequently reload the control with that saved string. Since Access
reformatted the date for me, the string i save is in the yyyy-MM-dd
format; when i set the textbox value to the saved string, that is what i
get. When i subsequently call the validation function, it cannot
interpret the string properly.)

Any ideas ?

 

[Hans-Christian Francke]

Use the DateSerial() function to return a proper date to Access.

 

[jfp]

Use the DateSerial() function to return a proper date to Access.

This is NOT the issue.
The problem relates to some distinction in what Access does when:
a) user types characters into a text box,
b) program code sets value of a text box to a string.

Any ideas anyone ?

 

[Douglas J. Steele]

What's the exact code you're trying to use?

 

[jfp]

What's the exact code you're trying to use?

"edtVal" is the name of the (unbound) TextBox on the form

* In the Form_Open event:

dim strVal as String

' retrieve previously saved contents of textbox into strVal
' this comes from the form that opened us

edtVal = strVal

* In the Click event for the "Continue" button (user has entered data
and wants to proceed)

Dim vt_Hi_Lim As Variant
Dim vt As Variant

' set vt_Hi_Lim to Hi limit for the date
' this comes from the form that opened us
' it is a variant since this form could be used for dates or numbers

vt = edtVal.Value
If (vt > vt_Hi_Lim) Then
' Declare error
End If


-=-=
The complete code is more complicated, since it involves getting values
from the form that opened this one, most of which are in a UDT passed
via a custom class. The portions germane to the issue are above.

-=-=-=
What i think is happening:
If i type "2003-02-22" into the text box, Access processes that value
using my international settings and interprets it correctly, setting
some hidden underlying value.
When the program fills this value into the text box, that processing
does not happen.
Thus, although the visible contents are the same in both cases,
something is different behind the scenes.

Does this make sense ?

 

[Hans-Christian Francke]

Access stores the US date/time format "mm-dd-yyyy hh:nn" careless of what
your textbox format is. Also the general datesetting on your mashine will
affect how dates are displayed, but not how they are stored in Access.
I think you are making a misstake evaluating dates as string while they
actually are doubles. Different datesetting and formating will trick you
around. Treating dates as dates/doubles is the best scope and if you would
like to use strings, keep to the US date/time format.

 

[jfp]

In the code below, if i change
edtVal = strVal
to
edtVal.SetFocus
SendKeys strVal
then it works. So -- looks like it does relate to the difference
between value being "typed" in vs programmaticaly set.