Help with this JET Sql Query [SOLVED]

Question

Hi:

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

Answer 1

Select idInvoice, date, amount, 
amount * case when date<'20100101' then 20 else 35 end as amount from invoices 

Answer 2

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.

Answer 3

or

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