Evaluating numeric string as a number in record selection (v8.5) 1

[tutuster]

I want to get records from a db based on "category"-field, which is string typed but it contains mostly numeric data, some exceptions excluded. Let's say that the categories that we need to get are from 1 to 3, and we have categories from 1 to 99 in the db.

I get the data ok, but the problem is, that when i evaluate string fields with >= and <=, the results include category 10 too, since it fits in the statement "1 to 3" somehow, based on alphabetical comparison maybe?

Any ideas how to solve this?

 

[Madawc]

Try ToNumber({your.field})

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[tutuster]

Editor says that "String is required here", since the parameter is a string value. Therefore it wont let me convert values during evaluation, furthermore, i cannot declare variables in the record selection. Im totally stuck here!

 

[lbass]

I think you need to share the possible variations in your field, if it only 'mostly' is numeric. To use Madawc's solution you would need to change the datatype of your parameter to number. If the field is a number that is sometimes followed by a letter, then instead use:

val({table.field}) = {?numberparm}

-LB

 

[tutuster]

The rows i need to fetch are only numeric strings, i dont need to mind about the alphanumeric categories, since they are specialities.

Tried using that numeric parameter, but the editor still whines about wanting to have a string there (the record is string datatype), should i convert the numeric parameter value back to string again? But that would lead me straight to the starting point again. Bah. Stupid CR... or just a stupid user

 

[synapsevampire]

A shame you didn't post your database type, why people don't post this nor ask for it always amazes me, this is a great candidate for a SQL Expression which tests for a numeric and converts it to either the proper number or a zero...

Anyway, try this, it's slow but it will work:

Create a formula of:

//@NumPart
if isnumeric({table.field}) then
val({table.field})
else
0

Then in the Report->Edit Selection Formula-Record:

{@NumPart} = {?MyNumericParameter}

This is a kludgy solution, as your real problem is that your dba and coder need to go back to flipping burgers for a living for allowing meaningful numeric data to be stored this way.

I would probably create a View which fixes the issue.

-k

 

[tutuster]

I believe the type was mentioned on the opener of this thread.. anyway, thank you for your reply, need to check that out.

I agree on this one, there is a fundamental flaw in this data system, this is not the first nor the last time i need to gimmick with string typed data, which is actually used as numeric after all. However, this is what you get when corp uses software solutions that are tailored to everyone, one size definitely does not fit all..

 

[synapsevampire]

No, read your thread, you didn't post the database type, why would you just offhandedly say that, and still not post it...always amazing to me how little effort people want to put towards solutions.

Anyway, my solution should work for you, otherwise I won't put you out any further asking for technical information when trying to solve your problem...

<shaking my wee pointy noggin>

-k

 

[tutuster]

Erm, yes my bad, misread it myself.. i apologize for that.

What do you mean by database type? I just dont know what information you are looking for, since (as you may have noticed) english is not my native language.

The db is oracle 8.x

 

[tutuster]

Thanks for the tip, turned out good and rockin'

 

[synapsevampire]

Glad it worked out, keep in mind that the solution isn't optimized, and that you can't use a zero as the parameter.

-k