Separate a date field

[EagerBeaver]

I need to seperate a date field in a table. The order dates are entered as "19970101" so January 1, 1997 is entered as this. I need to make a report that will group my orders by years / months and even weeks. I will need to make charts and graphs on there. I need to know how I can break up the date field into month day and year.

Please help.


Thank you

 

[DjangMan]

Hi EagarBeaver,

Here's a really ugly looking formula that will do the trick. It assumes turning the field value into a number.

[tt]
NumberVar vyear:= Truncate({date field}/10000);
NumberVar vmonth:= Truncate(({date field}-vyear*10000)/100);
NumberVar vday :=Remainder({date field},100);
//convert to date
Date(vyear,vmonth,vday)
[/tt]

DjangMan

 

[EagerBeaver]

I understand the formula but where do I place it? In the formula field? I want to group by the date...

How do I put the formula into action...

step by step...


Thanks for all your help

 

[mbarron]

Another formula option:

Date (tonumber(totext(datefield,0,"&quot[1 to 4]),
tonumber(totext(datefield,0,"&quot[5 to 6]),
tonumber(totext(datefield,0,"&quot[7 to 8]) )

Create a Formula Field and replace "datefield" with the appropriate date field. Then use the Formula Field you created for your calculations and grouping.

 

[idle]

Another formula option:
DateValue({field}[1 to 4] + '/' +
{field}[5 to 6] + '/' +
{field}[7 to 8])

 

[VE]

Why not make a formula with just this:

pwformatdate({field})

Then group on that formula field.

VE

 

[kenhamady]

Whichever option you use you add this as a new formula (Insert, field Object). Then you group on the formula. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[idle]

pwformatdate({field}) is specific to ACCPAC for Windows, more specifically those with UFLCAPW.DLL installed in the Crystal directory.