new to access (date calculations)

[keeper1984]

i am busy with creating a access storage database and need to calculate the number of days a vehicle is in storage, i have a [date in] field and a [date out] field. i need to calculate if [date out] is empty to use today otherwise [date out] - [date in]

 

[pr2-eugin]

Do not store the "Number of Days In Storage" field. This is a calculated value and should not be stored.

More info on Calculated fields - http://allenbrowne.com/casu-14.html

So create an Unbound Text Box, with its control Source as.

= DateDiff("d", [DateIn], Nz([DateOut], Date()))

 

[CazB]

You can use the Nz function to specify what to do if a field is empty... in this case you need to tell it that if [date out] is empty, then use today's date, as follows...

DaysStorage: Nz([date out],date())-[date in]

or use DateDiff... DateDiff("d", [Date In], Nz([Date Out], Date()))

 

[keeper1984]

you are the best it works perfectly thanks i was dreading trying to put nested iif commands together

 

[pr2-eugin]

trying to put nested iif commands together

The way I look at it, Nz is nothing but a simplified IIF function, that by default test for one condition Null values. If the value in the first argument is null it will place the second argument as result.. Else just keeps the first argument.

Glad to have helped ! Good Luck !

 

[RainLover]

You can use the Nz function to specify what to do if a field is empty... in this case you need to tell it that if [date out] is empty, then use today's date, as follows...

DaysStorage: Nz([date out],date())-[date in]

or use DateDiff... DateDiff("d", [Date In], Nz([Date Out], Date()))

CazB. Although "DaysStorage: Nz([date out],date())-[date in]" may work in this instance it is not guaranteed to work in all situations.

When Date/Time is stored rather than just the Date part, your result will end up with part of a day.

I would suggest DateDiff is a better method.