Need to show oct - Dec as 1st Qtr

[Brian62]

In my query I need to show Oct - Dec as "1 Qtr 2010" Jan - Mar as "2 Qtr 2011" etc

The code I am using now gives me 2010 as the fourth quarter.

Here is the code I am using: Qtr: Format$([Dateofaudit],"q"" Qtr - ""yyyy",0,0)

The return is: "1 Qtr - 2011" etc.

I tried adding -1 at the end of the code and in the code but won't work.

 

[raskew]

Hi -

Check-0ut this post: http://www.access-programmers.co.uk/forums/showthread.php?t=72063&highlight=quarter

Best wishes - Bob

 

[Brian62]

Sorry but that is not what kind of query I need. In the query datasheet view this is what I need to show:

Qtr = Column name

1 Qtr - 2011
2 Qtr - 2011

Remember that data it is getting is 10/1/2010 - 12/1/2010 which should be 1 Qtr -2010; 1/1/2011 - 3/1/2011 = 2 Qtr - 2011, etc....

It needs to be listed in the datasheet view since it is being used in a report.

 

[stopher]

Just add 3 months to the date before you format it, like this:

Qtr: Format(DateAdd("m",3,[Dateofaudit]),"q"" Qtr - ""yyyy",0,0)

hth
Chris

 

[Brian62]

Thanks for the code! There is one small problem with it. The date I have is 12/1/2010 and with the code it gives me a return of "1 Qtr - 2011". It needs to show "1 Qtr - 2010". The rest of the returns need to show "2 Qtr - 2011" for Jan - Mar, etc. Is there a way to alter the code to give me what I need?

 

[Brianwarnock]

How about
datepart("q",dateadd("m",3,date)) & " Qtr - " & year(date)

Brian

 

[Brian62]

Worked perfectly! Thanks!!!

 

[Brian62]

Now I am trying to show just the last four quarters. With the script I have now I am showing five quarters in the graph. I only need to show the last four quarters or calendar year. How would I change this script to what I need to show?

Qtr: DatePart("q",DateAdd("m",3,[dateofaudit])) & " Qtr - " & Year([dateofaudit])

Note: Each time the quarter changes I still will need to show only the last four.

These are the other scripts that are part of my query but should have nothing to do with my problem.

(Year([DateofAudit])+DatePart("q",[DateofAudit]))

and

Findings: Sum(IIf(Year([dateofaudit])=2011,[FindingTotal],Null))

This is how it shows in the query. I don't need "1 Qtr - 2010" to show.

Qtr
1 Qtr - 2010
1 Qtr - 2011
2 Qtr - 2011
3 Qtr - 2011
4 Qtr - 2011

 

[Brianwarnock]

You have opened a new ball game.
You cannot do what you want with a little tweak of the formula, you need to make the Selection of the data based on which records you want to process, I guess that it would be something like

Dateinrecord>datadd("y",-1,[datetoauditto])

However more of concern is the idea that 12/12/2011 sat would vary from 3rd qtr 2011 through 2nd 1st and finally, no hang on that's not right.!

Personally I think that you should label your qtrs properly as
2010 - Qtr 4
2011 - Qtr 1

etc and as you can see this would still produce a correct sort order and be a lot more meaningfull.

Brian

 

[crxftw]

Isn't crosstab query a solution so you can pivot the quarters as column headers?

 

[Brian62]

I tried to use your new script... Dateinrecord>datadd("y",-1,[datetoauditto])
but I could not get it to work. It doesn't recognize "Dateinrecord" in the query.

Also, I can't get the labels as you suggest to show properly. "2011 - 1 Qtr"

 

[Brian62]

I played around with the script and this is what I cam up with but I only need to show the last 4 quarters. This does show 2011 - Qtr 1

Qtr: Format(DateAdd("m",0,[Dateofaudit]),"yyyy"" - Qtr ""q",0,0)

 

[Brian62]

I used this to get what I want. >"2010 - Qtr 4" in the criteria section.

 

[Brianwarnock]

I am glad that you have it working.

I am sorry that I did not make my earlier posts clearer, and that I did not get back to you , my friends know that my time on here is a bit erratic due to domestic circumstances.

May I comment on a few things that may help you go foreward.

Firstly you do not need the Dateadd as you are adding zero.
This
Qtr: Format(DateAdd("m",0,[Dateofaudit]),"yyyy"" - Qtr ""q",0,0)
could be
Qtr: Format([Dateofaudit],"yyyy"" - Qtr ""q")

The rest of this post notes changes that you might want to consider

This

you need to make the Selection of the data based on which records you want to process, I guess that it would be something like

Dateinrecord>datadd("y",-1,[datetoauditto])

was not intended for a direct copy, I guess your Dateinrecord is Dateofaudit and the "datetoauditto" was for a parameter input either via a pop up prompt or better from a form.
This was to select the data prior to prcoessing rather than after which is the method you have chosen, nothing wrong with that if little processing takes place and maybe easier in some cases.

I would urge you to use a parameter rather than hard coding which must be changed for each run.
I would use a Form and I would have 2 controls a from and to which would allow complete flexibility and future proving. I would also use Combo boxes with pre entered values to be selected for normal use, this minimises failure due to typos, they can be over ridden if it is ever necessary.

The criteria would then be
>=forms!yourformname!startcombo And <=Forms!yourformname!endcombo

The query would be run from a command button on the Form.

hope this helps , the forum has examples of the use of query by form and combos, and you can always ask specific questions.


Brian

 

[Brian62]

Thanks. Your new shorter code worked well.