Substring Function

[Mike_P._(Guest)]

Howdy!____I have parsed out a substring from a long field using the SUBSTR function into a new field that will be linked to another database of numeric codes later in my project. The problem is that when I attempt to join the other data source that has the numeric codes in it, the matching column does NOT show up in the join dialog box.____My guess is that there is a data type mismatch.__When I write a short report and select the numeric field in question, I can easily insert them into a stand-alone report. Based on the long string below - how can I make the 36 numeric?____Field Name: Series_ID__Field Contents: SMS360680102001321____I defined a calculated field State as__ __Substr(Series_ID,4,2) and can get the value of 36 - but can^t link to it..____Thanks for any solutions...____Mike P.__Buffalo, NY____

[Thomas_Freitas_(Guest)]

You did not say what version you are using and if it is sql or xbase.____In Xbase Version 10+ there is no problem with creating a substring and then related it to another file.

[Morrisbaish]

Yes - I am using Version 10+____Maybe I should have phrased the question: when you use the Substr function to parse out a subset of characters, is the result always a character type and subsequently you can^t do numerical operations with the value even after you establish a "join" with another table?______

[Randall_Warden_(Guest)]

Just modify the function to VAL(Substr(Series_ID,4,2)) to convert to a numeric value.

[kfleming]

The only calculated fields that are available as join fields are those that can be evaluated by the SQL engine. When you create a calc, R&R checks the RSWSQL.INI file to see if that calc can be translated and sent to SQL for evaluation. If it can, then when you select the calc in R&R, on the status bar at the bottom of the screen an (x) will be displayed.__Only fields having this (x) can be used as join fields.__R&R evaluates the RSWSQL.INI based on what ODBC driver you are using.__Only those functions seen as translatable for that driver will be shown with an (x).______Kathleen__R&R Support__