Subtracting date fields

[grogmi1]

Hi,

I have an Access query which has 2 fields 'Sent Date' and 'Return Date'. Sent date is input by the user when they return an item for repair, return date is input by the user when the item is sent back to us from repair. What I need is a 3rd calculated field which will work out the number of days the item was away for repair. I have included the 3rd column 'Repair Duration' but I don't know how to type the formula in the query to work this out.

I'm not the best at Access so any help would be much appreciated.

Regards

 

[Mihail]

Return Date: DateDiff( FieldsNames as parameters)
Search in Access help for the exact syntax for DateDiff function.

 

[grogmi1]

Thanks for the reply Mihal, the Datediff function doesn't appear to work. I just need a similar formula like one I would use in Excel =Sum(Return Date - Sent Date) if that's possible ?

 

[Mihail]

Can't think that don't work.

The formula is:
DateDiff ( interval, date1, date2)

So, in your 3rd field type this:
RepairDuration: DateDiff("d",[Sent Date],[Return Date])
or, maybe, this:
RepairDuration: DateDiff("d",[Return Date],[Sent Date])
should work.

I can't remember the exact order of parameters.
As an advice: Never use a space into a field name.
This way you will avoid a lot of troubles.

So, rename your fields as SentDate and ReturnDate. Of course the previous formulas must be changed a little bit.

 

[grogmi1]

Hi,

Tried both formulas and keep getting errors with syntax ??

 

[JHB]

Try to replace the , with a ;.
RepairDuration: DateDiff("d";[Sent Date];[Return Date])

 

[Brianwarnock]

Hi,

Tried both formulas and keep getting errors with syntax ??

The above and

. the Datediff func tion doesn't appear to work.

Don't really help, I would expect some sort of explanation such as the error message.

Brian

 

[Mihail]

Can you upload the database ?