Dates and Calculated Fields

[JerSand]

I thought this would be simple. My form contains, among other fields, short date fields entitled "Expected date of first separation" and "Date of the letter". Each appears simply as the result of dragging the fields from the table on which the form is based onto the form design grid. I would like an unbound text box to display the difference in days between the two dates for each record after the user enters the dates. So I created the unbound text box and entered the folowing code on the control source line: =[Data]![Expected date of first separation]-[Data]![Date of the letter].

I've looked for a Date/Time function that will make this work, but so far have found none and, of course, am getting #Name? as my result.

I'd be grateful for a solution.

Thanks.

JerSand

 

[lameid]

Control sources are a little different than code... About anything that will work in a query will work in a control source as long as it has an equal sign in front of it.

Try

=[Expected date of first separation]- [Date of the letter]

I bet that control source works for you.

 

[WildHare]

..and the function you're looking for to give you intervals other than days is

DATEDIFF(/interval/, /start-date/, /end-date/)

where Interval can be

"YYYY" - years
"Q" - Quarters
"M" - Months
"W" - Weeks
"D" - Days
"H" - Hours
"N" - minutes
"S" - Seconds

Haven't the foggiest why the year guy needs all four characters, instead of just one "Y" - but if you use just ONE Y, you will get the number of DAYS between them. Go Figger.

The result is a NUMERIC value of some type, not a date/time guy, don't forget.

JMH
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[missinglinq]

While you can NAME controls using words separated by spaces, i.e. "Expected date of first separation" VBA doesn't always allow you to manipulate these controls if you do so; this goes for the code underlying the Properties Box (i.e. the Control Source of the unbound text box).

I created a table and resulting form using the names "ExpectedDateOfFirstSeparation" and "DateOfTheLetter" for the fields/textbox, created an unbound text box and in the Control Source for this text box placed the simple code:
=[Expecteddateoffirstseparation]-[Dateoftheletter]

and it works fine.

Hope this helps.

The Missinglinq "It's got to be the going,
not the getting there that's good!"
-Harry Chapin

 

[JerSand]

Many thanks, lameid, WildHare, and missinglinq. Each of your responses was clear, useful, and worked to solve my problem. I'm grateful.

JerSand