Wildcards in an Access module

[MBC]

I have created a query which calls a function in a Module that looks at
the call number of each book to determine the SubGroup that book
belongs to.
For instance, Call numbers that begin with F belong in the SubGroup
"AF" and call numbers beginning with "E" belong to SubGroup "Easy".
These all work just fine.
But I'm having problems with call numbers like these
(813,823,843,813.13,815.543,etc.) which need to be in the SubGroup
"AF". (they all begin with 8 and end with 3 & there may be 1-4 places
after the decimal point. A number like 810 would be in a different
SubGroup--it begins with 8 but doesn't end in 3). Call numbers like
these (J813, J823,J813.3, J812.543, etc.) need to be in the SubGroup
"JF".
I've tried something like this in the module:
If StartofCall Like "8#3" Then
SubGroup ="AF"
ElseIf StartofCall Like "8##[.]##3" Then
SubGroup ="AF"
ElseIf StartofCall Like "J8#3" Then
SubGroup ="JF"
ElseIf StartofCall Like "J8##[.] Then
SubGroup ="JF"
I've tried other variations as well, but nothing seems to work. Any
suggestions on how to do this?

 

[Albert D. Kallal]

You can use

strYourNumber like "8*3"

However, you will have to test for number with the "dot" in it first..as


815.543 like "8*3"

Will return true, with, or without the ".". So, to test first, for a
dot...you can use:

815.543 like "8*[.]*3"

 

[MBC]

I don't know what you mean by
strYourNumber like "8*3"
Can you explain a little more? Is this a statement I need to put at the
beginning, before my If Startof Call Like .....?

 

[Albert D. Kallal]

I don't know what you mean by
strYourNumber like "8*3"
Can you explain a little more? Is this a statement I need to put at the
beginning, before my If Startof Call Like .....?

Sure...I kind of thought that since your code looked SOOO close to working,
that you had things all under contorl!!

You have:

If StartofCall Like "8#3" Then
SubGroup ="AF"

So, I am suggesting to use:

If StartofCall Like "8*3" Then
SubGroup ="AF"

So, the whole function that you place in a standard modue woud look like

Public Function MySubGroup(vData As Variant) As Variant

If IsNull(vData) = True Then

' null paseed...exit
Exit Function
End If

If vData Like "8*[.]*3" Then
MySubGroup = "AF"

ElseIf vData Like "8*3" Then
MySubGroup = "AF"

ElseIf vData Like "J8*3" Then
MySubGroup = "JF"

End If

End Function

I am sure you will add "more" to the above "example" code..

So, now, in the query builder, you can add the above collum, like:

MyCoolGroup:MySubGroup([YourFieldNameGoesHere])

Now, try the query...it should produce a collum called mycoolGroup...

 

[MBC]

Right now my function begins like this.
Function SubGroup (Item as String, LOCATION as String, CallNumber as
String, StartofCall As String) As String
Dim this as String

Case "Book"
If StartofCall Like "8*3" Then
SubGroup = "AF"
If StartofCall Like "8*[.]*3" Then
SubGroup = "ANF"
Then there are lots of ElseIf statements. After looking at the 800
call numbers then it needs to look at other types of call numbers
Example: ElseIf StartofCall Like "R" then
SubGroup = "ARef"
ElseIf Startof Call Like "B" Then
SubGroup = "ANF"
etc., etc.
Else SubGroup ="UnBK"
End If

Everything works OK except for the 800 numbers. It looks like using
"8*3" works OK for numbers without a decimal, but "8*[.]*3 doesn't work
for the ones with a decimal point. Am I going to have to put the 800s
in a separate function so it will treat the call number as a variant
instead of a string or is there a way to do it all within this same
function?

 

[Albert D. Kallal]

It looks like using
"8*3" works OK for numbers without a decimal, but "8*[.]*3 doesn't work
for the ones with a decimal point.

You did not read my last post....I will re-quote:


<quote>

You can use

strYourNumber like "8*3"

However, you will have to test for number with the "dot" in it first..as


815.543 like "8*3"

Will return true, with, or without the ".". So, to test first, for a
dot...you can use:

815.543 like "8*[.]*3"

<quote>

Again, since "8*3" will return true WITH, OR WITHOUT the ".", then you have
to test for the dot BEFORE you test for the 8*3 one. Hence, you need to do
the test for 8*[.]*3 BEFORE the 8*3 one.......

 

[MBC]

I tried putting the 8*[.]*3 before the 8*3 and it doesn't make any
difference. At the bottom of my ElseIf statements I have a final one
that is Like "###" then SubGroup = "ANF" which should catch everything
that doesn't begin with 8 and end with 3 but pretty much every call
number with a decimal in it is falling into that category so it doesn't
seem to be matching the 8*[.]*3 with any of my call numbers.