Date Queries (2002)

Topic

New Reply

Hey Gang. I have to make an access DB calculate increases in mortgage rates by quarter. In other words the calculation is done quarterly not monthly. I need a text box to trigger the calculation. I have tried This: =if datepart(“m”,[text2]) = 3 and datepart(“d”,[text2]) =31 then ([text3]*[text4])

I wanted to set up every quarter end i.e. 3-31, 6-30, 9-30 and 12-31 to automatically do the calcs.

Any suggestions??

Dan

Reply | Quote

Replies

Could you provide some more details? In particular what do you mean by “set up every quarter end i.e. 3-31, 6-30, 9-30 and 12-31 to automatically do the calcs”?

Reply | Quote

Did you try specifying “q” for the DatePart function Interval argument (“q” = Quarter)? Example from VBA Help:
[indent]

---

DatePart Function Example

The following example uses the DatePart function to specify criteria for a select query. For example, suppose you want to create a query based on an Orders table to list all orders placed in the first quarter of 1996. Assuming your Orders table has a OrderID field and an OrderDate field, you can drag the OrderID field to the first cell in the query design grid, and enter the following in the Criteria cell beneath it.

(DatePart(“q“, [OrderDate]) = 1) and (DatePart(“yyyy”, [OrderDate]) = 1996)

---

[/indent]

See VBA Help for more details on DatePart function. Recommend try using this in totals query (you’d want to Group By the quarterly interval for date field in question). Simple example using Northwind Orders table:

SELECT Orders.CustomerID, DatePart(“yyyy”,[OrderDate]) AS [Order Year], DatePart(“q”,[OrderDate]) AS Quarter, Sum([UnitPrice]*[Quantity]) AS [Quarterly Totals]
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID, DatePart(“yyyy”,[OrderDate]), DatePart(“q”,[OrderDate])
ORDER BY Orders.CustomerID, DatePart(“yyyy”,[OrderDate]), DatePart(“q”,[OrderDate]);

This query lists the quarterly Order totals for each CustomerID in Orders table. Note also use DatePart with “yyyy” to get yearly interval, then “q” for quarterly interval within each year.

HTH

Reply | Quote

Did you try specifying “q” for the DatePart function Interval argument (“q” = Quarter)? Example from VBA Help:
[indent]

---

DatePart Function Example

The following example uses the DatePart function to specify criteria for a select query. For example, suppose you want to create a query based on an Orders table to list all orders placed in the first quarter of 1996. Assuming your Orders table has a OrderID field and an OrderDate field, you can drag the OrderID field to the first cell in the query design grid, and enter the following in the Criteria cell beneath it.

(DatePart(“q“, [OrderDate]) = 1) and (DatePart(“yyyy”, [OrderDate]) = 1996)

---

[/indent]

See VBA Help for more details on DatePart function. Recommend try using this in totals query (you’d want to Group By the quarterly interval for date field in question). Simple example using Northwind Orders table:

SELECT Orders.CustomerID, DatePart(“yyyy”,[OrderDate]) AS [Order Year], DatePart(“q”,[OrderDate]) AS Quarter, Sum([UnitPrice]*[Quantity]) AS [Quarterly Totals]
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Orders.CustomerID, DatePart(“yyyy”,[OrderDate]), DatePart(“q”,[OrderDate])
ORDER BY Orders.CustomerID, DatePart(“yyyy”,[OrderDate]), DatePart(“q”,[OrderDate]);

This query lists the quarterly Order totals for each CustomerID in Orders table. Note also use DatePart with “yyyy” to get yearly interval, then “q” for quarterly interval within each year.

HTH

Reply | Quote

In further reply – not sure if previous reply addressed issue entirely – you may also want to use some user-defined functions like these examples to determine the first or last day of the quarter for a given date:

Public Function GetFirstDayOfQtr(ByRef dt As Date) As Date
GetFirstDayOfQtr = DateSerial(Year(dt), Int((Month(dt) – 1) / 3) * 3 + 1, 1)
End Function

Public Function GetLastDayOfQtr(ByRef dt As Date) As Date
GetLastDayOfQtr = DateSerial(Year(dt), Int((Month(dt) – 1) / 3) * 3 + 4, 0)
End Function

Example of use:

? GetFirstDayOfQtr(#12/29/2003#)
10/1/2003
? GetLastDayOfQtr(#12/29/2003#)
12/31/2003

For current date use Date() function:

? GetFirstDayOfQtr(Date())
1/1/2004
? GetLastDayOfQtr(Date())
3/31/2004

For more examples of date-related expressions & functions that may be useful, see this MSKB article:

ACC2000: Functions for Calculating and Displaying Date/Time Values

The examples should apply to ACC 2002 as well as ACC 2K.

HTH

Reply | Quote

In further reply – not sure if previous reply addressed issue entirely – you may also want to use some user-defined functions like these examples to determine the first or last day of the quarter for a given date:

Public Function GetFirstDayOfQtr(ByRef dt As Date) As Date
GetFirstDayOfQtr = DateSerial(Year(dt), Int((Month(dt) – 1) / 3) * 3 + 1, 1)
End Function

Public Function GetLastDayOfQtr(ByRef dt As Date) As Date
GetLastDayOfQtr = DateSerial(Year(dt), Int((Month(dt) – 1) / 3) * 3 + 4, 0)
End Function

Example of use:

? GetFirstDayOfQtr(#12/29/2003#)
10/1/2003
? GetLastDayOfQtr(#12/29/2003#)
12/31/2003

For current date use Date() function:

? GetFirstDayOfQtr(Date())
1/1/2004
? GetLastDayOfQtr(Date())
3/31/2004

For more examples of date-related expressions & functions that may be useful, see this MSKB article:

ACC2000: Functions for Calculating and Displaying Date/Time Values

The examples should apply to ACC 2002 as well as ACC 2K.

HTH

Reply | Quote