Query crieria help please

[Andy]

Hi

I have a query which contains the following fileds.

Contractor Name
Contractor Invoice Value
Contractor VAT (This is an expression which calculates
17.5% of the Contractor Invoice Value)

Not all of the Contractors in Contractor name are
registered for VAT, so for thos that aren't I need the
Contractor VAT to return me a value of zero.

Hope this makes sense, any help appreciated.

Andy

 

[Tom Ellison]

Dear Andy:

The answer depends on what you have in the [Contractor VAT] column for
those contractors which are not registered for VAT. If these are
recorded with NULLs, and you're using Jet, you could use a calculated
column for it like this:

[VAT Rate]: Nz([Contractor VAT], 0)

In your SQL this would read:

Nz([Contractor VAT], 0) AS [VAT Rate]

If you are using MSDE, the SQL would read:

ISNULL([Contractor VAT], 0) AS [VAT Rate]

Hope this helps.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts