query data from mixed format field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The field has data in several different alpha-numeric-symbol incarnations, ie
AA-11-B-2222 or 1-AA-2222-B. There are about 4000 records...that's a lot to
create a new field with a standardized format for pulling results. I want to
query the database to show all records within a numeric range, like all the
2000-2400 records, but the data format is as the example of above. Any
suggestions on how to either query or create a new field that pulls only the
needed number (and then i can run a standard <> query?) thanks in advance -
this message board has been a big help to me. ***susan***
 
Something like this might get you started

Criteria Example:

Like "*20##*"
Like "*21##*"
Like "*22##*"
Like "*23##*"
Like "*24##*"
 
Darren, we tried your example yesterday...but we got results returned that
looked like AA-11-B-3224 as well as the desired outcome of AA-11-B-2240.
While this does narrow things down somewhat, I would like to come up with
some slick way of not using an initial wild card in the number sequence and
still be able to wild card for the various data input formats that exist
before and after the 4 digit numeric portion of this field...
 
Darren, we tried your example yesterday...but we got results returned that
looked like AA-11-B-3224 as well as the desired outcome of AA-11-B-2240.
While this does narrow things down somewhat, I would like to come up with
some slick way of not using an initial wild card in the number sequence and
still be able to wild card for the various data input formats that exist
before and after the 4 digit numeric portion of this field...
Click to expand...

Unfortunately those are purely contradictory requirements.

Access has no way of knowing that you're only interested in the 4th
through 7th bytes in this record, and the 6th through 9th bytes in the
next record. The problem is that the data is an "intelligent key" with
multiple pieces of information stored in one field!

Is there ANY way that you can rationalize this hodgepodge into a
rational set of fields? Or is there any consistant way to identify
which subfield contains the four-digit number of interest, other than
it being a four digit number?

John W. Vinson[MVP]
 
John Vinson said:
Unfortunately those are purely contradictory requirements.

Access has no way of knowing that you're only interested in the 4th
through 7th bytes in this record, and the 6th through 9th bytes in the
next record. The problem is that the data is an "intelligent key" with
multiple pieces of information stored in one field!

Is there ANY way that you can rationalize this hodgepodge into a
rational set of fields? Or is there any consistant way to identify
which subfield contains the four-digit number of interest, other than
it being a four digit number?

John W. Vinson[MVP]
Click to expand...

John, thank you for your answer. Short of my redesigning the tables in this
database, I haven't been able to come up with anything that would give me a
field with consistantly formatted data. This isn't "my" database; if it were
I would build the field one way or the other. I was hoping to be able to
pull up some sort of make table calculated field magic. This particular
database started as a Word Perfect table, which I at one time converted to
text and then to Excel 97. Later, a student converted the old Excel table to
Access 2002 without fulling communicating with the primary user or me on what
type of structure might be appropriate. A pity...
 
The field has data in several different alpha-numeric-symbol incarnations, ie
AA-11-B-2222 or 1-AA-2222-B. There are about 4000 records...that's a lot to
create a new field with a standardized format for pulling results. I want to
query the database to show all records within a numeric range, like all the
2000-2400 records, but the data format is as the example of above. Any
suggestions on how to either query or create a new field that pulls only the
needed number (and then i can run a standard <> query?) thanks in advance -
this message board has been a big help to me. ***susan***
Click to expand...

ok... just because it's a challenge... here's a little VBA function to
extract the first four-digit string of characters from an arbitrary
text field:

Public Function strFourDigits(strIn As String) As String
Dim iPos As Integer
Dim iLen As Integer
strFourDigits = ""
iLen = Len(strIn)
If iLen >= 4 Then
For iPos = 1 To iLen - 4
' skip hyphens so they don't get confused with minus sign
If Mid(strIn, iPos, 1) <> "-" Then
If IsNumeric(Mid(strIn, iPos, 4)) Then
strFourDigits = Mid(strIn, iPos, 4)
Exit Function
End If
End If
Next iPos
End If
End Function

You might be able to use this function to extract the number to a
calculated field for searching. In testing I found one misfeature:
"aa-1-222-bb" returns "222-", apparently because "222-" is recognized
as numeric, presumably equal to negative 222.


John W. Vinson[MVP]
 
i'll try it...thanks!

John Vinson said:
ok... just because it's a challenge... here's a little VBA function to
extract the first four-digit string of characters from an arbitrary
text field:

Public Function strFourDigits(strIn As String) As String
Dim iPos As Integer
Dim iLen As Integer
strFourDigits = ""
iLen = Len(strIn)
If iLen >= 4 Then
For iPos = 1 To iLen - 4
' skip hyphens so they don't get confused with minus sign
If Mid(strIn, iPos, 1) <> "-" Then
If IsNumeric(Mid(strIn, iPos, 4)) Then
strFourDigits = Mid(strIn, iPos, 4)
Exit Function
End If
End If
Next iPos
End If
End Function

You might be able to use this function to extract the number to a
calculated field for searching. In testing I found one misfeature:
"aa-1-222-bb" returns "222-", apparently because "222-" is recognized
as numeric, presumably equal to negative 222.


John W. Vinson[MVP]
Click to expand...
 
Back
Top