Month and Year Extraction

[peterpater]

Crystal Version :11.0
Database : Access 2000

Hello all:
I need to extract only the month and year from a date in my Access table which is formatted as 04/19/06 i.e mm/dd/yy.

I only require to extract the Month and the Year so I can group them accordingly.

I tried using the formula below (Crystal Syntax):

Monthname(month(table.myAccessDateField)&"-"&Year (table.myAccessDateField).

The result is June-2,006.00

I would like the result to be obviously June-2006.

Thank you in advance for all your assistance in this matter.

 

[synapsevampire]

Just place the date field as the group and select For Each Month.

Then you can right click the field and select format field->Customize and get th format you need without code.

Here's how you resolve your code though:

Monthname(month(table.myAccessDateField) & "-" &
totext(Year (table.myAccessDateField),0,"")

It won't sort properly though.

-k

 

[peterpater]

Thanks!

 

[peterpater]

How would I format the field so that I can group it by the Month-Year only.

If I directly change the format in the field, it gets grouped by dates. I want to group them by Month-Year.

Thanks in advance.

 

[peterpater]

How would I manipulate the date (mm/dd/yy) just so that I can extract only the Month-Year and group it?

 

[synapsevampire]

You don't manipulate it...

At the time of grouping on your date field you'll see that it offers an option of Group By Month.

That means that it groups by the month and year. And don't be confused by Crystal's default otuput format of mm/dd/yy, that doesn't mean that it's stored that way and has nothing to do withg the solution you're after.

Please try the solution as suggested.

-k

 

[peterpater]

Thanks it worked. It was simple. I guess I am a newbie!

 

[synapsevampire]

Nae worries, it isn't intuitive and there's much more to database programming and Crystal than most realize.

-k