Date field in SQL not working on form with UK date

[1DMF]

Hi,

I have a table in MS SQL that has some columns declared as Date (NOT DateTime)

When I link Access to SQL and view the bound fields on a form they are not recognised as date fields and show as text.

Any input of a valid date creates the following error...

Conversion failed when converting date and/or time from character string

I'm using 19/10/15 as the date.

Because I am unable to apply a data format type to the form field as it isn't recognised as a date field, it is taking that date as an invalid USA date.

I don't want a time dimension in the SQL table, but it seems Date isn't a recognised data type in MS Access 2010, so it is taking the input as string literal and USA format!

Is there any way I can fix this so it recongnises UK dates correctly like all my other datetime fields do?

Thanks,
1DMF


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[lameid]

You could parse out the day month and year and pass them to date serial. Then you can format the resulting date however you want.

That's an off the cuff answer. The other thought I have is that you can send commands directly to SQL, so it is a matter of knowing what the server expects in terms of locale etc. Honestly I have not dealt with this particular issue as everything has been the US region or text imports that you just deal with as I said above by parsing out the date pieces.

 

[1DMF]

Problem being though they are 'bound' fields. It would take a bit of work to re-write the app to use unbound fields just so I can massage the data behind the scenes.

I think I'd rather change SQL to datetime than do that!

It seems even though SQL is 2008 and Access is 2010, in classic MS fashion they aren't compatible with themselves!

Two years passed and still Date does not appear to be a recognised format in MS Access, really?

I was wondering if I could programmatically say on 'form open' apply the correct format so it treats the bound field correctly as a date field?

Is this possible?

I tried editing the linked table and changing it to date data type but you can't do that with linked tables via the GUI, what about with code?

What about Access 2013? Does that know what a date field is, or is this still an issue with Access 2013/2016?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[lameid]

To workaround, I would use a query as the record source for the form. I would get the date to a date in the query as well as include the other field. Then you stack an unbound control on top of the bound control for the date. On the after update event of the unbound control, you write data to the not shown date field. A lot of annoying little things to work out but a viable solution (when I have done this sort of thing before it feels like a death of a 1000 cuts). I'm currently not using SQL and Access 2010 so I have no idea on the other questions.

 

[1DMF]

(when I have done this sort of thing before it feels like a death of a 1000 cuts).

You're not wrong there! That is way complicated for a simple date field and there are five of them on the form!

I've changed them to datetime fields in SQL and re-linked them, by far the simplest solution, though searches can no longer be performed with an equals against a date!



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music