Formating Date/Time in an Access FrontEnd with a SQLServer backend

[David C. Holley]

I am in the process of converting an Access back-end to SQLServer. At
the same time I am altering the database schema which is now...

tblTransferHeaders (one-side)
transferHeaderID
date

tblTransferDetails (many-side)
recordId
transferHeaderId
transferTime Small Date

I want to provide my users with a form where they enter a time (eg
10:00) and store it in the transferTime field of the tblTransferDetails
table. The problem, is that SQLServer evidently wants me to provide a
date component along with the time. (When I enter 10:00 AM in the Access
FrontEnd, I get a ODBC error indicating that SQLServer doesn't like the
value. If I enter 4/15/2006 10:00 AM, everything is fine.)

1) The DATE component *HAS* to be in the header table.
2) I don't care if the DATE component is stored along with the TIME.
3) I want to be able to only have to enter the TIME in the Access Front End.

Question:
Can SQL Server be setup to only capture the time or do I have to
manipulate the value using VBA?

 

[Alex Dybenko]

Hi David,
the right answer depend on how you want to use this time later. You can
either store it as string, or add date portion using VBA or trigger

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[David C. Holley]

I ended up adding the date to it via VBA as simple as

Me.txtTime = CDate(CStr(Format(Me.Parent.sdteTransferDateTime,
"mm/dd/yy")) & " " & Me.txtTime)

The fun part will be the VBA code that updates the child records when
the date changes. Athough not neccessary since only the TIME from the
child records will be displayed, but preferred just in case that changes.

How have things been in Russia?