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!

Simon Harris wrote:
> 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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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.

--
HTH
Van T. Dinh
MVP (Access)



"Simon Harris" <too-much-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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!
>

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

Simon.

"Van T. Dinh" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Simon Harris" <too-much-(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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!
>>
>
>

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 2630 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

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

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.

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 2630 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

"Simon Harris" <too-much-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>>> 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.
>
> 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.

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> 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)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)

Thanks - Will check them out.

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 2727 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!