Character (text) field to date field

[nigelot]

Hi
I am trying to create a report using Crystal Reports 8.5 and Act! 2000.

The field in Act! is a character field but contains dates and text ie: 01/02/02 P (P stands for Paid)

The Reports I am trying to produce with this field are based around dates. ie how many jobs are being done on each day in Feb 2002.

Is there a way for Crystal to convert the text into date format. Or any other work around. It would be a real pain to change the field to date in Act!
thanks in advance
Nigel
UK

 

[dgillz]

Cdate(left({fieldname},8)) should work. Read the help on the Cdate function if this gives you an error or returns unexpected results.

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[nigelot]

Thanks for your reply
The field name is Aer Date, so would this be
(left({Aer Date},8))
Also where do I enter this,
thanks
Nigel

 

[nigelot]

I have gone Insert, field object, formulae fields, New

I have created a field Aeration Date and entered

Cdate(left({Contact.Aer Date},8))

When I add field to report and preview I get 'Bad date format string'

Am I missing something?

Thanks
Nigel

 

[nigelot]

I've got it to work with (left({Contact.Aer Date},8))

However sorting sorts by first two digits sort first so I am gettin 01/02/02 & 01/02/01 together

thanks

Nigel

 

[dgillz]

Nigel-

I tested the following formula without errors:

cdate(left("01/01/02 P",8))

maybe this field is not a text field afterall. If you look at this database field in the field explorer, and click on the show data types button in the upper left, what does it say the data type is? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[nigelot]

It says- String (50)

Nigel

 

[dgillz]

Nigel,

maybe it has spaces padded to the left of the string. Try this to get rid of any spaces:
cdate(left(trim({Contact.Aer Date}),8)) Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[nigelot]

If I ommit 'cdate' it works ie
(left(trim({Contact.Aer Date}),8))
or cdate(left("01/01/02 P",8))
works......

But it sorts by first/second digit ie

01/02/02
01/01/01
01/00/01
02/01/02

etc

Any way for the information to be put in day, month, year *order*
thanks
Nigel

 

[nigelot]

It might be worth mentioning there is text in some of the contacts field in Act, in addition to dates ie the words 'Not Booked'

thanks

Nigel

 

[dgillz]

Nigel-

I think I understand the error now, but I still don't have a solution. Are you expressing January 31, 2002 as 01/31/02 or 31/01/02?

I believe there may be a regional setting for dates as many europeans express them DD/MM/YY versus us yanks as MM/DD/YY.

At worst case you could build a new string using left, right and mid functions in the yankee mm/dd/yy way and then use Cdate().

Let me know if this is your solution. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

It might work without the Cdate, but it isn't going to convert it to a date. If there are some entries that don't have dates at the beginning then this will cause the error. Try this:

if '/' in {Contact.Aer Date} [1 to 3]
then Cdate(left({Contact.Aer Date},8))

This will only convert values with a slash in the first 3 characters. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[nigelot]

That works a treat

thanks

Nigel

 

[nigelot]

A few more points please-

Can a report be set to refresh with the source database automatically or when opened? How do people refresh, I would forget to press refresh button to update
and report would be out of date.

Also can the Report be set to-
a) Add the day of week to report ie: Monday 11/02/02
b) Have report to display a week per page, Monday to Friday
The
if '/' in {Contact.Aer Date} [1 to 3]
then Cdate(left({Contact.Aer Date},8))
has saved me a lot of time
thanks again

Nigel

 

[kenhamady]

1) Don't save the data with the report. That way you won't forget to refresh it. To get an automatic refresh you would need to write an application.

2a) Don't understand this part

2b) Group the report by the date formula field and set the group break to be 'for each week", and then give it a page break after the group footer.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[nigelot]

1) Great works a treat, thanks

2a) Have the *day* appear on the Report as well as the date.

2b) I'm not sure how to do this could you give me more clues is it to do with the Section expert? do I have to write a formula?

thanks
Nigel

 

[nigelot]

I've got the first part of 2b)
but I dont understand how to 'give it a page break after the group footer.'

thanks

Nigel

 

[kenhamady]

2a) if it is a date field, use Format Field and you can select from many common formats, or use the Customize button.

2b) "Format - Section", highlight the GF and check off the property "New Page After". Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[nigelot]

thank you

Nigel

 

[nigelot]

Iam using a formula field that trimms a Postal (Zip) code. The field is called Pcode

(left(trim({Contact.Postcode}),4))

This work fine for codes like CH60 8PA etc becomes CH60. However we have Liverpool codes 'L' ie L1 4SN

This causes problems and appears as L1 4. What would I use for Liverpool Postal codes starting with L ( or any code with only one character at the start)to be trimmed by 3 only?

thanks

Nigel