Extracting $ Data from Alpha Field?

[shcram]

My DB ("Animal") has a field ("Comments") that contains monitar
information in some cases. When present, it is "Vet $164.00, Copa
$25.00". (Other, non monitary information can also be present.)

My agency needs to know the total amount of "Vet" and "Copay" monie
for this year. I need to write a query to extract the $ information i
this field, store it in its own column, and print and total the colum
in a subsequent report.

I think this will work, but not sure. I would have two columns in m
query as follows:

Vet:Mid$([Animal].[Comments],InStr([Animal].[Comments],"Vet $")+1)
Copay:Mid$([Animal].[Comments],InStr([Animal].[Comments],"Copay $")+1)

If I understand the manual, this will "parse" the data that follow
"Vet $" and "Copay $" and put it in its own field. Am I on the righ
track?

Thanks,
Su

 

[Marshall Barton]

My DB ("Animal") has a field ("Comments") that contains monitary
information in some cases. When present, it is "Vet $164.00, Copay
$25.00". (Other, non monitary information can also be present.)

My agency needs to know the total amount of "Vet" and "Copay" monies
for this year. I need to write a query to extract the $ information in
this field, store it in its own column, and print and total the column
in a subsequent report.

I think this will work, but not sure. I would have two columns in my
query as follows:

Vet:Mid$([Animal].[Comments],InStr([Animal].[Comments],"Vet $")+1)
Copay:Mid$([Animal].[Comments],InStr([Animal].[Comments],"Copay $")+1)

If I understand the manual, this will "parse" the data that follows
"Vet $" and "Copay $" and put it in its own field. Am I on the right
track?

Well almost, but ...

The Mid function calls you have will include the entire
remainder on the comments after the V in Vet, which is way
more stuff than you want. I think you want to use the third
argument to specify how much ot comments to extract:

Mid$([Animal].[Comments], InStr([Animal].[Comments],
"Vet $") + 6, InStr([Animal].[Comments], ", ") -
InStr([Animal].[Comments], "Vet $") - 6)

On the other hand, you might be able to use this instead:
Val(Mid$([Animal].[Comments], InStr([Animal].[Comments],
"Vet $") + 6)

Now for the big BUT. You are relying on the data to be in
an exact form with exactly one space between Vet and $, that
no one will ever enter anything other than "Vet", and that
the $ will always be there. If anyone ever enters
Vetinary:150, it just won't work. These are the kind of
things people do that make parsing such an inexact, if not
impossible activity and a big reason why the rules of
database Normalization prohibit multiple values in a single
field. But you have to play the hand you are dealt, even if
it is a loser.

 

[shcram]

Yes, it's a bad data base construct. We're waiting for our designer t
add fields to store money, and in the meantime we had to come up with
way to retain the data until the new fields are available.

The "comments" field was the only one not already used for somethin
else, so we tried to include delimiter information that would allowu
to retrieve the data for end of year reporting.

It would be a great help to have a manual that includes the syntax fo
the available functions. They are not all included in the materia
that I have (Running Access 2000 by Viescas). Can you recommen
something?

Meanwhile, I'm trying to understand these commands using intuition s
let's see if I've got this broken down right:

1st Arg: Instr([Animal].[Comments],"Vet $")+6,
*This says look for Vet $ and take the next 6 digits
2d Arg: InStr([Animal].[Comments],",") -
*This says stop looking when you get to the comma ?
3d Arg: InStr([Animal].[Comments],"Vet $") - 6
* This tells how much of the data to extract?

And what if the entry is "Vet $27.20," - 5 digits instead of 6? Sur
wish I had a good reference! Is the command saying "take up to
digits but stop at the comma"?

Thanks in advance

 

[Marshall Barton]

Responses inline below

Yes, it's a bad data base construct. We're waiting for our designer to
add fields to store money, and in the meantime we had to come up with a
way to retain the data until the new fields are available.

The "comments" field was the only one not already used for something
else, so we tried to include delimiter information that would allowus
to retrieve the data for end of year reporting.

It would be a great help to have a manual that includes the syntax for
the available functions. They are not all included in the material
that I have (Running Access 2000 by Viescas). Can you recommend
something?

The definitive reference document for the VBA functions is
the VBA Help file.

When viewing any VBA module in the VBE window, open Help -
Microsoft Visual Basic Help. Select the Contents tab and
navigate to Visual Basic Language Reference - Functions.

Meanwhile, I'm trying to understand these commands using intuition so
let's see if I've got this broken down right:

1st Arg: Instr([Animal].[Comments],"Vet $")+6,
*This says look for Vet $ and take the next 6 digits

No. Actually that's the 2nd arg of the Mid function. It
says look for the start of Vet $ and add 6 to that position,
which was supposed to be the position of the character after
the $
That's a mistake, it should be 5 instead of 6

2d Arg: InStr([Animal].[Comments],",") -
*This says stop looking when you get to the comma ?

3d Arg: InStr([Animal].[Comments],"Vet $") - 6
* This tells how much of the data to extract?

These two are both part of a single expression that is the
third arg to the Mid function.

Unfortunately, I had a another major mistake in this one.
The objective is to figure out how many characters there are
between the "$" and the ", "
What I should have written:

InStr(InStr([Animal].[Comments],"Vet $")+5,
[Animal].[Comments], ", ") - InStr([Animal].[Comments],"Vet
$") - 5,

And what if the entry is "Vet $27.20," - 5 digits instead of 6? Sure
wish I had a good reference! Is the command saying "take up to 6
digits but stop at the comma"?

The 6 (should be 5) is not the length of the 27.50. it's the
length of "Vet $". The above (corrected) expression
calculates the length of the amount value.

When you see the confusing complexity of this entire
expression, you can see why I suggested trying the
alternative:
Val(Mid$([Animal].[Comments], InStr([Animal].[Comments],
"Vet $") + 5)