Dates

[bogglebeats]

How do i subtract 2 dates in Access? I want the value of one field to be the difference of two different fields. these two fields are dates. i would like to display the calculated field as the number of days that have passed between the two dates. thanks

ameen

 

[geralf]

Try this in a textbox and set the recordsource for it to

=DateDiff("d",[FirstDateFieldNameHere,[SecondDateFieldNameHere])

 

[bogglebeats]

the two date fields are from 2 different tables. would it be possible to do this in a query?

 

[geralf]

Yes, bring the two tables together in a query. There is a relationship between them- yes?

Pull down the datefields (and other required) and enter the previous posted line into the next free column. Enter it in the field row

 

[bogglebeats]

if i put the line,

=DateDiff("d" ,[FirstDateFieldNameHere,[SecondDateFieldNameHere])

in the criteria segment of the field that i want to calculate the difference of the two dates, i get an error message that says i have too many arguments. should i be putting that line somewhere else?

 

[geralf]

Not in the criteria, but in the first row of a free column in the query. (the place where you normally see your field names). After entering the line, Access will add Expr1: in front of it.

 

[bogglebeats]

would you be able assign a specific field with that expression then?

 

[geralf]

Not shure what you mean by your last post, but if you use the query as a recordsource for a form or report, you'll see the 'Expr1' in the field list, and can drag it as a field to the form or report. In general you should not store a calculated value.

 

[bogglebeats]

thank you very much. this works.