Problems with $ in a Comments Field?

[shcram]

I am a volunteer for an animal shelter. I'm writing year-end reports t
help with grant applications for next year.

Here's my problem. I am using the following code to extract co-paymen
data from a general comments field in my agency's animal data base an
put it in a separate CoPay column in my query:

CoPay:Val(Mid$([Animal].[Comments],InStr([Animal].[Comments],"Co Pa
$")+8))

When the comments field has the entry "Co Pay $NN.NN" in it, the logi
works great. However, there are also lots of times when the fiel
contains "CoPay $NN.NN", "Copay $NN.NN", "Co pay $NN.NN", or n
reference to co payment at all. In those instances it puts garbage i
the "CoPay" field.

Can I expand this statement to include variations of spelling in th
comments field - and have it default to $0 if there are no qualifyin
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, Su

 

[Vincent Johns]

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

Well, I couldn't get Access 2000 to recognize the "$" sign, but using

SELECT Animal.Comments,
Val(IIf(InStr(1,[Animal].[Comments],"Co Pay",1)>0,
Mid$([Animal].[Comments],InStr(1,[Animal].[Comments],"Co Pay",1)+8),
IIf(InStr(1,[Animal].[Comments],"CoPay",1)>0,
Mid$([Animal].[Comments],
InStr(1,[Animal].[Comments],"CoPay",1)+7),"0"))) AS CoPay
FROM Animal;

seemed to work. It's said to be case-insensitive.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.