Having trouble with SWITCH - Microsoft Access Expert 25


	Back to Access Expert 25 Comments List

Having trouble with SWITCH

Yonatan Weinberg

2 years ago

I'm trying to make a field in a Query that will calculate SDLT(a property tax in the UK), I'm using the Switch function but it only works if the calculation is a simple one Like this: Switch([PurchasePrice]<=40000,[PurchasePrice]*0).

However when I make it a bit more comlicated like this: Switch([PurchasePrice]<=925000,[PurchasePrice]-250000*0.08+7500) all of a sudden it just skips parts of the calculation.

If anyone can help me understand what I'm doing wrong I would really appreciate it.

Many thanks,
Yonatan

	Kevin Robertson @Reply 2 years ago
Do the calculation first then use the Switch function based on the calculated field. You will probably need 2 queries for this.

	Yonatan Weinberg OP @Reply 2 years ago
How would I do this?

	Kevin Robertson @Reply 2 years ago
See this post from last week: Switch Function

	Yonatan Weinberg OP @Reply 2 years ago
Hi Kevin, Thank you for your help, I can't seem to figure it out. What do I need to concatenate? Also how would another Query help? What I'm trying to do is to manipulate the data in my field called PurchasePrice which is a currency field. Hope I'm not being too complicated, Thank you

	Richard Rost @Reply 2 years ago
Are you trying to CHANGE the PurchasePrice or set a value in a new calculated field? Let's see EXACTLY what you're trying to do (screen shots help).

	Yonatan Weinberg OP @Reply 2 years ago
I'm tyring to set a value in a new calculated field

	Yonatan Weinberg OP @Reply 2 years ago

	Yonatan Weinberg OP @Reply 2 years ago

	Kevin Robertson @Reply 2 years ago
Not sure what the problem is. Isn't '237, 500 the correct value?

	Yonatan Weinberg OP @Reply 2 years ago
No the correct answer should be 7,500

	Kevin Robertson @Reply 2 years ago
You will need parentheses. Remember Multiplication comes BEFORE Addition and subtraction. 250,000 - 250,000 * 0.08 + 7,500 = 237,500 250,000 - (250,000 * 0.08) + 7,500 = 237,500 *(250,000 - 250,000) 0.08 + 7500 = 7,500**

	Yonatan Weinberg OP @Reply 2 years ago
Wow, Thanks Kevin! It's working now, is there anywhere that Richard covers this?

	Kevin Robertson @Reply 2 years ago
The Order of Operations is mentioned in Access Expert 8

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Expert 25.

Next Unseen