Odd results after update

  • Thread starter Thread starter Anna
  • Start date Start date
A

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
 
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!
Click to expand...

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?
 
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!
Click to expand...

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?


.
Click to expand...
 
Back
Top