Selecting records 'near' parameter values

[Mark77]

I have a table of quality control data. The user will enter a group of serial numbers to be reported upon. Every serial number will not have a record in the table as items are pulled off the line randomly to be tested. I would like to display:
a) data for the serial number entered if there happens to be a record for it in the table
otherwise
b) data for the nearest lower serial number and the nearest higher serial number in the table

Crystal 7.0 and Crystal 8.5 Enterprise
Microsoft Access ODBC

 

[synapsevampire]

You'll probably want to use a stored procedure to do this, otherwise you'll have to pull in all rows (or cetainly a large superset) and then do the check.

Since they're entering a group of S/N's, you'd have to build a multiple record selection criteria in CR to *possibly* allow for a lower value - tricky stuff, but doable.

Post some sample serial numbers.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Mark77]

The serial number field in the Microsoft Access database is a long integer data type, e.g., 43657, 77889, 123456. I don't see where a stored procedure would be involved regarding Access.

 

[SmpTraining]

Looks like your only option would be to select ALL records and, as SV says, conditionally supress the unwanted records.

SV is obviously concerned with the performance issues involved in this process but if your database isn't too vast, the results may be acceptable.

You will need to apply a formula again the 'supress' option for the details section such that you only get shown the records which are equal to or immediately before and immediately after the entered numbers.

You will need to use the previous and next functions to achieve this. Steve Phillips, Crystal Consultant

 

[Mark77]

I believe I am making progress. At the present time I am stuck on how to reference the subscript of the parameter array so the PREVIOUS(parameter[x]) and NEXT(parameter[x]) records can be selected. I've been working on a formula in the group header section to increment a global variable, but not much luck so far.