Help with calculating in a control... please

  • Thread starter Thread starter DevArrah
  • Start date Start date
D

DevArrah

I'm having a problem with a control in access 2000.

I have two textboxes on a form called start and length
In the controlSource of a third textbox I have =[start]+Year[length]
The problem is when I enter data into the field I get nutty results.
After literal hours tooling around with I finally tried setting the
controlSource to =Year([length])

Then when I played with the form get these results (result as number
formatted as a general number)

entered into length Result
0 1899
1 1900
2 1900
3 1900

All i want to do is to have a text that automatically adds 'length'
years to the start date. What on god's green earth am I doing wrong? Any
semi-logical reason why I get the results that I do?

I admit this is the first time I'm i've done any extensive fooling
around in access but for the life of me I don't understand why the same
calculation works in a query but not in the control.
 
Assuming that Start is a Date/Time field, and Length is a Number field, use
the DateAdd() function to add the number years to the date, i.e.:
DateAdd("yyyy", [length], [start])

To explain the results you got, internally Access stores date/time values as
a number where the whole number represents the date and the fraction the
time (0.5 = noon (half a day), 0.25 = 6am, etc). They decided that day zero
would be Dec 30 1899. Therefore:
Year(0)
calculates as 1899, and so does Year(1) since that's the next day. Year(2)
is the first day of 1900, so following days up to 366 are all in 1900.

HTH
 
Allen Browne said:
Assuming that Start is a Date/Time field, and Length is a Number field, use
the DateAdd() function to add the number years to the date, i.e.:
DateAdd("yyyy", [length], [start])

To explain the results you got, internally Access stores date/time values as
a number where the whole number represents the date and the fraction the
time (0.5 = noon (half a day), 0.25 = 6am, etc). They decided that day zero
would be Dec 30 1899. Therefore:
Year(0)
calculates as 1899, and so does Year(1) since that's the next day. Year(2)
is the first day of 1900, so following days up to 366 are all in 1900.

HTH
Click to expand...

Thanks, I knew it was something like that :) The folks at work
brilliantly failed to install the help files so a lot of times I'm
shooting in the dark with only the scripting I did for excel and word
that I half-remember as the flashlight ;)

Anyhoo, I appreciate your response to a clearly RTFM question with
dignity and grace.

-Eli
 
Back
Top