Problems converting a string to a date

[benamorton]

Hey All,
I am having problems converting a string into a date, so that even users running the runtime package of Crystal (I have 8.5) don't get errors.

The String sits as: "03/11/04 3/15/04" (w/o quotes)
First, I had this:

if length ({TRANS.COMMENTS}) >=8 then
datevalue(left({TRANS.COMMENTS},8))

but runtime users were getting:

Error in Formula //Datevalue A number, currency amt, Boolean is expected here.

So, I knew that datevalue was fairly new, so I tried simply:

if length ({TRANS.COMMENTS}) >=8 then
date(left({TRANS.COMMENTS},8))

but other users were getting errors again.
Then I tried:

date(tonumber({TRANS.COMMENTS} [1 to 2]),
tonumber({TRANS.COMMENTS} [4 to 5]),
tonumber({TRANS.COMMENTS} [7 to 8]))

But it says: A month must be between 1 and 12
So I figured it wanted month,day,year, so I tried:

date(tonumber({TRANS.COMMENTS} [4 to 5]),
tonumber({TRANS.COMMENTS} [1 to 2]),
tonumber({TRANS.COMMENTS} [7 to 8]))

But still won't work! If anyone has other ways I could try to convert this string into a date, I would be appreciative to hear them. Thanks in Advance

Ben

 

[rchandr3]

Use FORMAT in Formula field(VB)... You can get the date in any form you want.

ALL THE BEST

 

[benamorton]

Isn't that used when displaying a field? Its not that I need to change the format of a date, I need to change the string to a date in the first place. This is all happening inside a formula. I need to convert it so I can compare it to a parameter in the select expert. It errors out before it runs. Sorry for the confusion.

 

[rchandr3]

where are you wring these code lines?

if length ({TRANS.COMMENTS}) >=8 then
datevalue(left({TRANS.COMMENTS},8))

VB or Crystal or any other tool?

 

[benamorton]

It is Crystal syntax inside Crystal reports 8.5, and like I said I am comparing it to a parameter. Thanks

 

[rchandr3]

O, Sorry. I thought it as a crystal syntax in a formula field

if you need the date correct every time, then the string should come to you in a specified format every time. In that case, it would be easy for us to do things.

if length ({TRANS.COMMENTS}) >=8 then
datevalue(left({TRANS.COMMENTS},8))

The above code will not work well if the date comes as 1-2-1978

So if you are sure about the string format which is to be converted, give that and let us make a try.

Cheers,

Ravi

 

[benamorton]

The data sits as 01/15/04, for example. So the numbers are always in the same place in the string. It is always MM/DD/YY. But I am having problems with the above given formulas. They work when users have the full version of Crystal installed on their machine, but not if they have just the runtime package (which simply runs compiled reports). Any ideas?

Thanks for your time

 

[rchandr3]

Error in Formula //Datevalue A number, currency amt, Boolean is expected here.

This message would come if the version of Crystal Reports you develop and the users' runtime use are different.

Did you try CDate(stringvalue)... I tried that here and for CDate("01/15/04") it gives me 15/01/2004



Cheers,

Ravi

 

[benamorton]

That CDate() function didn't work either. Actually I found out more information about what the problem is. When the report runs, it is supposed to ask for a parameter that goes to the first report(which happens OK), and then when it is supposed to ask for anohter date (which needs to be converted from a string) that goes to the subreport, the report does not have a tab for the user to click on and enter the value.
Any more ideas?
Thanks

Ben

 

[rchandr3]

Ben,

I can suggest you these things:

First thing: You have to avoid users getting such messages. That can be done by finding the length of the string to know whether you got the string in place.

Second thing: If possible, try to have a default value for the formula in case of failure.

I am hopeful that you would find the solution yourself!



Cheers,

Ravi

 

[benamorton]

I am not sure where I will go from here, thanks for your help

BEN

 

[lbass]

If we assume your dates are all greater than or equal to the year 2000 and that you are only trying to capture the first date in the string field, then try:

date(val("20"+mid({table.stringdate},7,2)),
val(left({table.stringdate},2)),
val(mid({table.stringdate},4,2)))

-LB