Is it possible to declare and use a varible in a select statement?

Is there anyway you can use a varible in a select statement in SQL?

Reason i ask is because i have a very complect calculationin the select statement and i need to work out percentages still.

Whole query below.

SELECT Equipment.EquipmentCategory, IIf([Invoice].[HiredOnWeekend]=TRUE,
SUM((((DATEDIFF("d",Invoice.HiringDate,Invoice.ReturnDate)-1)*Equipment.EquipmentPriceAfter)+Equipment.Equipm entPriceWeekend)*Invoice.NumberEquipmentHired),
SUM((((DATEDIFF("d",Invoice.HiringDate,Invoice.ReturnDate)-1)*Equipment.EquipmentPriceAfter)+Equipment.Equipm entPriceWeek)*Invoice.NumberEquipmentHired)) AS TotalIncome
FROM Membership INNER JOIN (CustomerDetails INNER JOIN ((Equipment INNER JOIN Invoice ON Equipment.EquipmentID = Invoice.EquipmentID) INNER JOIN Stock ON Equipment.EquipmentID = Stock.EquipmentID) ON CustomerDetails.CustomerID = Invoice.CustomerID) ON Membership.MembershipID = CustomerDetails.MembershipID
WHERE (((Invoice.hiringDate)>=[forms].[FormA5].[startdate].[value]) AND ((Invoice.ReturnDate)<=[forms].[FormA5].[enddate].[value]))
GROUP BY Equipment.EquipmentCategory, Invoice.HiredOnWeekend, Invoice.ReturnDate, Invoice.HiringDate;

Thanks for the help

is it possible to declare and use a varible in a select statement? no.

is there anyway you can use a varible in a select statement in sql?yes.

reason i ask is because i have a very complect calculationin the select statement and i need to work out percentages still.you can still get percentages other ways. How would you like to do it? In the query itself? What fields, etc..?

yes i the query its self. For each of the parts of the if statement i above. i need to add 14&#37; onto the calculation and then subtract 10%25%or 40% depending on the companies membership level. This applies to both branches of the above calculation.

Perhaps you could post the database? HARD TO TELL WHAT'S GOING ON WITHOUT THE WHOLE PICTURE, BUT MAYBE SOMEONE ELSE HAS AN IDEA.

... depending on the companies membership level.

How is that level determined?

Bob

is there a function in sql to add 14&#37; vat onto a total?

if so what is the structure?

is there a function in sql to add 14&#37; vat onto a total?

if so what is the structure?

x = 100 'amt to be taxed
y = 0.14 'tax rate
z = x * (1 + y) 'computation
? z 'amt + tax
114

Bob

ok thanks a million. as usual i'm making things more complicated than they seem. Thanks guys for all your help.