Quarterly definition

[driver7408]

Being in the Army, my database needs to define "quarters" as dates that are 3 months prior to the dates access uses. EX: oct 01 to dec 31 is qtr 1, jan 01 to mar 31 is qtr 2. I'm trying to find a way to redefine this so I can use the "q" expression instead of having to write some major complicated query to get the quarter number from the date I want. So far, the only thing i came up with is =DatePart("q",Date()+90), but this is only going to add 90 days, not put me at the first of that month, 3 months ahead. I just want to define quarter 1 as quarter 2, 2 as 3, 3 as 4, and 4 as 1. Any easy way around this?

Also, is there a way to widcard the year expression IE: #12/02/xxxx# ? So access just returns the dates that are within the specified month and days, not regarding the year?

 

[vbaInet]

So far, the only thing i came up with is =DatePart("q",Date()+90), but this is only going to add 90 days, not put me at the first of that month, 3 months ahead. I just want to define quarter 1 as quarter 2, 2 as 3, 3 as 4, and 4 as 1. Any easy way around this?

You could get the quarter and add 1 to it?
=DatePart("q",Date()) + 1

Also, is there a way to widcard the year expression IE: #12/02/xxxx# ? So access just returns the dates that are within the specified month and days, not regarding the year?

Day() and Month() functions should do it:

Day([DateField]) = Day(#12/02/2010#) AND Month([DateField]) = Month(#12/02/2010#)

 

[SOS]

I think you would need to use an IIF to add one to it. Set it to 1 if it is 4 and then just add one if it is one of the other quarters. Adding one regardless will make a 5 if in quarter 4 .

 

[vbaInet]

I wasn't quite sure what the OP wanted with that one lol.

 

[SOS]

So you could also just use the format to create a new field like this:

MyQuarter: IIf(DatePart("q",[FieldNameHere])=4,1,DatePart("q",[FieldNameHere])+1)

 

[driver7408]

Figured it out.

Not sure if this was the logical way of doing it, but i added an invisible textbox to my form that used the Dateadd formula and added 3 months to the date that was in the 'Date Issued' box. I then took the 'Quarterly' box and based its input on the date that was in that invisible modified date box.

When I put the date in the Date Issued box, after action macro adds the three months, then calculates the value in the 'Quarterly' and inputs it. Seems to work like a charm.

 

[vbaInet]

All can be done in the query.