StartOf search for field with blank spaces

[mbc04]

I am running a query that counts all Adult and Juvenile audio book
cassettes. I have call numbers like BOOK CAS J GANTOS (which is a
juvenile cassette) and BOOK CAS F SMITH (which would be an adult
cassette). The J or the F is the important thing here to determine
whether the item is Adult or Juvenile. Some juvenile cassettes also
start with Z (that part works fine). Here is part of the module I've
used for this.

Case "Audio Book Cassette"
If StartofCall Like "Z??" Then
SubGroup = "JCAS"
ElseIf StartofCall Like "BOOK CAS J*" Then
SubGroup = "JCAS"
Else
SubGroup = "ACAS"
End If

It seems to not be recognizing the spaces between the letters and is
counting everything as ACAS. If I use somthing like this
ElseIf StartofCall Like "BOO*" Then
SubGroup = "JCAS", then it will return it as JCAS.
Unfortunately, it also counts the Adult cassettes as Juvenile as well.
What can I do

 

[Guest]

You should have the data in four separate fields.

 

[John Spencer]

Hard to say. What is in the variable StartOfCall?

Try adding
Debug.Print ":" & StartOfCall & ":"
to your code after
SubGroup = "ACAS"
Then you can view what you are comparing to the string.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I'm with Karl on this one. You need to normalize your database. You should
have a "Age_Cat" column to store this information. It will significantly
speed up any aspect of your application that has to filter or count or any
number of other operations to identify books in one or more of those
categories.

Dale

 

[mbc04]

I'm with Karl on this one. You need to normalize your database. You should
have a "Age_Cat" column to store this information. It will significantly
speed up any aspect of your application that has to filter or count or any
number of other operations to identify books in one or more of those
categories.

Dale
--
Email address is not valid.
Please reply to newsgroup only.







- Show quoted text

I've discovered that
ElseIf InStr(1, CallNumber, "BOOK CAS J") Then
SubGroup = "JCAS"
seems to do the trick!