Solved

# access 2007 create expression for Quarter date query

Posted on 2011-10-30
538 Views
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
Question by:topUKlawyer

LVL 48

Expert Comment

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
``````

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 84

Expert Comment

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)

0

LVL 84

Expert Comment

Sorry ... didn't read this one correctly.
0

Author Comment

Thanks guys,

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 48

Expert Comment

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
``````

/gustav
0

Author Comment

Thanks, if I might ask, what does the integers 0 through to 3 represent exactly?
0

LVL 48

Expert Comment

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

Thanks, just to be sure what my query criteria would be?

0

LVL 48

Accepted Solution

As I wrote above:

Between DateThisQuarterFirst() And Date()

/gustav
0

Author Closing Comment

sounds good. will try and if any probs report back,

many thanks
0

LVL 48

Expert Comment

You are welcome!

/gustav
0

## Featured Post

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …