Case Statement

[RexesOperator]

My first Case Statement!

I need to choose a value based on a date:

Select Case DATERECEIVED
< 01/01/1991 GST = 0
BETWEEN 01/01/1991 AND 01/07/2006 GST = .07
BETWEEN 01/07/2007 AND 31/12/2007 GST = .06
> 31/12/2007 GST =.05

I have no idea how to set this up (yes I have looked at examples, but I have not found any I can use as a template. This is part of a sales tax calculation in a report (Canucks will recognize this as the GST). I need to have the correct value for any past reports requested.

I have set this up as a gigantic IIF Statement (it works, but it's clumsy). If these values change, it will be much easier for whoever maintains this db to update them from a case statement.

I just need to know the format for the dates and ranges. I think I can figure out the syntax from there.

 

[pbaldy]

Actually, I would put the values in a table with fields for start and end dates plus the value. Then it's more dynamic, easy to do lookups into or you can join that table to others in queries to get your value.

You realize there's a gap in your time periods there?

In a previous life I was the supervisor of a tax department here and I used to deal with the GST. I didn't realize it had gone down since then. It's nice to know a tax can actually go down!

 

[RexesOperator]

Actually, I would put the values in a table with fields for start and end dates plus the value. Then it's more dynamic, easy to do lookups into or you can join that table to others in queries to get your value.

You realize there's a gap in your time periods there?

In a previous life I was the supervisor of a tax department here and I used to deal with the GST. I didn't realize it had gone down since then. It's nice to know a tax can actually go down!

Thanks for pointing out the gap.

I thought of using a table, but if I can get this going, it would be a lot simpler for me to deal with. I use this value in only one field in only one report.

The field GST is actually a text string. I use a text box to do the actual calculation.

The GST is hated by most Canadians. The Conervatives vowed to eliminate it. But it is such a cash-cow even they couldn't resist keeping it. As a compromise they lowered it just enough to keep people thinking taxes will go down under their government. Who knows what will happen with the current economic situation.

I think I am getting close. Would the format for date be

Select Case DATERECEIVED

< #01/01/1991# GST = 0
Between #01/01/1991# and #01/07/2006# GST = .07
Between #01/07/2006# and #01/01/2008# GST = .06
> #01/01/2008# GST = .05

End Case

 

[pbaldy]

I don't like any design that requires design changes when a rate changes, but it's your baby. The structure would look like:

Select Case DATERECEIVED
  Case Is < #01/01/1991#
    GST = 0
  Case #01/01/1991# To #01/07/2006#
    GST = .07
...

 

[RexesOperator]

I noticed you have Case Is on the first line but not the subsequent one?

 

[pbaldy]

Yes; if you look in Help, you'll see how/when to use it.

 

[RexesOperator]

OK - thanks for your help - it works.