Hello all, I am currently converting a large time and attendance package 
from pure Access 2000 to an Access project connecting to SQL 7. My problem 
is, when I try to write a time value, ie the time an employee has clocked 
on, into a datetime field, it is automatically prefixed with "01/01/1900". 
This is no use to me as it upsets all sorts of reports etc, and the wages 
of hourly paid staff are calculated from here - there may be some knock-on 
effect for these calculations, DateDiffs etc if it is looking at a date 
and time rather than just a time value. If I just type a time value 
directly into the table there is no problem, only when it is inserted from 
code. Is there some bit of formatting trickery I can use, either in the 
Access front end or the SQL table, to force it to insert only the time 
value? I have tried TIMEVALUE and FORMATDATETIME to no avail, as can be 
seen in this example. The original time value is obtained from a string 
stored in a field TransIn![F4]..........


"INSERT INTO [Incorrect Issue Nos] " & _
"([Employee No], [Issue No], Date, Time, [Reader No], [Clock Type]) " & _
"VALUES (" & Val(Right$(TransIn![F3], Len(TransIn![F3]) - 1)) & ", '" & 
Left$(TransIn![F3], 1) & "', " & _
MakeUSDate(DateValue(TransIn![F5])) & ", '" & FormatDateTime(TimeValue
(TransIn![F4]), vbShortTime) & "', " & TransIn![F1] & ", " & TransIn![F2] 
& ")"


.....thanks in advance.

Alan

SQL Server ALWAYS stores both the date and time in a datetime field. Here's
 the relevant extract from SQL BOL:

Microsoft=AE SQL Server=99 2000 has the datetime and smalldatetime data typ
es to store date and time data.

There are no separate time and date data types for storing only times or on
ly dates. If only a time is specified when setting a datetime or smalldatet
ime value, the date defaults to January 1, 1900. If only a date is specifie
d, the time defaults to 12:00 A.M. (Midnight).


Even when you type the data into the table, it's still storing the date, it
's just not displaying it.

I'd suggest storing the correct date with the time - this would allow your 
date calculations to work correctly,

Brian

>  from:    Alan Edwards <alan.edwards@n...>
>  date:    Thu, 25 Apr 2002 12:18:53
>  to:      access@p...
>  subject: Re: [access] Problem Updating SQL DateTime fields from an Acces
s Project
>
> Hello all, I am currently converting a large time and attendance package

> from pure Access 2000 to an Access project connecting to SQL 7. My proble
m
> is, when I try to write a time value, ie the time an employee has clocked

> on, into a datetime field, it is automatically prefixed with "01/01/1900"
.
> This is no use to me as it upsets all sorts of reports etc, and the wages

> of hourly paid staff are calculated from here - there may be some knock-o
n
> effect for these calculations, DateDiffs etc if it is looking at a date

> and time rather than just a time value. If I just type a time value
> directly into the table there is no problem, only when it is inserted fro
m
> code. Is there some bit of formatting trickery I can use, either in the

> Access front end or the SQL table, to force it to insert only the time
> value? I have tried TIMEVALUE and FORMATDATETIME to no avail, as can be

> seen in this example. The original time value is obtained from a string

> stored in a field TransIn![F4]..........
>
>
> "INSERT INTO [Incorrect Issue Nos] " & _
> "([Employee No], [Issue No], Date, Time, [Reader No], [Clock Type]) " & _
> "VALUES (" & Val(Right$(TransIn![F3], Len(TransIn![F3]) - 1)) & ", '" &

> Left$(TransIn![F3], 1) & "', " & _
> MakeUSDate(DateValue(TransIn![F5])) & ", '" & FormatDateTime(TimeValue
> (TransIn![F4]), vbShortTime) & "', " & TransIn![F1] & ", " & TransIn![F2]

> & ")"
>
>
> .....thanks in advance.

DOH!

Oh well, cheers anyway Brian. Looks like a bit of code redesign coming up!!