By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,968 Members | 1,691 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,968 IT Pros & Developers. It's quick & easy.

query to return a value

P: 10
Hello to everyone.

Here is a thing, I have been playing around with for a while now and I am still stumped !!!!!!!

I have a table that contains expenses. Some of the values contain VAT and some don’t. I have a tick box to identify the non vat-able amounts. Tick when vat is not applicable returning a value of “-1”

What I would dearly like to be able to do is run a query that will look to the amounts and remove the vat content of the records that require it and if the vat is not to be removed because the tick box is checked then to return the original amount.

E.g.

Amount______Vat y/n______new amount
£117.50________y_________£100.00
£200.00________n_________£200.00

Any ideas, all you clever people?

Regards
Scottie
Aug 30 '08 #1
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
What you need is a conditional algorithm in your query.
For this an IIF(condition,true,false) statement can be used like:
Expand|Select|Wrap|Line Numbers
  1. select Amount, [Vat y/n], IIF([Vat y/n]=True,Amount-Amount*0.18,[Amount]) as NewAmount from tblYours.
  2.  
Getting the idea?

Nic;o)
Aug 30 '08 #2

NeoPa
Expert Mod 15k+
P: 31,347
Perfect response.

I would calculate the pre-VAT value as [Amount]*100/117.5 though myself.
I guess this must be a British questioner from the VAT rate ;)
Aug 30 '08 #3

P: 10
My thanks to you nico5038 and NeoPa.

With a bit of a jiggle this worked great.

NoePa – yes I am in the UK and unfortunately we have to give our bit to the delightful Mr Brown JOY :(

Thanks again
All the best
Scottie
Aug 31 '08 #4

P: 10
BTW – for any one else out there, this is the code that I used. Including the date select for the month.

Regards
Scottie
Expand|Select|Wrap|Line Numbers
  1. SELECT Expenses.date, Expenses.[Amount inc Vat], Expenses.[No Vat], IIf([no vat]=False,[Amount inc vat]/1.175,[Amount inc vat]) AS amount ex vat
  2. FROM Expenses
  3. WHERE (((Expenses.date) Between #mm/dd/yyyy# And #mm/dd/yyyy#));
Aug 31 '08 #5

NeoPa
Expert Mod 15k+
P: 31,347
Thanks for posting Scottie and you're welcome to the help.

I did notice however, a few problems with your posted SQL.
Expand|Select|Wrap|Line Numbers
  1. SELECT Expenses.date, Expenses.[Amount inc Vat], Expenses.[No Vat], IIf([no vat]=False,[Amount inc vat]/1.175,[Amount inc vat]) AS amount ex vat
  2. FROM Expenses
  3. WHERE (((Expenses.date) Between #mm/dd/yyyy# And #mm/dd/yyyy#));
  1. In your SELECT line you remove the VAT if [no vat]=False (rather than if [no vat]).
  2. The ALIAS amount ex vat is not surrounded by [] and has embedded spaces.
  3. The date format strings in the WHERE clause are very nearly correct for format strings (I think you need to use \#), but they aren't usable values in themselves.
Sep 1 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.