0

I need help to do this query select.

for example I have these fields:

  • idInvoice
  • date
  • amount

Depending of the date I need multiply the field "amount" for x number or other one.

For example, if the date is less 01/01/2010 to multiply for 20 . But if it is major or equal to multiply for 35

Select idInvoice, date, amount, amount * varNumber from invoices
razlebe
  • 7,134
  • 6
  • 42
  • 57
aco
  • 819
  • 4
  • 15
  • 32
  • SOLVED: IS POSSIBLE DOING "UNION" – aco Jun 17 '10 at 14:58
  • It is considered acceptable to answer your own question. If you've solved the problem, you might consider submitting your solution as an answer. – Eric Jun 17 '10 at 15:16
  • but how i can submit my solution as answer? only I can edit title. – aco Jun 17 '10 at 15:57

3 Answers3

1

Assuming your date field does not allow Nulls, you should be able to use an IIf expression for your calculation.

SELECT
    idInvoice,
    [date],
    amount,
    IIf([date] < #2010/01/01#, amount * 20, amount * 30) AS extended_amount
FROM invoices;

Notice I enclosed the date field name in square brackets because date is a reserved word in Access. The square brackets signal the database engine that you want a field named "date" rather than the Date() function.

Also Access date fields include a time component. So that query will multiple amount by 20 for any values earlier than midnight at the start of this year. That means for 1/1/2010 at 1:00 AM, amount would be multiplied by 30. If that isn't what you want, change the IIf expression.

And if your [date] field allows Nulls, look at Access' help for the Nz() function.

HansUp
  • 95,961
  • 11
  • 77
  • 135
1

or

SELECT 
    idInvoice, 
    [date], 
    amount, 
    amount *IIf([date] < #2010/01/01#, 20, 30) AS extended_amount 
FROM invoices;
Madhivanan
  • 13,470
  • 1
  • 24
  • 29
-1
Select idInvoice, date, amount, 
amount * case when date<'20100101' then 20 else 35 end as amount from invoices
Madhivanan
  • 13,470
  • 1
  • 24
  • 29