Dates and bookings

[spn200286]

Just a small query for today (if you pardon the pun)

basaclly my problem is, i am creating a booking system, and i want the total for the length of stay;
attributes are

Date Arrive | Date Depart | Room Number | room price

i want to add an total column at the end (i was using the query by example builder) and tried [datedepart] - [datearrive] * [roomprice]

this however doesn't always work well, is there any other way of doing the same function? i can use SQL if that makes life easier

cheers for any response

 

[EMP]

([datedepart] - [datearrive]) * [roomprice]

^

 

[spn200286]

lol cheers, i tried that before and it didn't seem to work

ill give it another go

 

[neileg]

There a chance that ([datedepart] - [datearrive]) will return a date/time datatype. Try:
DateDiff("d",[datedepart],[datearrive]) * [roomprice]

 

[spn200286]

it still no works, no data is being displayed when i run the query at all (yes i have checked that theres data in the fields) below is a screen of the queries and my relationships in case its a problem with them

 

[neileg]

You haven't used DateDiff. Plus Total is a reserved word which can cause problems. Use TotalPrice or something like that.

 

[spn200286]

cheers, i have done this, and still no info is being shown

TotalPrice: DateDiff("d",[DateDepart],[DateArrive])*[Room_Price]

sorry for being a pain, but as far as i can tell, this should be working

 

[neileg]

Are your date fields date/time datatype or text?

 

[spn200286]

Are your date fields date/time datatype or text?

the fields are date/time (medium date inputmask format)

could that be the problem?

 

[neileg]

No, it would be a problem if they were text. Apart from having the two date fields the wrong way round, there's nothing wrong with your formula.

 

[spn200286]

well thanks for the help, its working now, tbh im not too sure what i did, but beggers cant be choosers.

i know this may be a silly question so just so i know DateDiff is the difference in dates i assume, but what is the purpose of the "d" is that just a variable which the value is assigned too? (if that makes sense)

 

[raskew]

Lookup the DateDiff() function in the help file.

"d" (representing days) is the interval that you're asking the function to return.

"m" represents months

"yyyy" represents years

Here's an example from the debug (immediate) window that shows what happens when you specify different intervals. When computing age, this becomes a tad more complicated and I won't get into that, but rather try
to demonstrate the interval argument for the function.

x = "12/1/85"

? datediff("d", x, date())
 8145 

? datediff("m", x, date())
 267 

? datediff("yyyy", x, date())
 23

Bob