calculated field in query


S

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
 
Ad

Advertisements

S

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


Klatuu said:
Use the datepart function

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


Stephanie said:
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
 
K

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


Stephanie said:
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


Klatuu said:
Use the datepart function

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


Stephanie said:
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
 
M

Marshall Barton

Klatuu said:
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
 
Ad

Advertisements

K

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


Stephanie said:
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


Klatuu said:
Use the datepart function

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


Stephanie said:
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
 
P

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

Klatuu said:
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...
 
K

Klatuu

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


Piet Linden said:
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

Klatuu said:
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...
 
S

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
 
S

Stephanie

Perfect!
Thanks so much for your help!

Piet Linden said:
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

Klatuu said:
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...
 
Ad

Advertisements

M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top