Question Time Data Type, SQL Server Versus M Access, Access 2010

[KenRob]

Hello all!

I have an Access front end linked to a SQL Server 2008R2 database. I am having a lot of trouble with the time field.

I have a few time data type fields in a few tables. I have chosen the time(0) data type in SQL server, but whenever I attempt to add test data to the time fields via the Microsoft Access front end, I receive an error stating, "Invalid character value for cast specification."

Has anyone had a similar issue? Any advice?

Thanks!!
:banghead:

Ken

 

[GinaWhipp]

Welcome to the Forum!

How are you entering the time?

This might help...
http://msdn.microsoft.com/en-us/library/bb677243.aspx

EDIT: No image attached

 

[KenRob]

Hi Gina,

Thanks for your reply.

I was entering the time through an Access form with a field that was formatted as a date. I tried to enter the time directly to the table and realized I have to include the colons. This seems clunky. But at least I got the data into the table. I just wish my end users didn't have to type the colons

-Ken

 

[GinaWhipp]

Perhaps you could apply a Format to the field to elimate that?

 

[KenRob]

My thoughts exactly Gina. time(0) requires hh:nn:ss, but when I apply that format I still get the error message.... I am wondering now if formatting, or input masks actually store the punctuation (colons in this case).

Hmm... it's a head scratch-er!

 

[KenRob]

Gina! It works!

I used the input mask wizard to create a new input mask. Even though I had used the same mask (00:00:00;0;_) before, when I added it with the wizard, it asked me when I wanted to save the characters (colons) or not. This allowed me to save the time in a way that SQL Server would accept.

Thank you for you help and support, made all the difference !

 

[GinaWhipp]

No problem, glad to help! Any your Users can do the happy dance, even better!