result of Date$ is americanized in Access 97 VBA

[Gary]

Hello,

Im sure there's an easy solution to this but i cant find.

I have one table. this table has a field inside it called date.
I have a control on a form, lets call it control for simplicity.

I have a button next to that control, which has some 'on click code' in
vba which is:

Forms!formname!control.value = Date$

when this button is clicked it put's today's date into my control which
is great. However, the date is americanized, the month and day are the
wrong way around.

How do i get it to return the date in a British format?

Thank You.
Gary.

 

[Allen Browne]

Several potential issues here, Gary.

See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Gary]

Thankyou for your very quick reply. I have had a look and the article
was interesting but I still can't see how to get date$ to return the
date properly. I have tried putting '#' 's around date$ but this
doesn't work.

In the VBA code I tried date$(dd/mm/yy) as a long shot but that didn't
work either, as vba didn't like that syntax.

In the table I have checked and the field is specified as a date/time
field - I have tried 'general date' and 'medium date' as the format of
this field and allthough they both look find in the example provided in
the table designer, the control on my form seems to be totally ignoring
this format.

Any advice would be appreciated thanks.

Gary.

 

[Allen Browne]

Gary, we need to be really clear about whether you are talking about showing
a date in a textual format, or working with a real date. The Format()
function will give you a text result, but the dates won't sort correctly,
and date math will not work reliably.

Internally, Access handles date/time values as real numbers, where the
integer part is the date, and the fractional part is the time (e.g. noon =
0.5.) It then formats the date to display it to the user according to your
regional settings (in the Windows Control Panel.)

As explained, JET and VBA expect literal dates in the American format, and
converts them accordingly. However, in the interface, the dates you input
are interpreted according to your regional settings.

Now, are you asking how to get Access to *display* a date accoring to a
particular format? If so, make sure your regional settings are correct in
Contol Panel. Any control bound to a date/time field will then display
correctly. Any unbound control can be made to display correctly just by
setting its Format property to one of the date formats, such as Short Date.

However, if you are happy with just text representations of the date, and
you don't need to sort, filter, or search by them, the Format() function
will do what you asked.

 

[Gary]

Many thanks again,

I will need to sort by these dates eventually, although i will look
into the format() function you mentioned as I haven't seen it before.
However as I will need to sort by these dates i'd like to try and get
this working as a 'real date'.

The control is bound to the field in my table, so as per your advice I
checked the format of the control and I hadn't specified a format.

So i specified the format in the control as 'Long Date' - but would you
beleive it, it's still wrong! instead of reading 10/12/2005 it is now
reading 10 December 2005. Still the wrong way around - the date should
be today's date (i.e. 12/10/05 in british).

I have checked regional settings and i'm set to English United Kingdom
- and in the sample boxes provided in the control panel, my Long date
is displaying fine. For some reason however access is just ignoring my
regional settings.

 

[Allen Browne]

Okay, that's starting to make sense.

If you have the correct date format specified in Control Panel, and the
Format property of the text box set, then the data is wrong in the table,
i.e. you need to change the entry so it is Oct 12 instead of 10 Dec.

 

[Gary]

i'm still stuck with this. the data in the table is wrong. but it's
automatically generated by the code:

Private Sub Command35_Click()
Forms!Data!DateLastCalled.Value = Date$
End Sub

My problem is date$ is returning the date in the american format, i
need date$ to return the date in the uk format - or i need another
function that will return the date in the uk format. thanks! gary.

 

[Allen Browne]

Okay: as suggested by the $ suffix, Date$ returns a string. The string in in
American format, which is then misinterpreted by your regional settings.

Instead use the Date() function, or just Date:
Forms!Data!DateLastCalled.Value = Date

 

[Brendan Reynolds]

I tried the following ...

Private Sub Command18_Click()

Me.Date1 = Date
Me.Date2 = Date$

End Sub

I got 12 October 2005 in Date1, and 10 December 2005 in Date2.

The Date function returns the system date as a Date/Time value, while the
Date$ function returns the system date as a String. As you're storing the
value in a Date/Time field, the String returned by Date$ has to be converted
back into a Date/Time value before it can be stored. Somewhere in the
conversion process things are going awry. Just use Date instead of Date$ and
you should be find.

BTW: If your field is named Date, you should rename it, or Access is likely
to get confused between the name of the field and the name of the function.

 

[Douglas J Steele]

Access gets its format from Regional Settings.

However, you could try using the Format function:

Forms!Data!DateLastCalled.Value = Format(Date$, "dd/mm/yyyy")

 

[Brendan Reynolds]

you should be find.

<snip>

.... or "fine", even!

 

[Allen Browne]

There, you go, Gary. Now you have help from Irish (Brendan), Canadians
(Doug), and Australians who all use your dates.

 

[Gary]

That's it sorted!
I didn't have any luck with format() it still returned in the american
format, but the date instead of date$ worked great.

Allen thank's for seeing it out to the end, and thanks everyone else
for your excellent transatlantic advice

=)

 

[Douglas J Steele]

Sorry to disappoint you, Allen, but I use yyyy-mm-dd <g>