Clumsy Switch Coding Help

[Steve183]

I have a rather clumsy code that is in an access query-grid. It’s a donation database with a donation amount which trips a txtbox on a subform within a form. When a certain amount of money is donated the txtbox fills in with a donation level. I was thinking that there must be some type of coding in VB that would do the trick as I am almost maxed out on the number of characters that can be put into the query-grid. This code is working.

I’m a newbe to access and was wondering if anyone could steer me in the right direction. I’m assuming of course that any VB code would go into a txtbox “after the update” properties.

Switch Code​

Level: Switch([SumOfDonationAmount] Between 1 And 999," Supporter",
[SumOfDonationAmount] Between 1000 And 4999," Contributor",
[SumOfDonationAmount] Between 5000 And 9999," Alumni",
[SumOfDonationAmount] Between 10000 And 14999," Vanguard Member",
[SumOfDonationAmount] Between 15000 And 19999," Team Canada",
[SumOfDonationAmount] Between 20000 And 34999," Vanguard Member with Honors ",
[SumOfDonationAmount] Between 35000 And 49999," Benefactor",
[SumOfDonationAmount] Between 50000 And 74999," Premier Benefactor",
[SumOfDonationAmount] Between 75000 And 99999," Guarantor",
[SumOfDonationAmount] Between 100000 And 149999," Humanitarian",
[SumOfDonationAmount] Between 150000 And 249999," Humanitarian with Honors",
[SumOfDonationAmount] Between 250000 And 499999," Silver Humanitarian",
[SumOfDonationAmount] Between 500000 And 999999," Gold Humanitarian",
[SumOfDonationAmount] Between 1000000 And 2499999," New Civilization Builder")

 

[boblarson]

1. I think you mean VBA and not VB. (and why I am moving this to the VBA area)

2. You can use a function to return the value - OR you can use a table of values along with a query.

 

[Steve183]

Thanks boblarson. I'll give it a try.