473,227 Members | 1,661 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,227 software developers and data experts.

Searching for serial numbers

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
3 3215
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

21
by: Gavin | last post by:
Hi, I'm a newbie to programming of any kind. I have posted this to other groups in a hope to get a response from anyone. Can any one tell me how to make my VB program read the Bios serial number...
21
by: nephish | last post by:
i have an interesting project at work going on. here is the challenge. i am using the serial module to read data from a serial input. it comes in as a hex. i need to make it a binary and compare it...
1
by: Darren DeCoste | last post by:
I am trying to create an application in Access that I will be able to print bar code labels. A stumbling block that I have hit is the Serial Number on the bar code. I would like to be able to read...
3
by: Gianmaria | last post by:
Anyone knows how can i get the cpu serial or the hd serial of the machine runnig the application with c#? regards Gianmaria
33
by: Geoff Jones | last post by:
Hiya I have a DataTable containing thousands of records. Each record has a primary key field called "ID" and another field called "PRODUCT" I want to retrieve the rows that satisy the following...
0
by: Ryan Liu | last post by:
How to read NIC card serial numbers or MAC address , Hard Drive serial numbers in Java or Delphi or C/C++ or C#? Thanks, Ryan
3
by: Bayazee | last post by:
Hi, How can I get CPU Serial number , or motherboard serial number with python . I need an idetification of a computer .... ThanX --------------------------------------------------- iranian...
6
by: Paul Bromley | last post by:
Ok - I have given up on trying to find the active IP address for a given PC. For licensing purposes I need to retrive a unique identifier from the PC that the program is installed on. The Hard disk...
2
blyxx86
by: blyxx86 | last post by:
Great insight needed!! Good evening everyone.. I've come to a point where I can't even fathom how to go forward. I'm attempting to create a serialized inventory. I don't know where to begin....
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.