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(LowSt ring, SerialNumberFie ld) and TestHi: =HiValue(HiStri ng,
SerialNumberFie ld). 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!txtLowVa lue,
SerialNumberFie ld)]. TestLow returns True if the current value of
SerialNumberFie ld 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 "computeriz ed" 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.wav e.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
