Calculations on date fields

[shopoholic]

Still need help calculating difference between dates

Hi there

I need to perform a query that works out how far in advance people book holidays. I have the arrival date and also the booking date, and I think I should be using DateDiff. However, I tried to put it in an existing query and it hasn't worked:

SELECT Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings, DateDiff("d", [Booking Date], [Arrive])
FROM Bookinfx
GROUP BY Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date])
HAVING (((Year([Start Date]))=2004));

Any help most gratefully received!

 

[Pat Hartman]

You need to use an aggregation function for the date difference:

SELECT Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings, Avg(DateDiff("d", [Booking Date], [Arrive])) As AvgDays, Min(DateDiff("d", [Booking Date], [Arrive])) As MinDays, Max(DateDiff("d", [Booking Date], [Arrive])) As MaxDays
FROM Bookinfx
GROUP BY Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date])
HAVING (((Year([Start Date]))=2004));

 

[shopoholic]

Thank you for your suggestion. I tried it but the only problem is that when I run it, it asks me for the arrival day - any idea why it's doing that???

 

[Pat Hartman]

That must not be its actual column name. Check the table to see what its name really is.

 

[shopoholic]

Thanks!

Thanks again Pat, its working now and interestingly highlighted a problem with people entering dates incorrectly, so has been useful in two ways!

 

[Sakhikid]

Hi, I am having a similar problem. I have a database where i have records based on dates. I need to bold all the dates that are over a month old. Is there a way I can do that in my query or report??

 

[mresann]

First, let's create the formula. Since you are looking for all dates more than a month old, I assume the following is correct: For July 9 2005, you are looking at all dates from June 9 and before. Therefore, the date would be caluclated as follows:

DateSerial(Year(Now), Month(Now)-1, Day(Now))

You can even subract a day if you wish, to make it June 8 as the last day, for instance, by this formula: Day(Now) - 1.

If you have Access 2002 and above, you can use Conditional formatting (I'm not sure if Access 2000 has it or not). Do the following

If you are using Access with Conditional Formatting, then you can just set it up with the wizard. In design view, click on the text box on the report you wish to format. Then click on menu item "Format", then "Conditional Formatting". When you see the box pop up, under Condition 1:

1. Leave "Field Value Is" combo box as is
2. Change "between" to "less than"
3. In the expression box, paste in the following formula:

DateSerial(Year(Now), Month(Now)-1,Day(Now))

Now change the formatting you wish to occur for the conditioning, including font weight and text color.

If you don't have conditional formatting, then you have to program this change in VBA under the Form Detail section.

 

[Sakhikid]

Got it. Thanx!!

I just put in the condition Date()-30 in the expression box. But my second question is can I apply it to the entire record. Bold the entire record if this condition applies to the date part of the record?? Or it can only be done for that field. Coz I couldn't find anything in the report's conditional formatting that would link all the fields of that record to change based on a condition.

 

[mresann]

You have to use the same conditional formatting for each control you want formatted. You have to do each control separately. Just use the same expression in each control.

By the way, you used the term "over a month old", not "over 30 days old" in your original request, that is why I used the DateSerial method in my example. If all you require is 30-day period, your expression works well.

 

[Sakhikid]

I have other fields with names, other dates and account numbers which don't seem to change. How do you relate those to the one date field and make them change if it is related to that one particular date field.

 

[mresann]

Oops, sorry. I have given this type of help to several other people that I forgot that you had multiple controls to format. My bad.

First, get the name of the control that contains the date you want to compare. NOT the field, but the name of the control. By the way, this is a good time to check all the control names on the subform. If any control name matches the name of the field that is bound to the control, then change the name of the controld. For instance, if the name of your date field is "StartDate", then your control should NOT be named "StartDate". The best way is to use the three letter prefix "txt" in front of the control name, and call it "txtStartDate".

OK, now that all the controls on your subform are properly named. Since "txtStartDate" holds the date that you want to check, perform the following step for EACH control you want to conditionally format:

1. Click on the control.
2. Click Menu item Format, then Conditional Formatting...
3. Under Condition 1, change the first combo box from "Field Is" to "Expression Is". The two controls to the right will change into a single text box.
4. Enter this expression: txtStartDate.Value < Date()-30
5. Select the formatting options you want the control to have if the expression is true.
5. Click OK.

Repeat steps 1 through 6 for EACH control you want formatted.

 

[Sakhikid]

It worked!!!
Thanx alot.