I have an Access table field called MIX_SERIAL_TXT that contains serial 

number ranges in each field.  Here are some examples:



4208828 THRU 5147771                              

5147772 THRU 5516584                              

5516585 AND UP                                    

0A266251 AND UP                      



They are formatted exactly as above, and correspond to another field that 

contains the model number for that particular serial number range, 

MIX_DOC_PART_FIELD, in the same table.  I need to be able to search for a 

specific serial number from MIX_SERIAL_TXT field, but cannot figure out 

how to search these ranges since the ranges are each within their own 

cell.  I know there has to be a way to do this, but I'm a beginner-

intermediate with Access/SQL.  Can anyone help?



Thanks,



Matt Davis

matt at mdjj.com             

Tell me if I understand you wrong, but was it that you have in one table 

both fields MIX_SERIAL_TXT and MIX_DOC_PART_FIELD and you would wish to 

find a row of one serial number, e.g. 4208829.



If I would make such database I would make two separate fields for 

MIX_SERIAL_TXT, other with the beginning of the range and other the end. I 

know it's possible to slice e.g. 7 first letters from a field but it would 

be much easier to have it separate in the table.



Then you could make a query to find row where serial number is bigger then 

the beginning field and smaller then the end field.



Hope this will help and don't hesitate to correct me if I understand you 

wrong or if you need more help with this,



Sanna



> I have an Access table field called MIX_SERIAL_TXT that contains serial 

> number ranges in each field.  Here are some examples:

> 

> 4208828 THRU 5147771                              

> 5147772 THRU 5516584                              

> 5516585 AND UP                                    

> 0A266251 AND UP                      

> 

> They are formatted exactly as above, and correspond to another field 

that 

> contains the model number for that particular serial number range, 

> MIX_DOC_PART_FIELD, in the same table.  I need to be able to search for 

a 

> specific serial number from MIX_SERIAL_TXT field, but cannot figure out 

> how to search these ranges since the ranges are each within their own 

> cell.  I know there has to be a way to do this, but I'm a beginner-

> intermediate with Access/SQL.  Can anyone help?

> 

> Thanks,

> 

> Matt Davis

> matt at mdjj.com             

Firstly, you've got 2 fields that end with "AND UP."  Assuming that "AND UP"

means all the way up to 9999999 or something like that, you'll have some overlap

and your search will never work.  So the first thing I'd suggest is redefining

the upper bound of each of your data ranges.  For example, "0A266251 THRU

ZZZZZZZ" and "5516585 THRU 9999999" (note: these are just guesses on my part).

Once you've redefined your boundaries, I'd split them into two fields, perhaps

txtStart and txtEnd.  Then your searching becomes something like "mySearchString

BETWEEN txtStart and txtEnd" (note: pseudocode!), which then returns

MIX_DOC_PART_FIELD for that serial number range.



That's what comes to me off the top of my head.  Let us know if you need more!



Chris "And Up" Kryshtalowych  :-)

Systems Specialist

University Healthcare System

Augusta, Georgia





{snip}

********** original message **********

Subject: Help With Access Query

From: "Matthew F. Davis" <matt@m...>

Date: Wed, 22 Aug 2001 19:29:07

X-Message-Number: 4



I have an Access table field called MIX_SERIAL_TXT that contains serial

number ranges in each field.  Here are some examples:



4208828 THRU 5147771

5147772 THRU 5516584

5516585 AND UP

0A266251 AND UP



They are formatted exactly as above, and correspond to another field that

contains the model number for that particular serial number range,

MIX_DOC_PART_FIELD, in the same table.  I need to be able to search for a

specific serial number from MIX_SERIAL_TXT field, but cannot figure out

how to search these ranges since the ranges are each within their own

cell.  I know there has to be a way to do this, but I'm a beginner-

intermediate with Access/SQL.  Can anyone help?



Thanks,



Matt Davis

matt at mdjj.com

**************************************

{/snip}