shcram said:
I am a volunteer for an animal shelter. I'm writing year-end reports to
help with grant applications for next year.
Here's my problem. I am using the following code to extract co-payment
data from a general comments field in my agency's animal data base and
put it in a separate CoPay column in my query:
CoPay:Val(Mid$([Animal].[Comments],InStr([Animal].[Comments],"Co Pay
$")+8))
When the comments field has the entry "Co Pay $NN.NN" in it, the logic
works great. However, there are also lots of times when the field
contains "CoPay $NN.NN", "Copay $NN.NN", "Co pay $NN.NN", or no
reference to co payment at all. In those instances it puts garbage in
the "CoPay" field.
Can I expand this statement to include variations of spelling in the
comments field - and have it default to $0 if there are no qualifying
references? Something like:
If "Co Pay $" or "CoPay $" or "Co pay $" or "Copay $"
then field = NN.NN
else field = 0.00
Anyone have any ideas?
Thanks, Sue
schram,
Yes.
"Co Pay" information should be in a column named CoPay (or possibly
another design choice, but that is the basic one). It should be a
CURRENCY data type. This way you will not have to worry about using
string functions to extract data from comments-type columns.
"Comments"-type columns should never, ever, contain information that
will be involved in calculations of any type.
This column also contains multiple values. Not only is a dollar
amount recorded, but its purpose, "Co Pay".
These are sign-posts that there are serious problems with the
database's design (they all go against the database normalization
rule "1st Normal Form"; see below for links regarding database
normalization).
The type of problem you are encountering will arise again and again
unless the design is corrected.
------------------------------
If you are stuck with what you have, then, depending on the contents
of Comments column are, things might be easy, or tough.
From the information you have given, the *end* of the comments
string is always $NN.NN.
If this is true, then you can simply extract that data using a
CCur(RIGHT(Animal.Comments, 5)) expression. If the exact width of
the string decimal value is variable, then you can InStr for the
"$".
However, I must wonder if there isn't a requirment to identify that
the value of the Comments column in any particular row is a "Co
Pay", or not. If this is so, then you will have to determine,
manually, all the possible variations that indicate "Co Pay", and
write code to successfully identify them.
This does sound like a terrible solution, and I apologize. But now
you know what I said what I did above regarding including values
involved in calculations
Specific Example:
I would try SWITCH(), since its syntax is a little more regular than
lots of nested Iif statements.
IIf(Switch(Left(Animal.Comments, 8) = "Co Pay $", True
,Left(Animal.Comments, 7) = "CoPay $", True
,Left(Animal.Comments, 6) = "CoPay$", True)
,<True Code>
,<False Code>)
You would add as many elements to the Switch() as necessary.
The length of this expression may become so great that it would
become convenient to hide it in a VBA function.
Public Function ExtractCoPay(InCoPay As String) As Currency
Dim OutCoPay As Currency
OutCoPay = _
IIf(Switch(Left(Animal.Comments, 8) = "Co Pay $", True _
,Left(Animal.Comments, 7) = "CoPay $", True _
,Left(Animal.Comments, 6) = "CoPay$", True) _
,1 _
,0)
ExtractCoPay = OutCoPay
End Function
Note: (The 1 and the 0 in the function above are just temporary
place-holders that you will replace with whatever you want to do for
those true and false cases.)
You would use "CoPay:ExtractCoPay(Animal.Comments)" in the query
grid.
------------------------------
Database Normalization:
---------------------------
Googling on Database Normalization will provide many good articles.
Most of them are written for other database professionals (the
actual written rules of Database Normalization will throw you for a
loop if you don't know exactly what they are talking about).
Database Normalization is a process of decomposition, of breaking
down information until you get down the basic elements that describe
entities, and making sure that the information kept about an entity
(i.e. "table") *really* belongs to it, and not to something else (or
somewhere else).
---------------------------
Database Normalization:
Basics:
About.com
http://databases.about.com/od/specificproducts/a/normalization.htm
Intermediate:
MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
Advanced:
Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization
Very Advanced:
University of Texas:
(I quite like this whole site, since it has a handy menu on the
right
describing many important aspects of database normalization and
modeling.)
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html