?
Solved

access 2007 create expression for Quarter date query

Posted on 2011-10-30
11
Medium Priority
?
542 Views
Last Modified: 2012-05-12
I want to sort date fields for period for a query
the date is last quarter date to the present date

What is the expression for a query for the above please
0
Comment
Question by:topUKlawyer
  • 5
  • 4
  • 2
11 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37052721
Here is a function to use:
Public Function DatePreviousQuarterLast( _
  Optional ByVal datDateThisQuarter As Date) As Date
  
  Const cintQuarterMonthCount   As Integer = 3
  
  Dim intThisMonth              As Integer
  
  If datDateThisQuarter = 0 Then
    datDateThisQuarter = Date
  End If
  intThisMonth = (DatePart("q", datDateThisQuarter) - 1) * cintQuarterMonthCount
  
  DatePreviousQuarterLast = DateAdd("q", 0, DateSerial(Year(datDateThisQuarter), intThisMonth + 1, 0))

End Function

Open in new window


Thus your criteria could be (to include both dates):

  where [YourDateField] between DatePreviousQuarterLast() and Date()

or to exclude these:

  where [YourDateField] > DatePreviousQuarterLast() and  [YourDateField] < Date()

/gustav
0
 
LVL 85
ID: 37052729
There is no builtin function for that. You would need to "assign" a period, based on the Month value of your Date field:

Period: SWITCH((Month(YourDateField)>9, 4, Month(YourDateField)>6, 3,  Month(YourDateField)>3, 2, Month(YourDateField)>0,1)

This would add a new field named "Period" to your query.

0
 
LVL 85
ID: 37052730
Sorry ... didn't read this one correctly.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:topUKlawyer
ID: 37052758
Thanks guys,

have I asked accurately?

If say todays date is 30 October, which it is,  i need to select dates between 1st October  and now. should I have said present quarter date and now?

if todays date were 30 Sept then select 1st July to 30th Sept?

0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37052848
Then use this function:
Public Function DateThisQuarterFirst( _
  Optional ByVal datDateThisQuarter As Date) As Date
  
  Const cintQuarterMonthCount   As Integer = 3
  
  Dim intThisMonth              As Integer
  
  If datDateThisQuarter = 0 Then
    datDateThisQuarter = Date
  End If
  intThisMonth = (DatePart("q", datDateThisQuarter) - 1) * cintQuarterMonthCount

  DateThisQuarterFirst = DateSerial(Year(datDateThisQuarter), intThisMonth + 1, 1)

End Function

Open in new window


/gustav
0
 

Author Comment

by:topUKlawyer
ID: 37053029
Thanks, if I might ask, what does the integers 0 through to 3 represent exactly?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37053054
cintQuarterMonthCount  is the month count for a quarter (= 12 / 4)

datDateThisQuarter is 0 (the default numeric value of data type Date) if no value is supplied for this parameter.

/gustav
0
 

Author Comment

by:topUKlawyer
ID: 37053385
Thanks, just to be sure what my query criteria would be?

0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 37053397
As I wrote above:

  Between DateThisQuarterFirst() And Date()

/gustav
0
 

Author Closing Comment

by:topUKlawyer
ID: 37053555
sounds good. will try and if any probs report back,

many thanks
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37053632
You are welcome!

/gustav
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question