#ERROR 1

[uscitizen]

i have a report. i am trying to create a computed field on it. not to get too involved it will involved

"Date Started" (which is in the report) and "Date_Accrual_Closed" which is a parameter the user is prompted to enter when (s)he clicks on the report.

if i create a text/unbound control and populate the "Control Source" property with either "=[Date Started]" or "=[Date_Accrual_Closed]", set the format to a date type, no problems.

if i instead use "=[Date_Accrual_Closed]-[Date Started]" instead, set the format type to Fixed with 2 decimal places, what happens is i get a column of "#ERROR" instead!!

anybody know what that means?

 

[LonnieJohnson]

Are Date_Accrual_Closed and Date Started actual date fields (data type = DateTime in the underlying table)? If not, Access is cannot determine what to do with the string of characters in the text box.

Try using the DateDiff function to get your numeric value.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[uscitizen]

"Date Started" is in the underlying table and yup it's a date/time field.

"Date_Accrual_Closed" is the name i gave to a value the user's prompted to provide when running the report using "[To Date Accrual Closed:]" as the Control Source.

 

[dhookom]

Is the name of your "=[Date_Accrual_Closed]-[Date Started]" text box also the name of a field? If so, change it. Also, since Date_Accrual_Closed comes from a parameter, you might want to use
=CDate([Date_Accrual_Closed])-[Date Started]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Davidwazza]

I find that this kinda error happens to me alot - and yea, as dhookom was saying, it has something to do with the name of the textbook sharing the same name, or something like that.

Try inserting a new text box in your report, and copying the same equation into that. Hey, this worked for me for numerous other stuff, so yeah. Also, the datediff function should also do the trick, but try the above first.

David.

 

[uscitizen]

hi -

i kinda liked dhookum's idea so i took a first tentative step and used

"=CDate([Date_Accrual_Closed])-[Date Started]"

in the Control Source property of the report's textbox and by gosh it gave me real numbers!! then i decided i'd go the next step and divide by 365.25

"=(CDate([Date_Accrual_Closed])-[DateStarted])/365.25"

and, guess what? that works as well!!

now comes the best part, since i want to report on the average number of folks we enrolled in a typical year during the interval computed by the one immedately above, i entered

"=[CountofMedRecNum]/(CDate([Date_Accrual_Closed])-[DateStarted])/365.25"

where the numerator is the census over the interval and the number returned were always zero. bummer, right....

then as i pasted the above into this window, it occurred to me that ms a2k might not be parsing it quite the way i intended, so i parenthesized the denominator and lo and behold, it works

 

[CajunCenturion]

Couple of things to keep in mind uscitizen. First, 365.25 is not the most accurate representation of a year, but rather 365.2425 taking into account the 400th year exception, as was the year 2000.

Also, since you're using the equation in a division formula, you might want to check for zero before dividing.

=IIf((CDate([Date_Accrual_Closed])-[DateStarted])=0),0,[CountofMedRecNum]/(CDate([Date_Accrual_Closed])-[DateStarted])/365.2425

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein