Calculating Date Difference

[Banderas23]

Hi,

I have a car park booking system.
In this system, when taking a customers booking, are two fields. Departure_Date and Return_date. There is also a third Field which is number of days.

What i need is, when you select The departure and return dates, i need the Number of days field to auto populate with the number of days the customer will be gone for.

I've tried to find info on how this can be done and have seen a Datediff function. DateDiff("d",[Departure_date],[Return_date]).. However, when i use this function is does not bring up any information.

Can anyone help me get this number of days field to auto update with the difference between my two selected dates.

Thanks in advance

Antony

 

[plog]

DateDiff("d",[Departure_date],[Return_date]).. However, when i use this function is does not bring up any information.

It looks like you have used the DateDiff correctly in terms of syntax, perhaps you are using it in the wrong context. Where exactly have you put this code?

Here's where it should be--in a query. This shouldn't be in a table--you shouldn't have a field that stores this value. Instead you should have a query and this value gets calculated whenever you need it.

 

[sneuberg]

Also if you want to just display this on a form you can put it in the control source of a textbox like:

=DateDiff("d",[Departure_date],[Return_date])

Of course that textbox can't be bound to a table or query so it's just for display.

 

[The_Doc_Man]

First question: What data types are the two dates? Are they stored as text or as actual Date variables?

At least in simple terms, IF you store only dates and not times AND are using date fields, then you can do this:

ElapsedDays = CLng( CDbl(EndDate) - CDbl(StartDate) )

Then you could display this in a query or form. Note that if the dates are NOT in Date format but are in a text format such as "dd/mm/yyyy" AS TEXT, you cannot use DateDiff on the raw fields, you must encapsulate them in CDate(text) functions.

Note also that this does not distinguish between weekdays and weekends. That formula is just "raw days difference" between two dates.