Months with No Date

[tmumaw]

Good morning everyone,

I have this table which has monthly buckets for debits, credits and year.  The different buckets represent the months. Something like this:

C01 C02  C03  C04  C05  C06  C07  C08  C09  C10  C11  C12   D01  D02  D03  D04  D05  D06  D07  D08  D09  D10  D11  D12  YEAR

My report need to be formatted something like this:

Jan        Credit     Debit   Bal

Feb.......

Mar.......

Etc......

How can I get each bucket to represent a month ?

Thanks

[sunny_talwar]

May be this

Month(Date#(PurgeChar(FieldName, 'CD'), 'M')) as Month

[tmumaw]

Each one of those fields at the top are months

C01  jan

D01  jan

C02  Feb

D02  Feb

[sunny_talwar]

Yes, I get that.... and I am guessing your data looks like this

FieldName

C01

D01

C02

D02

C03

.

.

.

Right?

[tmumaw]

No.  My data looks like this.  One record contains all months.

C01 C02  C03  C04  C05  C06  C07  C08  C09  C10  C11  C12   D01  D02  D03  D04  D05  D06  D07  D08  D09  D10  D11  D12  YEAR

[sunny_talwar]

Then may be you need to use The Crosstable Load to transform your data so that C01 and D01 can be changed to Jan and so on....

[shraddha_g]

date(date#(right(Fieldname,2),'MM')) as MonthStart

Monthname(date(date#(right(Fieldname,2),'MM'))) as Month-Year,

Month(date(date#(right(Fieldname,2),'MM'))) as Month