INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Date field in SQL not working on form with UK date

Date field in SQL not working on form with UK date

(OP)
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...

Quote:

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

RE: Date field in SQL not working on form with UK date

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.

RE: Date field in SQL not working on form with UK date

(OP)
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

RE: Date field in SQL not working on form with UK date

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.

RE: Date field in SQL not working on form with UK date

(OP)

Quote:

(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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close