Senseless, but please assist

[djclntn]

I have a field which contain the “Month/Year” eg. (1/2011)


*I don’t know how to convert DATES into month/year

Anyway, I need to create a query that will display the month/yr in QUARTERS for Jan – Dec. Does anyone know what expression I need to accomplish this tasks?

Thanks…

[boblarson]

1. You can convert dates into month/year simply by using
Format([DateFieldNameHere], "m/yyyy")

2. You can get the quarter from the date field by using
Format([DateFieldNameHere], "q")

3. If you don't have the date field available and need to convert from the m/yyyy then it is a little more complex:

Format(DateSerial(Mid([MonthYearFieldNameHere], Instr(1,[MonthYearFieldNameHere],"/") + 1), Left([MonthYearFieldNameHere], Instr(1, [MonthYearFieldNameHere], "/") - 1), 1), "q")

[djclntn]

I followed your advice changing my MNTH/YR Field to Date/Time

But I tryied the expression for quartly & it didn't work:
QRTLY: ([MNTH/YR],"q")

[djclntn]

Neither of your examples are working for me

MNTH/YR: [(TDate], "m/yyy")

[boblarson]

Neither of your examples are working for me

MNTH/YR: [(TDate], "m/yyy")

Looks like something missing (if you read my post a little more carefully you might find this part:

Format()

So

MNTH/YR: Format([TDate], "m/yyy"))

[djclntn]

I'm really sorry & stupid for overseeing, "FORMAT" I once heard & like the saying, "I'm not the sharpest knife in the draw." That'sme, to say the least...

Anyways, I revised the expession to:
Format([TPDATE],"m/yyyy"). In the "Total row I choose, Expression, right?

I'm being asked for a Parameter value for TPDATE...?

[boblarson]

Is TPDATE the actual name of your field, not a control name on a form is it? You are working in a query right? And you have TPDATE as one of your fields in the query?

[djclntn]

Sorry for my delayed response. Actually, the name of my date field isTDATE. I'm working on another database where I need to come up with some quarterly figures. I started to create a "qrtly" field in the table, filter the month eg. (Jan-Mar), go into Excel (if I have 100-records between Jan-Mar) I click (in Excel) A100, type the #1, copy it up to A1, then i have ythe # "1" 100 x's, then I copy/pated the 100 "1" in my QRTLY Field -- so fort for quarterly 2, 3, etc. I apologize if I have you head spinner in complete confusion. Yes, I'm making 10 x's more work for myself, however my dumb brain is not able to understand (get) the expression/formula for calcuulating the quarterly.


It' very frustrating!