Date and Month - No year (Fiscal Year) 2

[Tamrak]

Good afternoon,

I am coming up with an unusal query because our fiscal year has been set up differently. We need to be able to use this query for a long time. That's why we do not put the year information in there. I would like to have a query constructed, that will determine the following:

If the month and date >= 08/01 and (month and date) <=10/31, then, return value of "Quarter 1",

If the month and date >= 11/01 and (month and date) <=01/31, then, return value of "Quarter 2",

If the month and date >= 02/01 and (month and date) <=04/30, then, return value of "Quarter 3",

If the month and date >= 05/01 and (month and date) <=07/31, then, return value of "Quarter 4"

I have a field called, transaction date, which gives me the month, date and year format (mm/dd/yyyy). I would like to know how to match with this query.

For example:

Transaction Date Return Value

05/08/2005 Quarter 4
07/14/2005 Quarter 4
10/25/2005 Quarter 1

08/26/2006 Quarter 1

I am also concerned about my 2nd Quarter, because it starts in November of this year and finishes at the end of January of next year. I do not know whether the query will accept it.

Please let me know what method you think I should do to make it to work. Thank you.

 

[dhookom]

Try an expression like:
"Quarter " & Format(DateAdd("m",-7,[TransactionDate]),"q")



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[randy700]

How about...

SELECT Case Format([COLOR=blue]YourDateField[/color],"m")
   Case = 1, 2, 3:
      intQuarter = 2
   Case = 4, 5, 6:
      intQuarter = 3
   Case = 7, 8, 9:
      intQuarter = 4
   Case = 10, 11, 12:
      intQuarter = 1
End Select

Randy

 

[Tamrak]

Thank you for all of your help. The query is working fine at this moment. I do appreciate your promot response. Both of you have earned a star.

 

[lespaul]

but you didn't give them a star??? have one on me!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi