calculated field in query

[Stephanie]

Hi. Boy, am I Access-rusty!

I have a field: [LoggedMonth] that has values 1-12

I want to convert to quarters where:
[LoggedMonth] in (1,2,3) = Q1
[LoggedMonth] in (4,5,6) = Q2
[LoggedMonth] in (7,8,9) = Q3
[LoggedMonth] in (10,11,12) = Q4

I want to have a form that allow users to select from Q1, Q2, Q3, Q4

I would appreciate advice in how to "covert" to quarter.

Thanks,
Stephanie

 

[Klatuu]

Use the datepart function

"Q" & DatePart("Q", [LoggedMonth])

 

[Stephanie]

Interesting!
That didn't quite work in that
[LoggedMonth] = 1 retuns Q4 rather than Q1
[LoggedMonth] = 2, 3 correctly returned Q1
[LoggedMonth] = 4, 5, 6, 7, 8, 9, 10, 11, 12 (rather than Q2, Q3, Q4 as
required)

Any other suggestions?
Thanks,
Stephanie

Use the datepart function

"Q" & DatePart("Q", [LoggedMonth])
--
Dave Hargis, Microsoft Access MVP

Hi. Boy, am I Access-rusty!

I have a field: [LoggedMonth] that has values 1-12

I want to convert to quarters where:
[LoggedMonth] in (1,2,3) = Q1
[LoggedMonth] in (4,5,6) = Q2
[LoggedMonth] in (7,8,9) = Q3
[LoggedMonth] in (10,11,12) = Q4

I want to have a form that allow users to select from Q1, Q2, Q3, Q4

I would appreciate advice in how to "covert" to quarter.

Thanks,
Stephanie

 

[Klatuu]

That should not be correct. I just tested it using 1 as the month, and it
returned 1 as it should. I have never experienced the behavior you are
describing.

--
Dave Hargis, Microsoft Access MVP

Interesting!
That didn't quite work in that
[LoggedMonth] = 1 retuns Q4 rather than Q1
[LoggedMonth] = 2, 3 correctly returned Q1
[LoggedMonth] = 4, 5, 6, 7, 8, 9, 10, 11, 12 (rather than Q2, Q3, Q4 as
required)

Any other suggestions?
Thanks,
Stephanie

Use the datepart function

"Q" & DatePart("Q", [LoggedMonth])
--
Dave Hargis, Microsoft Access MVP

Hi. Boy, am I Access-rusty!

I have a field: [LoggedMonth] that has values 1-12

I want to convert to quarters where:
[LoggedMonth] in (1,2,3) = Q1
[LoggedMonth] in (4,5,6) = Q2
[LoggedMonth] in (7,8,9) = Q3
[LoggedMonth] in (10,11,12) = Q4

I want to have a form that allow users to select from Q1, Q2, Q3, Q4

I would appreciate advice in how to "covert" to quarter.

Thanks,
Stephanie

 

[Marshall Barton]

That should not be correct. I just tested it using 1 as the month, and it
returned 1 as it should. I have never experienced the behavior you are
describing.

Dave,
PMJI. but DatePart needs a date field, not a month number.
DatePart("q", 1) is Q4 because 1 is the date 12/31/1899.

Stephanie,
If you have month numbers 1 thru 12, the quarter is:
"Q" & LoggedMonth \ 3 + 1

 

[Klatuu]

Doh!

Right you are.
Guess I better brew another pot of coffee.

 

[Klatuu]

Sorry, Stephanie. My mistake.
If you don't have the full date, just the month, this will work:
"Q" & Choose([LoggedMonth],1,1,1,2,2,2,3,3,3,4,4,4)
--
Dave Hargis, Microsoft Access MVP

Interesting!
That didn't quite work in that
[LoggedMonth] = 1 retuns Q4 rather than Q1
[LoggedMonth] = 2, 3 correctly returned Q1
[LoggedMonth] = 4, 5, 6, 7, 8, 9, 10, 11, 12 (rather than Q2, Q3, Q4 as
required)

Any other suggestions?
Thanks,
Stephanie

Use the datepart function

"Q" & DatePart("Q", [LoggedMonth])
--
Dave Hargis, Microsoft Access MVP

Hi. Boy, am I Access-rusty!

I have a field: [LoggedMonth] that has values 1-12

