I'm trying to use a parameter field in a report based on a picklist I have imported consisting of our suppliers. The picklist goes like so:

 

002 Kent Farm

ABA Roland Farm

040 Big Farm ..... etc.

 

This table is quite old and the first 10 records consist of the following:

 

A Small Farm

B Med Farm

D Tiny Farm ..... etc.

 

Now, whenever I use the Supplier parameter field to pick up one of the old records above, it returns nothing. The parameter field works fine with all of the other records, its just the single digit/number records.

 

The dbase seems to show the single digit records as " D ", but I have no idea how to replicate this in Crystal. I have set the option to set all of the Nulls to the default, but nothing seems to work.

 

The daft thing is, if you leave the parameter field empty (so that it picks up all suppliers - e.g. if = "" then true), suppliers with a single digit appear. But if you then search specifically for them suppliers (select them in the parameter drop down box) crystal returns nothing.

 

Thanks all, i'm still new to Crystal so any help would be much appreciated. If you need to know more please ask. By the way i'm running my database links over an Advantage system.

 

Thanks again!

 

My picklist is a combination of the codes and supplier names, so:

 

400 Wilson Farm

050 Big Farm

G Small Farm

262 Wylie Farm

D Tiny Farm

 

It is a combination of suppliers and their codes. The older ones are the single digit ones, but there are only 10 of them. My database (as viewed directly) seems to show the single digit ones as " D " and all of the others as "400", "020" etc, so it seems to centre the single digit.

 

My selection code for the parameter is as follows:

 

'

(if {?Supplier (ignore for all)} = "" then true else if {?Supplier (ignore for all)} <> "" then {PO.SUPPLIER} = {?Supplier (ignore for all)})

'

 

'{?Supplier..' is my parameter field, PO.SUPPLIER is where the supplier code (" D ", or "040" etc) is tagged to an order.

 

My picklist was generated from a table called SUPPLIER, which shows the TEXT (supplier name) and its CODE. This is linked via a left outer join to the PO table (PO -> SUPPLIER).

 

I am using the above code so that if nothing is selected then the query will return all records - hence the '(ignore for all)' text.

 

Sorry if this is even more confusing!

 

Thanks for the more efficient code. I still can't seem to pick up the single digit values though.

 

Tried this earlier but the amount of suppliers we have is over 1000, therefore past the limits of the parameter field values!

 

This is the reason why I have to import a pick list, if I try to import directly from the table it doesn't include all the values.

 

I think i'll just have to leave this how it is and make people aware that there are 10 of the 1000 odd suppliers that we have which may cause a problem.

 

Many thanks for your help Rahul, but I think that due to bad old dbase design, this problem will take way too long to solve!

 

That last bit of code seems to have worked.

 

Many, many thanks!