Odd results after update

[Anna]

Access 97

In the 'Before Update' event of my form I have some code
that creates a number based on a date inputted previously
in the form:

If Me.NewRecord Then
Me!DateB = DateDiff("d",#01/01/00#,[DateA])
End If

For the majority of cases, this works fine, but I have
noticed some strange results creeping in - when checked,
some DateBs were 1 day out, some 3 days and one was a year
out!

Any ideas as to why this may be?

Many thanks,

Anna

 

[John Vinson]

Access 97

In the 'Before Update' event of my form I have some code
that creates a number based on a date inputted previously
in the form:

If Me.NewRecord Then
Me!DateB = DateDiff("d",#01/01/00#,[DateA])
End If

For the majority of cases, this works fine, but I have
noticed some strange results creeping in - when checked,
some DateBs were 1 day out, some 3 days and one was a year
out!

I'm surprised ANY of them are right. DateDiff does not return a date;
it returns a Long Integer count of days. A Date/Time is stored as a
double float number, a count of days and fractions of a day since
midnight, December 30, 1899; if DateB is bound to a Date/Time field
this would turn (say) January 16, 2000 to an integer 15, and then
convert that to a Date of #01/14/1900#. Leap years would introduce
additional offsets.

What are you actually trying to accomplish?

 

[Anna]

Thanks for your reply.

I'm using the integer created by the DateDiff function to
make an 'AppointmentID' field (ie PersonalID + no of days
after 01/01/00 the appointment is) which I can then use to
check for duplicate records, etc. (This may sound a wierd
way to do things, but it would take too long to explain!)

I think it's probably something to do with the form, as
when I checked (looking for records where AppointmentID
was not equal to "DateDiff("d",#01/01/00#,[DateA])" - the
same code as in the 'before update' event) it came up with
the odd results I listed previously.

Can you think of anything that might be causing this? The
error has occured on 6 records (out of about 3000).

Many thanks,

Anna

-----Original Message-----

Access 97

In the 'Before Update' event of my form I have some code
that creates a number based on a date inputted previously
in the form:

If Me.NewRecord Then
Me!DateB = DateDiff("d",#01/01/00#,[DateA])
End If

For the majority of cases, this works fine, but I have
noticed some strange results creeping in - when checked,
some DateBs were 1 day out, some 3 days and one was a year
out!

I'm surprised ANY of them are right. DateDiff does not return a date;
it returns a Long Integer count of days. A Date/Time is stored as a
double float number, a count of days and fractions of a day since
midnight, December 30, 1899; if DateB is bound to a Date/Time field
this would turn (say) January 16, 2000 to an integer 15, and then
convert that to a Date of #01/14/1900#. Leap years would introduce
additional offsets.

What are you actually trying to accomplish?


.