I want to convert to quarters where:
[LoggedMonth] in (1,2,3) = Q1
[LoggedMonth] in (4,5,6) = Q2
[LoggedMonth] in (7,8,9) = Q3
[LoggedMonth] in (10,11,12) = Q4

I want to have a form that allow users to select from Q1, Q2, Q3, Q4

I would appreciate advice in how to "covert" to quarter.

Thanks,
Stephanie

 

[Piet Linden]

Interesting!
That didn't quite work in that
[LoggedMonth] = 1 retuns Q4 rather than Q1
[LoggedMonth] =  2, 3 correctly returned Q1
[LoggedMonth] = 4, 5, 6, 7, 8, 9, 10, 11, 12 (rather than Q2, Q3, Q4 as
required)

Any other suggestions?
Thanks,
Stephanie

Use the datepart function

"Q" & DatePart("Q", [LoggedMonth])

Hi. Boy, am I Access-rusty!
I have a field: [LoggedMonth] that has values 1-12
I want to convert to quarters where:
[LoggedMonth] in (1,2,3) = Q1
[LoggedMonth] in (4,5,6) = Q2
[LoggedMonth] in (7,8,9) = Q3
[LoggedMonth] in (10,11,12) = Q4
I want to have a form that allow users to select from Q1, Q2, Q3, Q4
I would appreciate advice in how to "covert" to quarter.
Thanks,
Stephanie

failing that, if you use something like
"Q" & ([LoggedMonth]+2)\3

but DatePart should definitely work...

 

[Klatuu]

I like that, Piet
--
Dave Hargis, Microsoft Access MVP

Interesting!
That didn't quite work in that
[LoggedMonth] = 1 retuns Q4 rather than Q1
[LoggedMonth] = 2, 3 correctly returned Q1
[LoggedMonth] = 4, 5, 6, 7, 8, 9, 10, 11, 12 (rather than Q2, Q3, Q4 as
required)

Any other suggestions?
Thanks,
Stephanie

Use the datepart function

"Q" & DatePart("Q", [LoggedMonth])

"Stephanie" wrote:

Hi. Boy, am I Access-rusty!

I have a field: [LoggedMonth] that has values 1-12

I want to convert to quarters where:
[LoggedMonth] in (1,2,3) = Q1
[LoggedMonth] in (4,5,6) = Q2
[LoggedMonth] in (7,8,9) = Q3
[LoggedMonth] in (10,11,12) = Q4

I want to have a form that allow users to select from Q1, Q2, Q3, Q4

I would appreciate advice in how to "covert" to quarter.

Thanks,
Stephanie

failing that, if you use something like
"Q" & ([LoggedMonth]+2)\3

but DatePart should definitely work...

 

[Stephanie]

Hmm. First I have to grin that you know "1899"!
Thanks all for the comments- so appreciate them!

All is well except month 12 which gives Q5!
However, Piet's crazy cool equation worked.

Thanks,
Stephanie

 

[Stephanie]

Perfect!
Thanks so much for your help!

Interesting!
That didn't quite work in that
[LoggedMonth] = 1 retuns Q4 rather than Q1
[LoggedMonth] = 2, 3 correctly returned Q1
[LoggedMonth] = 4, 5, 6, 7, 8, 9, 10, 11, 12 (rather than Q2, Q3, Q4 as
required)

Any other suggestions?
Thanks,
Stephanie

Use the datepart function

"Q" & DatePart("Q", [LoggedMonth])

"Stephanie" wrote:

Hi. Boy, am I Access-rusty!

I have a field: [LoggedMonth] that has values 1-12

I want to convert to quarters where:
[LoggedMonth] in (1,2,3) = Q1
[LoggedMonth] in (4,5,6) = Q2
[LoggedMonth] in (7,8,9) = Q3
[LoggedMonth] in (10,11,12) = Q4

I want to have a form that allow users to select from Q1, Q2, Q3, Q4

I would appreciate advice in how to "covert" to quarter.

Thanks,
Stephanie

failing that, if you use something like
"Q" & ([LoggedMonth]+2)\3

but DatePart should definitely work...

 

[Marshall Barton]

In Access, the date 0 is 30 Dec 1899 (I don't know why). We
see this a lot when people only enter the time of day
instead a date and time.

Sorry about the missing -1, I had intended it to be:
"Q" & (LoggedMonth-1) \ 3 + 1
but Piet's expression is a little better so this post is
only a correction.