formula error

[Cretin]

I have a problem. i am trying tio use crystal reports to report off of a heat data base. i want to use a parameter date range. Heat stores dates as a string so we need to convert it. I created the following formula:
__________________________________________________________
whilereadingrecords;

datevar wdate;

wdate:=Date(tonumber({Profile.InstallDate}[1 to 4]),
tonumber({Profile.InstallDate}[6 to 7]),tonumber({Profile.InstallDate}[9 to 10]));
datediff("d",wdate,currentdate);
_________________________________________________

When I check for errors its fine but when I refresh my data and get the parameter screen I enter in the beginning and ending date. Then I get the message sting is non numeric and the report is blank. Any ideas what I should be looking at?

Cretin

 

[Cretin]

sorry I am using CR 8.5

Cretin

 

[Katy44]

I know nothing about heat (in fact I've never even heard of it!) but have you tried displaying the substrings you are using on the report to check they are in fact all numbers?

e.g. a formula that is: {Profile.InstallDate}[1 to 4]

what does that show on the report?

 

[Cretin]

Thanks I will try that, and heat is a help desk database.

Cretin

 

[synapsevampire]

What database are you using?

I think that Heat used fox tables at some point, and also used other types of databases. The software name isn't as important as technical information, such as:

Crystal verisons
Database/connectivity
Example data
Expected output

You may be able to use a SQL Expression to convert it to a date, but you'll have to try it, and knowing what type of database will help.

Once you have the SQL Expression, Select Report->Edit Selection Formula->Record and place something like:

{%SQLEXpressionDate} = {?MyDateParm}

You don't need to use variables.

-k