By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,972 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

Searching for serial numbers

P: n/a
Hi there

I'm trying to find part serial numbers between 2 numbers. The user selects a
part number from a combo box and then enters a range of serial numbers into
2 text boxes and the resulting query should find every machine that has that
part number between the serial number range.

The problem is that the serial number stored is a text field and the results
are not what they should be.

PartNo SerialNoFrom SerialNoTo
A333 100 400

Returns
100
1001
1033A
20000345
285D
309
3987092

The results should be
100
285D
309

What's the best way to create a search form where the items being searched
for are a combination of text and numbers?

Stewart

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:c8**********@news.wave.co.nz...
Hi there

I'm trying to find part serial numbers between 2 numbers. The user selects a
part number from a combo box and then enters a range of serial numbers into
2 text boxes and the resulting query should find every machine that has that
part number between the serial number range.

The problem is that the serial number stored is a text field and the results
are not what they should be.

PartNo SerialNoFrom SerialNoTo
A333 100 400

Returns
100
1001
1033A
20000345
285D
309
3987092

The results should be
100
285D
309

What's the best way to create a search form where the items being searched
for are a combination of text and numbers?


Assuming the numbers are always at the front of the serial number you could use
the Val() function to return the numeric portion and filter on that. This will
not be very efficient if this is a large database however since filtering on an
expression can not take advantage of any indexes you might have on the field.

SELECT * FROM SomeTable
WHERE PartNo = 'A333'
AND Val(SerialNo) BETWEEN 100 And 400
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #2

P: n/a
Hi Stewart,

I won't try to solve your issue in its entirety. I will characterize some
of the constituent issues and then make a couple of suggestions. The rest
will be up to you or maybe some really, really kind hearted Samaritan will
leap in an take it the rest of the way.

As you already know, that thing you refer to as a Serial Number isn't a
number at all. It is an alphanumeric string in a text field. Not only
that, while the numeric root may progress in a meaningful numeric sequence
the alphabetic post fix character(s) may be from 0 to some undetermined
number of characters in length. While you haven't said so, it is implicit
that the suffixed alpha characters and in alpha sequence as regards the same
root.

Your problem/goal is to return a range of values between two text string
values. If you have to stay with the existing "Serial Number" construct,
you can do it with a fair bit of string manipulation in VBA. [If you have a
good book that tells you about string manipulation in BASIC or VBA you're in
luck. If not, I recommend The VBA Developer's Handbook by Ken Getz et alia
from Sybex]. You'll need two function procedures in a general code module;
one for LowValue and another for HiValue. They will be similar but
different and should return True or False. Your existing query already
returns the "serial number". Leave that field in the query as it is. You
need to create two new fields something like the following - TestLow:
=LowValue(LowString, SerialNumberField) and TestHi: =HiValue(HiString,
SerialNumberField). You can get the values for LowString and HiString as
you are now or by referring to the appropriate control on the form that
launches your existing query [ TestLow: -LowValue(forms!MyForm!txtLowValue,
SerialNumberField)]. TestLow returns True if the current value of
SerialNumberField is equal to or greater than LowString otherwise it returns
False. Similar logic applies in TestHi.

If you have total control over the application and the serial numbers, I
recommend that you make it pretty much a machine generated affair with three
parts to the number separated by dashes if necessary; root number, main rev
number, minor rev number.. Given what you've posted, it looks like a paper
system that's been/being "computerized" and I imagine it's woven into the
culture of your company that it just "has" to be the way it is.

As to the code that goes into the two function procedures, it isn't complex
but it requires knowing what you are doing at each step of the way and
paying attention to the details..

hth
--
-Larry-
--

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:c8**********@news.wave.co.nz...
Hi there

I'm trying to find part serial numbers between 2 numbers. The user selects a part number from a combo box and then enters a range of serial numbers into 2 text boxes and the resulting query should find every machine that has that part number between the serial number range.

The problem is that the serial number stored is a text field and the results are not what they should be.

PartNo SerialNoFrom SerialNoTo
A333 100 400

Returns
100
1001
1033A
20000345
285D
309
3987092

The results should be
100
285D
309

What's the best way to create a search form where the items being searched
for are a combination of text and numbers?

Stewart

Nov 12 '05 #3

P: n/a
Thanks Larry for a well put forward explanation for how to tackle the
problem. It would be good if I could control the serial number's format and
that would make the searching easier but the serial number is the number
given by the maker of the part. A stereo system has a different serial
number format than your TV. In my case my client selects a part number for a
motor and then wants to find all machines that have a serial number that
belongs to that type of motor within a certain range of numbers. Then they
might want to do the same but with a controller which has a different serial
number format.

I'll take your advice onboard and create a couple of functions in VBA and
merge them into my query.

Stewart
"Larry Daugherty" <La********************@verizon.net> wrote in message
news:nh*********************@nwrddc01.gnilink.net. ..
Hi Stewart,

I won't try to solve your issue in its entirety. I will characterize some
of the constituent issues and then make a couple of suggestions. The rest
will be up to you or maybe some really, really kind hearted Samaritan will
leap in an take it the rest of the way.

As you already know, that thing you refer to as a Serial Number isn't a
number at all. It is an alphanumeric string in a text field. Not only
that, while the numeric root may progress in a meaningful numeric sequence
the alphabetic post fix character(s) may be from 0 to some undetermined
number of characters in length. While you haven't said so, it is implicit
that the suffixed alpha characters and in alpha sequence as regards the same root.

Your problem/goal is to return a range of values between two text string
values. If you have to stay with the existing "Serial Number" construct,
you can do it with a fair bit of string manipulation in VBA. [If you have a good book that tells you about string manipulation in BASIC or VBA you're in luck. If not, I recommend The VBA Developer's Handbook by Ken Getz et alia from Sybex]. You'll need two function procedures in a general code module; one for LowValue and another for HiValue. They will be similar but
different and should return True or False. Your existing query already
returns the "serial number". Leave that field in the query as it is. You
need to create two new fields something like the following - TestLow:
=LowValue(LowString, SerialNumberField) and TestHi: =HiValue(HiString,
SerialNumberField). You can get the values for LowString and HiString as
you are now or by referring to the appropriate control on the form that
launches your existing query [ TestLow: -LowValue(forms!MyForm!txtLowValue, SerialNumberField)]. TestLow returns True if the current value of
SerialNumberField is equal to or greater than LowString otherwise it returns False. Similar logic applies in TestHi.

If you have total control over the application and the serial numbers, I
recommend that you make it pretty much a machine generated affair with three parts to the number separated by dashes if necessary; root number, main rev number, minor rev number.. Given what you've posted, it looks like a paper system that's been/being "computerized" and I imagine it's woven into the
culture of your company that it just "has" to be the way it is.

As to the code that goes into the two function procedures, it isn't complex but it requires knowing what you are doing at each step of the way and
paying attention to the details..

hth
--
-Larry-
--

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:c8**********@news.wave.co.nz...
Hi there

I'm trying to find part serial numbers between 2 numbers. The user selects
a
part number from a combo box and then enters a range of serial numbers

into
2 text boxes and the resulting query should find every machine that has

that
part number between the serial number range.

The problem is that the serial number stored is a text field and the

results
are not what they should be.

PartNo SerialNoFrom SerialNoTo
A333 100 400

Returns
100
1001
1033A
20000345
285D
309
3987092

The results should be
100
285D
309

What's the best way to create a search form where the items being

searched for are a combination of text and numbers?

Stewart


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.