How can I reverse text in a field 1

[teamssc]

I have a text field that contains imported dates like, 11/16/2000, that I need to sort for reporting and filtering. My guess is that if I can reorder the text to 20001116 (yyyymmdd) that I would have better luck doing it...

Any suggestions on a formula that would do that?

Thanks

 

[MalcolmW]

Since they are dates, probably the easiest thing to do is to convert them to dates, then sort/filter them as dates (rather than as numbers).
The format for date is
DATE(2000, 12, 25)
So a formula would look like

WhileReadingRecords ;
Local NumberVar YearInt ;
Local NumberVar MonthInt ;
Local NumberVar DayInt ;
YearInt := ToNumber({datefield}[7 to 10],0) ;
MonthInt := ToNumber({datefield}[1 to 2],0) ;
DayInt := ToNumber({datefield}[4 to 5],0) ;
Date(YearInt, MonthInt, DayInt)

The use of the variables in the above formula is just to make it more readable.
You could then use this formula as a field for sorting and filtering your report. Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[kenhamady]

If you have trouble with some months or days being 1 digit, I have a formula in the FAQ area that looks for the positiion of the slash, and converts a character date to a true date. See the FAQ for common formulas. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[teamssc]

Great suggestion... I will try it in the next hour or so and see if I can make it work...

Thanks!

 

[teamssc]

I am getting "too many arguments have been given for this function"

YearInt := ToNumber({ContSupp.LINKACCT}[7 to 10],0) ;

and I would guess I'll get it on the next two lines also...

am I not inserting my field correctly?

I'm trying to see if I need extra () somewhere but have yet to figure that out...

 

[teamssc]

Taking the ",0" off of the formula works.... in fact the field is formatted and search able now...

This is great!!! what is the ",0" supposed to do?

 

[teamssc]

well i thought it worked.... when I try to set the report for a date range it brings up the formula and tells me "the string is non numeric"

 

[MalcolmW]

Do you have dates in the format "1/31/1999" instead of "01/31/1999"?
What version of SCR are you using?
Can you check to see what the maximum and minimum length is for the data in that text date field? Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[teamssc]

The dates are all "01/01/2000" or 10 digits...

The field length is 20 char, I'm not sure what you mean by Max and Min length though.... the data is a dbf, dbase 4, file...

Thanks for your continued help on this

 

[teamssc]

Missed one of your questions... running version 7

 

[teamssc]

Heres another aspect I don't understand... I just deleted the formula, ran the report with the formula empty... the report worked the way it used to ... then I pasted the formula back in and previewed the report... it runs great, converts the data and displays it as a true date, with no leading character, as 1/1/2000. Then I save the report and refresh the data and the formula box pops up and says "the string is non-numeric" and won't proceed.

 

[teamssc]

what does the ,0) at the end of the strings do?

 

[MalcolmW]

That is a bit odd.
Just to make sure it isn't a null value causing the problem:
WhileReadingRecords ;
Local NumberVar YearInt ;
Local NumberVar MonthInt ;
Local NumberVar DayInt ;
If Not IsNull({datefield}) then
(
YearInt := ToNumber({datefield}[7 to 10],0) ;
MonthInt := ToNumber({datefield}[1 to 2],0) ;
DayInt := ToNumber({datefield}[4 to 5],0) ;
Date(YearInt, MonthInt, DayInt)
)

The ,0) is a parameter for ToNumber(numerictext, x), where x is the number of decimal places. If the parameter is omitted, the number of decimal places defaults to the Windows default for numeric values on the client machine.

The min/max length for the data was just to confirm that all the strings were the correct length. It really doesn't matter what the field size is Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[teamssc]

I've included the If Not IsNull... but I still get an error "too many arguments have been give to this expression" and it points to...
ToNumber({ContSupp.LINKACCT}[7 to 10],0);

If I take out the ,0 then it still says the string is non numeric.

I've copied the forumla here to make sure I'm not typing something wrong...

WhileReadingRecords;
Local NumberVar YearInt;
Local NumberVar MonthInt;
Local NumberVar DayInt;
If Not IsNull ({ContSupp.LINKACCT}) Then
(
YearInt:= ToNumber({ContSupp.LINKACCT}[7 to 10],0);
MonthInt := ToNumber({ContSupp.LINKACCT}[1 to 2],0);
DayInt := ToNumber({ContSupp.LINKACCT}[4 to 5],0);
Date(YearInt, MonthInt, DayInt)
)

 

[MalcolmW]

This portion of your string
{ContSupp.LINKACCT}[7 to 10]
should contain the year ie "1999"
However, the error message says that this portion of the string is non numeric.
ie "1,99", "What", "etc."
So, which records are the problem?
A simple report with the record selection formula of
NOT IsNumber({ContSupp.LINKACCT}[7 to 10])
and showing the field {ContSupp.LINKACCT} will identify the problem fields. A formula with
{ContSupp.LINKACCT}[7 to 10]
would identify the specific parts of the string that are the problem.



Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?