Entering time in access when linked to SQL table

I have an sql table that has a field that is defined: [Finish] [time](0) NULL.

I am linking the table to an access database and then typing the data directly into the table. If I use military time the data gets saved without a problem. If I type in non military time like 1:34 PM I get an error: invalid character value for cast specification.  

Is this possible to do? Do I need to change the data type of the column?

Originally I had the field defined as a datetime field the person who hire me for the project does not want to see the date.  They want to be able to cut and paste the information into the table. I changed the field to characters but they keep pasting invalid information so that does not work.  

I am using access from office 2013 and sql 2012

Any suggestions would be appreciated.

Thanks in advance,

Karen
LVL 1
kw66722Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You should revert the data type of the field to DateTime (not DateTime2 or anything else), and your troubles will be gone.

If you "don't want to see the date" - and you wouldn't if the time part is the only of interest - just apply a proper format to the textbox where you display the value.

/gustav
kw66722Author Commented:
Other than creating a form how do you apply a format to a link table?  The main user is using the raw table.  The other users are using a form.

When the data is save from access using a datetime field the date defaults to
1899-12-30 - the data is displayed as time only in access

If the save field is updated from SQL server the date portion is displayed as 1/1/1900
in access both the date and time are displayed which is not what the user wants to see.

If I convert the time field to a datetime field the 1/1/1900 date will be used.  Is  there a way to check if the date is 1/1/1900 and than  change it to the 1899-12-30 date?

Or is there an easier solution to get the same result?

This is a sample of the data as the user wants to see it.  The data is literally cut and pasted into the linked SQL table in access.

text1, text2, text3, text4,      dateonly,      timeOnly1,  timeonly2      
text1 text2,  text3, text4,      8/13/2015,12:26 PM, 12:57 PM
Gustav BrockCIOCommented:
That's because the numerical zero value of a date is 1899-12-30 in Access but 1900-01-01 in SQL.

So if a time is entered in Access, it will be saved with a value for the year of 1899-12-30. This is suppressed when displayed in Access except if you specify a date format for the value.
So the main user should enter the time values in Access if that is where they later will be displayed.

If data is entered otherwise, you will have to decide which date should be the "zero" date and stick to that. If that is not possible, you will to - when retrieving data - to convert to the time value only. In Access that is TimeValue, in T-SQL it is CAST or CONVERT as far as I remember.

/gustav
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

kw66722Author Commented:
In access I am directly linking to the sql table and not doing an retrieval routine.
Gustav BrockCIOCommented:
Well, when you open that linked tabled you are retrieving the data ...

/gustav
kw66722Author Commented:
I have never intervened when opening a table. Is there a specific event I would need to put code in?
Gustav BrockCIOCommented:
No, that happens any time you open a table - manually, bound to a form or report, or in code.

/gustav
kw66722Author Commented:
thanks -

I created a sql rigger to update the date if it is 1/1/1900 to 1899-12-30.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kw66722Author Commented:
My own comment was the final result to accomplish what I needed. But without Gustav's input i would not have been able to come to that conclusion.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.