SearchForNumber +or_200 1

[CliffordTheRedDog]

I have a list of serial numbers, i need to search them for a match to my number + 200 and -200
Can anyone please help?
TIA
Cliff

 

[Norris68]

SELECT ... WHERE SerialNumber BETWEEN [MatchSerial]-200 AND [MatchSerial]+200

MatchSerial is the input parameter of the serial number you want to match. This will not work if your serial number is not purely numeric though.

 

[CliffordTheRedDog]

Norris68,
Thanks for that, it works most of the time. Occassionally i get the following error unless i reduce the search to say 70 each side of the figure. This is ok for now but future batches may breech this range of serial numbers.

"Numeric field overflow"
Help shows "The data in a Btrieve field is too large to represent in your application."

Btrieve? no help on this either!



Then all the fields turn to show

#name?

Do you know what is causing this? I have tried the other main help for the problem but i cannot find this particular error.

The reason i need to retrieve this quantity either side of the serial number is due to the way the software stores runs of serial numbers. It stores the first and the last. so a list of 300 numbers could be stored as 21034163 to 21034462 with obviously no mention of the 298 numbers in between.

A better way to search if it exists would be in the following order.

Find the serial number, or the nearest number above(the start of the run), or the nearest number below(the end of the run. This would then show the row containing the run of products that contain the searched for serial number.
Thanks again for your help!
Cliff

 

[Norris68]

If I understand you, you want the serial numbers that occur at the top & bottom of a range based on your given serial number. In other words, the serial number 300 before your one and the one 300 after - not SN-300 & SN+300 necessarily.

OK, but in Access it's going to take 3 queries as Access neatly cocks up the second sub query when used in a UNION.

Table is called tblSN, Field is called SN, Required S/N is called SerialNumber and in my example we are taking 3 numbers before and 3 after (but including the specified S/N if it exists):

Query 1 - 'qrySNTopRange'

SELECT TOP 1 SN FROM (SELECT TOP 3 SN FROM tblSN
WHERE SN < [SerialNumber] ORDER BY SN DESC) ORDER BY SN

Query 2 - 'qrySNBottomRange'

SELECT TOP 1 SN FROM (SELECT TOP 3 SN FROM tblSN
WHERE SN >= [SerialNumber] ORDER BY SN) ORDER BY SN DESC

Query 3 - the one that ties them together

SELECT SN FROM qrySNBottomRange
UNION
SELECT SN FROM qrySNTopRange

... so you will need to change the name where appropriate and select the TOP 150 in each of the range queries instead of 3 as per my example.

 

[CliffordTheRedDog]

Norris68,
I used just one part of your suggestion, this gives me the starting serial number for the batch containing the serial number being serached from that i am anble to display product creation date etc.

SELECT TOP 1 RunningSerialNumbers.F1 AS [Day], RunningSerialNumbers.F2 AS [Month], RunningSerialNumbers.F3 AS [Year], RunningSerialNumbers.F4 AS Qty, RunningSerialNumbers.F5 AS Product, RunningSerialNumbers.F6 AS Type, RunningSerialNumbers.F7 AS Start, RunningSerialNumbers.F8 AS [To], RunningSerialNumbers.F9 AS [End], RunningSerialNumbers.F11 AS Assemblers
FROM [SELECT TOP 1 F7 FROM RunningSerialNUmbers
WHERE F7 < [SerialNumber] ORDER BY F7 DESC]. AS [%$##@_Alias] INNER JOIN RunningSerialNumbers ON [%$##@_Alias].F7 = RunningSerialNumbers.F7
ORDER BY [%$##@_Alias].F7;

Thanks for your help,
Cliff.