search pattern in access module

[MBC]

I'm looking for a search pattern to match call numbers in a library
catalog database. I want to find all call numbers that begin with 8 and
end with 3. The call numbers are anywhere from 3-7 digits,with or
without a decimal point in them. The call number field is designated as
text. The dewey number is followed by a cutter which is alphanumeric
ex. 813 MAR GERMAN, 815.2993 LEN FRENCH, 866.13 GRI SPANISH and
unfortunately, some also have volume numbers at the end like v. 3
The function looks like this:
Function SubGroup(Item As String, LOCATION As String, CallNumber As
String, StartofCall As String) As String
Dim this As String

If StartofCall Like "8*[.]*3" Then SubGroup = "AF"
ElseIf StartofCall Like "8*3" Then SubGroup = "AF"
(Then there are other If statements that look for other types of
call numbers--this part works fine. Then I have
ElseIf StartofCall Like "###" Then SubGroup ="ANF" (trying to catch
all the call numbers that don't match any of the above possibilities)
Else SubGroup = "UnBK"
End If
This works pretty well with the call numbers that don't have a decimal
in them but is not working at all for those that do. For instance it's
matching on numbers like 813.54 (the part of the number before the
decimal begins with 8 and ends with 3, but that's not what I want)or
812.45 DEB FRENCH v. 3 and grouping them as AF when they should be ANF.
It seems to be ignoring the part of the dewey that comes after the
decimal. I want it to look at the whole dewey number and ignore all the
alphanumeric stuff that comes after it. I've tried other variations
like "8##[.]*3" and "8??[.]*3", etc.
Can anyone help?

 

[Marshall Barton]

I'm looking for a search pattern to match call numbers in a library
catalog database. I want to find all call numbers that begin with 8 and
end with 3. The call numbers are anywhere from 3-7 digits,with or
without a decimal point in them. The call number field is designated as
text. The dewey number is followed by a cutter which is alphanumeric
ex. 813 MAR GERMAN, 815.2993 LEN FRENCH, 866.13 GRI SPANISH and
unfortunately, some also have volume numbers at the end like v. 3
The function looks like this:
Function SubGroup(Item As String, LOCATION As String, CallNumber As
String, StartofCall As String) As String
Dim this As String

If StartofCall Like "8*[.]*3" Then SubGroup = "AF"
ElseIf StartofCall Like "8*3" Then SubGroup = "AF"
(Then there are other If statements that look for other types of
call numbers--this part works fine. Then I have
ElseIf StartofCall Like "###" Then SubGroup ="ANF" (trying to catch
all the call numbers that don't match any of the above possibilities)
Else SubGroup = "UnBK"
End If
This works pretty well with the call numbers that don't have a decimal
in them but is not working at all for those that do. For instance it's
matching on numbers like 813.54 (the part of the number before the
decimal begins with 8 and ends with 3, but that's not what I want)or
812.45 DEB FRENCH v. 3 and grouping them as AF when they should be ANF.
It seems to be ignoring the part of the dewey that comes after the
decimal. I want it to look at the whole dewey number and ignore all the
alphanumeric stuff that comes after it. I've tried other variations
like "8##[.]*3" and "8??[.]*3", etc.

Because the stuff that might come after the number part is
unstructured, you should try to isolate the number before
using your pattern matching sequence.

It looks like you may be able to rely on the number not
contaiing any space characters, so you can use space as the
separator between the number and the rest of the field.

strCallNum = Left(StartofCall, _
InStr(StartofCall & " ", " ") - 1)
If strCallNum Like "8*3" Then SubGroup = "AF"
. . .

 

[Guest]

Although I don't see why it should hurt, why is the '.' in brackets? It isn't
a special character in Microsoft's concept of wildcards.

What does StartOfCall look like? First of all, it looks to be redundant with
the call number string itself (at least the start of it). Why can't it be
derived from call number at the location as StartOfCall = left(callNumber,
InStr (1, CallNumber, " ")), splitting off the StartOfCall at first space?

 

[MBC]

Someone else created this function for me to get me started and then
I've modified and added to it to get it to this point. Unfortunately
that person is no longer here for me to ask for help. I'm new at this
and have basically just been figuring it out as I go along. I did try
the '.' without the brackets to no avail. I agree the StartofCall seems
redundant. I don't know why it was done that way. Since sending my
message, I did try truncating the call number which solved the problem
of the v. 3 at the end but doesn't resolve the problem with the decimal
point.

The whole numbers are OK but numbers like 823.59 (where the 3 comes
right before the decimal) come up as AF because it is 8*3, and all the
others come up as UnBk. I tried Like "8*3 " with a space at the end
and "8*3 ?" thinking that would only get the whole numbers but that
didn't work either. This is driving me nuts. There must be a solution
somewhere.

 

[Guest]

MBC,

I know this is a long time after your original post, and you've probably
worked this out already. But on the off chance that you haven't, I think the
problem is that the two cases overlap logically and you probably need to do
something along the lines of:

1. All of the call numbers begin with numeric strings. So first isolate the
numeric only part by using VAL(). This will return only the numeric part of
the call number. So even call numbers like '815.2993 LEN FRENCH V.3' when
processed through val() will return only the number part (815.2993).
2. The two tests that you have overlap logically. '813.54' for example will
not be selected by the 'LIKE "8*.*3"' test (it ends in 4), but it will then
be caught by the 'LIKE "8*3"' test. The best way around this is probably a
nested if...then...else...end if, as in:
CallNumber = TRIM(STR(VAL('815.2993 LEN FRENCH V.3'))) ' CallNumber =
"815.2993" (It is a string of digits)
IF (CallNumber LIKE "8*3") THEN
' These tests will guarantee that the string starts with an '8' and ends
with a '3'
IF (LEFT(CallNumber, 1) = "8") AND
(RIGHT(CallNumber, 1) = "3")
THEN SubGroup = "AF"
END IF
ELSEIF ....... ' Other tests
END IF

The LEFT() and RIGHT() tests will ensure that the string is starts and ends
with the digit you want. If it was necessary to differentiate between
815.2993 and 8152993 (no decimal point), you'd have to add a test in the
sequence using the InStr() function to recognize the presence or absence of
the '.'"

Good Luck!