Access equiv to SQL set date format dmy??

[Simon Harris]

Hi All,

I am storing a date value in a table. THe table column data type is set to
short date. My SQL to do the update takes the value from the form, using
CDate(Me.comboInvoiceDate).

When I look at the value in the table, it looks Ok - 11/01/2007. But, when I
view my report, which shows the Month and year for that value, it come out
as November 2007, not January 2007 as expected. I realise why - I have an
ambiguious date, but how do I tell Access the date format to use. I would
like it to use UK format - DD/MM/YYYY

This is my code on my report: CDate(Me.comboInvoiceDate)

Any help will be much appreciated.

Regards,

Simon.

--
--
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Rick Brandt]

Hi All,

I am storing a date value in a table. THe table column data type is
set to short date.

No, it is not. You have a DateTime field and have set the FORMAT property to
short date. This is a display property only. DateTimes are always stored
exactly the same.

My SQL to do the update takes the value from the
form, using CDate(Me.comboInvoiceDate).

When I look at the value in the table, it looks Ok - 11/01/2007. But,
when I view my report, which shows the Month and year for that value,
it come out as November 2007, not January 2007 as expected. I realise
why - I have an ambiguious date, but how do I tell Access the date
format to use. I would like it to use UK format - DD/MM/YYYY

This is my code on my report: CDate(Me.comboInvoiceDate)

Any help will be much appreciated.

Date literals in Access MUST use US formats (month before day) or a
non-ambigious format like ISO (yyyy-mm-dd) or where the month uses alpha
characters like 11-Jan-2007.

 

[Van T. Dinh]

Have you checked the Date/Time settings in the Regional Settings of your
Windows OS?

"Short Date" Format and CDate pick up and use the settings from the Rgional
Settings, AFAIK.

 

[Simon Harris]

Hi Van, Yes - These are set correct for the UK (dd/mm/yyyy).

Simon.

 

[Simon Harris]

I am storing a date value in a table. The table column data type is

No, it is not. You have a DateTime field and have set the FORMAT property
to short date. This is a display property only. DateTimes are always
stored exactly the same.

Ok, they are stored as a serial yes?

Date literals in Access MUST use US formats (month before day) or a
non-ambigious format like ISO (yyyy-mm-dd) or where the month uses alpha
characters like 11-Jan-2007.

So, does this mean I need to go re-code my database? Or, if I am
understanding you correctly, all my inserts/updates effecting a date column
will have been stored as US format, if so - How do I get these out formatted
to UK standard?

e.g. If my user entered 11/01/2007 (11th January 2007) how do I tell Access
this is the case? One i dea I had was to split the date into day, month and
year (3 strings) before reconstructing into an acceptable (US) date and
entering into the table?

This seems to be a bit of a weakness of Access, in that the forms seem to
respect local regional settings, where as the actual data storage does not.

Many thanks for your help.

Simon.

 

[Douglas J. Steele]

Ok, they are stored as a serial yes?


So, does this mean I need to go re-code my database? Or, if I am
understanding you correctly, all my inserts/updates effecting a date
column will have been stored as US format, if so - How do I get these out
formatted to UK standard?

e.g. If my user entered 11/01/2007 (11th January 2007) how do I tell
Access this is the case? One i dea I had was to split the date into day,
month and year (3 strings) before reconstructing into an acceptable (US)
date and entering into the table?

This seems to be a bit of a weakness of Access, in that the forms seem to
respect local regional settings, where as the actual data storage does
not.

You should probably read Allen Browne's "International Dates in Access" at
http://www.allenbrowne.com/ser-36.html, or what I had in my September, 2003
"Access Answers" column in Pinnacle Publication's "Smart Access". (You can
download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

 

[Simon Harris]

You should probably read Allen Browne's "International Dates in Access" at

http://www.allenbrowne.com/ser-36.html, or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
(You can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

Thanks - Will check them out.