Else If

  • Thread starter Thread starter Tracy
  • Start date Start date
T

Tracy

I don't have much experience with this, so please, when
you respond, type slowly, so I can understand :)

Our database stores sales tax in 4 different columns,
Sales_tax_rate1, Sales_tax_rate2, etc. I need to find
which one of those 4 have a sales rate populated. How do
I retrieve the sales rate? I am assuming that somehow I
do an Else if statement saying something like if
Sales_tax_rate1>0 then give me that number, but if it is
not >0, go to Sales_tax_rate2 and see if it is greater
than 0 and so on until if finds a tax rate. I will then
do calculations based on the number I find.

Thank you.
Tracy
 
Tracy,

Typing so slowly that I'm not even going to answer your question until
you answer one of mine :-)

Is the structure of this database set in concrete, or could a table
design modification be considered? Having the 4 sales tax rates in 4
different fields is a bad idea, flouts basic database design
principles, and will ultimately make working with this database more
difficult and complicated than it needs to be. You need another
table, with the data all listed in a single field.

- Steve Schapel, Microsoft Access MVP
 
The structure is set in concrete as it is what our
accounting system is built on, so changing/altering is out
of the question.

What next?

Thanks for the quick response by the way.

Tracy
 
Tracy,

Sorry to hear the bad news.

Am I correct in my reading of your original post, that only one of the
4 fields will have a non-zero rate entered? If so, I think I would
make a calculated field in a query, like this:
RateToUse:
Nz([Sales_tax_rate1,0)+Nz([Sales_tax_rate2,0)+Nz([Sales_tax_rate3,0)+Nz([Sales_tax_rate4,0)

If my assumption is wrong, and there may be a rate entered in more
than one column, please let us know how you decide which one is "the"
one.

- Steve Schapel, Microsoft Access MVP
 
Back
Top