join a number in a string field to a database field

[crystallearner]

Table 1 has name(string), timeslot(number) fields
Table 2 has schema a string field that contains 96 ASCII characters
Table 3 has ID(number), Description(string)

the result of:
asc(mid({table2.schema}, {table1.timeslot}+1, 1))
gives me the ID number for table 3

I am trying to report name and description on a report. I can extract the number from the string using the formula above but I have no idea how to then link that table3 and then display the associated description.

 

[Turkbear]

Hi,
Create that formula( Modified to cpnvert to Number not a char: tonumber(asc(mid({table2.schema}, {table1.timeslot}+1, 1)))
) in the main report and use it to link to a subreport using table3's ID as the linked field , that will handle the description.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[crystallearner]

Hi Turkbear,

I haven't explained this correctly.

each of the 96 characters = a 15 minute timeslot if the number after the conversion <> 255 then there is an entry for that timeslot. So I need to loop through all 96 characters and effectively create a record for each number that <> 255 then link that to table3 to display the description.

Does this make sense?

Lee

 

[Turkbear]

Hi,
OK, I missed the ASC conversion ( so ignore the tonumber thing) - Add a test to your formula :

If asc(mid({table2.schema}, {table1.timeslot}+1, 1))
<> 255 then
asc(mid({table2.schema}, {table1.timeslot}+1, 1))
else
0

(Assuming no code 0 exists in table3)
If you place that formula in the details of the main report ( it does not have to be displayed) and place the subreport,resized as needed and formatted with no lines or boxes around it, next to the detail fields, that link shoud cause the description to display on the same line as the other details...( assumes only 1 record in table3 has that ID number)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[crystallearner]

Hi,

Thanks for your help so far.

The code is different from mentioned earlier

numbervar i;
stringvar message;

for i := 1 to 96 do
(
if asc(mid({table2.schema}, i+1, 1)) = 255 then
message := message
else
message := message + i + " " + asc(mid({table2.schema}, i+1, 1))
);
message;