Different Quarter then default

[boge]

Hi
I have a problem, I need to make quarter reports but they should be different from the default ones, for example first quarter should be from December, November and January.
The standard quarter querry is using this format:

datum By Quarter: Format$(dnevna_lista.datum;'yyyy" Q"q ')

I hope it is posible )

Thanks!

 

[RuralGuy]

It looks to me like you can subtract 1 month from the actual date and then calculate the quarters.

 

[boge]

I guess you meen first to make a querry for monthly reports then another querry to make a sum report for the months I need to ?

 

[RuralGuy]

How about creating another field in your query equal to:
MyQuarter: Format(DateAdd("m",-1,[dnevna_lista.datum]),"q")

 

[EdFred]

Change month numbers as necessary.

Public Function Quarter(Datestring As Date) As Integer
Dim MonthString As Integer
MonthString = Month(Datestring)
Select Case MonthString
    Case 1, 2, 3
        Quarter = 1
    Case 4, 5, 6
        Quarter = 2
    Case 7, 8, 9
        Quarter = 3
    Case 10, 11, 12
        Quarter = 4
End Select
End Function

Then in the query your new field can be

myField: Quarter([datefield])

 

[boge]

RuralGuy, I have to check a little more to see if the results are correct - it seems ok so far, but the problem now is that the querry does not separate the years, it shows sums of all the quarters no matter what year they are

 

[RuralGuy]

You will definately need to group on year as well.

 

[EdFred]

Change months as ncessary.

Public Function FiscalYear(Datestring As Date) As Integer
Dim MonthString As Integer
MonthString = Month(Datestring)
Select Case MonthString
    Case 1, 2, 3, 4, 5, 6
        FiscalYear = Year(Datestring)
    Case 7, 8, 9, 10, 11, 12
        FiscalYear = Year(Datestring) + 1
End Select
End Function

Public Function FiscalQuarter(Datestring As Date) As Integer
Dim MonthString As Integer
MonthString = Month(Datestring)
Select Case MonthString
    Case 1, 2, 3
        FiscalQuarter = 3
    Case 4, 5, 6
        FiscalQuarter = 4
    Case 7, 8, 9
        FiscalQuarter = 1
    Case 10, 11, 12
        FiscalQuarter = 2
End Select

End Function

This is what I do for our system. Our fiscal year runs July 1 - June 30.

 

[boge]

Change month numbers as necessary.

Public Function Quarter(Datestring As Date) As Integer
Dim MonthString As Integer
MonthString = Month(Datestring)
Select Case MonthString
    Case 1, 2, 3
        Quarter = 1
    Case 4, 5, 6
        Quarter = 2
    Case 7, 8, 9
        Quarter = 3
    Case 10, 11, 12
        Quarter = 4
End Select
End Function

Then in the query your new field can be

myField: Quarter([datefield])

This is working fine it seems, it just needs some time (about 7-8 sec) to show the results. I have more then 15000 records in that table so that is probably why it takes some time.

Both ways are working!
I will probably see more what way will be more convinient, EdFred way is much more simplier but I will check if RuralGuy solution wills show results quicker although it needs more settings.

Thanks a lot both of you for the time and for the effort