access 2007 create expression for Quarter date query

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

Select allOpen 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

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.

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

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?

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

Select allOpen in new window

/gustav

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

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

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

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

many thanks

You are welcome!

/gustav