SQl Date Format for the Brits (1 Viewer)

[SmallTime]

I keep hitting this problem time and time again and it’s driving me to distraction.

Here’s What I have:
A continuous form with the RecordSource as a linked SQL table (QLTblHolidays ) that contains a date field (MyDate).

Here’s what I want
1. The date displayed in British format e.g. 01/01/2011
2. The field must be updatable
3. Must be able to add new records on the continuous form

Here’s my problem
If I leave the RecordSource as the linked table I can update and add records BUT crucially the date is in American Format e.g. 2011-01-01.

If I change the RecordSource to a query and format the date like so...

SELECT CDate([MyDate]) AS ConvDate FROM QLTblHolidays;

The form’s no longer updatable and I’ll get the message ‘… is based on an expression and cannot be updated' and also I can’t add a new record.

How do I overcome this without creating yet another separate unbound form to handle updates and editing?

SmallTime
Haven’t much hair left to pull out so now I grind my teeth instead. Hoping someone can help me out before I get to the gums

 

[Galaxiom]

BUT crucially the date is in American Format e.g. 2011-01-01.

That is not US format.You description suggests it is a text field and that is the problem. Dates should be stored as Date type fields.

Date is a Double datatype consistent across all date regions. Access displays it according to the regional settings of the computer.

 

[SmallTime]

Thanks Glaxiom for quick answer.

just double checked and DataType in sql Server is Date. However, viewing the linked table in design view from Access is indeed text.

SQL format = Date, Access format = Text.

It's a linked table so how do I resolve this?

 

[Galaxiom]

SQL format = Date, Access format = Text.

That is odd. I have SQL Server (2005) tables linked to Access (mdb in 2007) and the datatype comes across.

There is some problem in the ODBC. What ODBC client connection are you using?

 

[SmallTime]

using access 2010 with SQL server 2008 r2

Its DSNLess link

 

[Galaxiom]

A DSNless connection use an ODBC connection string.
What is its Provider property?

I do have a recollection of seeing something about a problem with Dates to do with a setting in SQL Server 2008 but I can't remember what it was.

 

[SmallTime]

What is its Provider property?

Sorry, don't know where to look for this.

I did notice this date issue before and thought it was just a MS quirk. I've been managing dates with the format() , and CDATE() functions but it's really getting on my nerves. Now you say SQL 2005 handles dates OK I'm starting to fear for my teeth.

Thanks
SmallTime

PS I'm hitting the sack (6.14am), been working 18 hours strait.

 

[Galaxiom]

Access 2007 does not support the MSSQL Server 2008 DATE format.
The server datatype needs to be changed to SMALLDATETIME.

 

[SmallTime]

Hip, Hip, Hurray.

Just tried out the SmallDateTime and it worked fine. Everything's back to normal. Phew.

This was killing me. Thanks from me, my dentist and my dear old neighbour who thinks I've got turrets.


SmallTime

 

[SmallTime]

Had to come back and say thanks again, spent the whole of yesterday removing all formats and Cdates from vba and queries and testing.

All is now good with the world.

SmallTime

 

[Galaxiom]

Glad we could help you get it sorted. I know it is a good feeling when you get something like that behind you. The clean up itself can be quite satisfying knowing that you can move forward again.