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

 

[nigelot]

It may be easier to go from right to left as it is always the *last 3 characters* I want to omit.

ie

CH40 8SN (omit 8SN)
CH1 0RB (omit 0RB)
L1 4SN (omit 4SN)

thanks

Nigel

 

[kenhamady]

Try:

{field} [ 1 to Length( {field} ) - 4 ] 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]

I tried

{Contact.Postcode} [ 1 to Length( {Contact.Postcode} ) - 4 ]

I get an error-

A subscript must be between 1 and the length of the string

I have got some incomplete Postal Codes or no code (not known) in the source database ie just- PR8 or L25 or blank

Nigel

 

[kenhamady]

Try this modification:

if length({Contact.Postcode}) > 4 then
{Contact.Postcode} [ 1 to Length( {Contact.Postcode} ) - 4 ]
else {Contact.Postcode} 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 fine

thanks

Nigel