returning current quarter from yyyymm short text field

[mindbender]

Hello gurus,



I have a short text field that contains year and month in the following format yyyymm (ex 201701) I am trying to use that field to return data for the current quarter. Basically if the actual date is April 12, 2017 I would like all data from Q2 (Apr-Jun) returned.

I have been playing with date part q and can't seem to get it to work.
Any ideas?

[andy49]

Datepart("q",datefield) should work. What have you currently got


Sent from my iPhone using Tapatalk

[mindbender]

I see weird results though. I added the month column as well to check the quarter field and this is what I see

Quarter (derived from above) Month
2 11
2 8
1 6
1 4
2 12

I would see month 12 as being Q4, month 8 as being Q3 etc.

[andy49]

Is datefield actually a date field? Or a text field storing 201701?

Does month (datefield) return the correct month?


Sent from my iPhone using Tapatalk

[CJ_London]

to determine the quarter from your datenum of yyyymm use

(val(right(datenum,2))\4)+1

note the \

for a comparison in a criteria for example

WHERE (val(right(datenum,2))\4)+1=datepart("q",date())

[mindbender]

It is a short text field

[andy49]

Then Ajax method is perfect. Datepart only works on a date field.


Sent from my iPhone using Tapatalk

[mindbender]

(Val(Right([YEAR_MONTH_NUM],2))\4)+1

returns data like this
Quarter Month
3 11 this should be Q4
3 8 this is correct
2 7 this should be Q3

some look right and others are off

[andy49]

Try using int in front of first bracket


Sent from my iPhone using Tapatalk

[mindbender]

Int(Val(Right([YEAR_MONTH_NUM],2))\4)+1

Tried this

same results as above. Some right some not

[andy49]

Ok try fix instead of int


Sent from my iPhone using Tapatalk

[mindbender]

everyone is correct except the following

Q M
2 7 should be Q3
3 10 should be Q4
3 11 should be Q4

it is very strange

[andy49]

Use fix but 3 instead of 4


Sent from my iPhone using Tapatalk

[mindbender]

I did this and it works

update query to change the text field yyyymm to a date field using the same 2 numbers for day as month since I really only care about month and year

then used this as criteria

Year([YEAR_MONTH_NUM])=Year(Now()) And DatePart("q",[YEAR_MONTH_NUM])=DatePart("q",Now())

Thanks for all your help on this....led me down the right path as always