Semi Anually

By semi-annually you mean quarterly? Use DatePart

Expr1: DatePart("q",Date())

No, every six month

if we start from april then it will add the numbers from April to September

<<I created corsstab query but there is no option for semi anuallay.>>

  You'd have to write a function to return that.  And by semi-annual, I'm assuming you mean two periods in a year; dates before 6/30 and those falling after.

JimD.

You may use (group by) this function - or just the core of it:

Public Function Semiyear( _
  ByVal datDate As Date) _
  As Integer

' Returns 1 if datDate falls in first semiyear of the year of datDate.
' Returns 2 if datDate falls in second semiyear of the year of datDate.

' 2007-01-06. Cactus Data ApS, CPH.

  Const cintMonthsPerSemiyear As Integer = 6
  
  Dim intSemiyear             As Integer
  
  ' No specific error handling.
  On Error Resume Next
  
  intSemiyear = -Int(-Month(datDate) / cintMonthsPerSemiyear)
  
  Semiyear = intSemiyear
  
End Function

Select allOpen in new window

/gustav

The data starts from April 2011 and end 2016. I want to run the report so I can group the data semi anually. I did it the simple way which I grouped the date quarterly then I added every two quarter together to get every 6 months.

Let me try again . . .

My first attempt only counts the 'Month' part without regard to the day of the month.  I am assuming that you want the six periods to be grouped starting from Now() - the current date.  If you want to start at a specific date, replace Now() with the date.  eg.  #01 Apr 2011#


Public Function SixMonthGroup(datDate As Date) As Integer

    'There aren't any limits on the [datDate] processed.
    'Eg.  If [datDate] < Now(), a zero or negative may be the result
   
    Dim MDiff As Integer
   
    MDiff = DateDiff("m", Now(), datDate) - IIf(DatePart("d", datDate) < DatePart("d", Now()), 1, 0)

    SixMonthGroup = Int(MDiff / 6) + 1
   
End Function

This returns a group values for dates greater than Now().

If Now() = #02 Mar 2011#

The first 6 months returns a 1 (#02 Mar 2011# to #01 Sep 2011#)
 the next 6 months returns a 2,  (#02 Sep 2011# to #01 Mar 2012#)
the third range of 6 months returns a 3  (#02 Mar 2012# to #01 Sep 2012#)
etc.

Then you can use the SixMonthGroup number to group the records.

J.

A 3rd try . . ., now that I better understand what you want (I think)

Does this do it?

Public Function SixMonthGroup(datDate As Date) As Integer

    'There aren't any limits on the [datDate] processed.
    'Eg.  If [datDate] < #01 Apr 2011#, a zero or negative may be the result
   
    Dim MDiff As Integer
    Dim SDate As Date
   
    SDate = #4/1/2011#
   
    MDiff = DateDiff("q", SDate, datDate)

    SixMonthGroup = Int(MDiff / 2) + 1
   
End Function

J.