Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Queries (2002)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Queries (2002)

    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"?

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Queries (2002)

    Did you try specifying "q" for the DatePart function Interval argument ("q" = Quarter)? Example from VBA Help:
    <hr>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)<hr>

    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

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Date Queries (2002)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •