no. of copies of a report is based on a database field

[adhhealth]

According to Ido Millet's Faq FAQ149-3226

Problem:
A typical situation is where a number of label copies must be printed for each order according to the {quantity} field in each order record.

Solution:
Create a "REPEATER" table with a single column (How_Many) that looks like this:

How_Many
1
2
3
4
5
6
etc.

Now, in your report, add the Repeater Table and add a join
condition of:
-----------------------------------
Order.quantity >= Repeater.How_Many
-----------------------------------

If the ">=" join option is not available, remove the join to the REPEATER table and create this condition in the Record Selection condition:
--------------------------------
{Order.quantity} >= {Repeater.How_Many}


My problem:
(I am using crystal 8)
The above method works fine if the database field is a "number" but the database field I am accessing is a "String" In the record selection condition I have given

tonumber({Order.quantity}) >= {Repeater.How_Many}

but it says "database dll error and doesnot work". Any suggestions?

 

[synapsevampire]

A shame you didn't bother to state your database type.

Look into SQL Expressions. You can CAST the data to a numeric on the database side, and then use the SQKL Expression in your record selection formula.

-k

 

[adhhealth]

I am using pervasive sql database

 

[adhhealth]

PL . note that I am very new to this field ,any guidance will greatly be appreciated.
1. i have written the formula
convert('quantity',SQL_INTEGER) in "SQL expressions" (called the formula "new")- saves correctly no syntax error
because when I tried to use the cast function
cast(Order."quantity" as Integer) it gave me error message syntax error select Order."quantity" as <?????> integer)
also i checked going to pervasive sql control center using the "convert" function it works fine.
2. Now in record selection formula I added
{%new} >=Repeater.how_many
when I saved the report I got the error
ODBC error:[pervasive][odbc client interface][lna][pervasive][odbc engine interface] The column prefix 'MXHIST' does not match with a table name used int the query.
('MXHIST' is another table I am using along with the other tables in the report and is in the "record selection formula")

I know I am doing wrong somewhere..can You direct me??
THank you!!

 

[adhhealth]

the formula i wrote is
convert(Order."quantity" , sql_integer)..typo in the earlier post

 

[synapsevampire]

Try posting everything in the record selection formula.

Unfortunately I don't have Pervasive to test with, perhaps the ODBC driver hiccups on these.

-k