I need help with syntax for Nz function in query

[mthornblad]

Hi

I can't seem to get the syntax correct with trying to add Nz function
to the following.

Tax: Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].[id_Ext])

How do I add the Nz function to id_SalesTaxRate and id_Ext in the
above expression ?

Thanks in advance
Mark

 

[Allen Browne]

Not sure if you just want a zero for null:
Nz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].[id_Ext]),0)

Or perhaps you want to assume a tax rate (e.g. 6%) if none is specified:
Sum(Nz([Invoice Detail].[id_SalesTaxRate], 0.06) * Nz([Invoice
Detail].[id_Ext],0))

 

[mthornblad]

Not sure if you just want a zero for null:
Nz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].[id_Ext]),0)

Or perhaps you want to assume a tax rate (e.g. 6%) if none is specified:
Sum(Nz([Invoice Detail].[id_SalesTaxRate], 0.06) * Nz([Invoice
Detail].[id_Ext],0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi

I can't seem to get the syntax correct with trying to add Nz function
to the following.

Tax: Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].[id_Ext])

How do I add the Nz function to id_SalesTaxRate and id_Ext in the
above expression ?

Thanks in advance
Mark

Allen

I used Nz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].
[id_Ext]),0) that you gave me
and that works great except for one problem. I get 14 digits to the
right of the decimal.

5.40662486106157 this is correct but I want to display this as a
currency. How do just get
5.41 for the result ?

Thanks
Mark

 

[mthornblad]

Not sure if you just want a zero for null:
Nz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].[id_Ext]),0)

Or perhaps you want to assume a tax rate (e.g. 6%) if none is specified:
Sum(Nz([Invoice Detail].[id_SalesTaxRate], 0.06) * Nz([Invoice
Detail].[id_Ext],0))

news:[email protected]...

Hi
I can't seem to get the syntax correct with trying to add Nz function
to the following.
Tax: Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].[id_Ext])
How do I add the Nz function to id_SalesTaxRate and id_Ext in the
above expression ?
Thanks in advance
Mark

Allen

I used Nz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].
[id_Ext]),0) that you gave me
and that works great except for one problem. I get 14 digits to the
right of the decimal.

5.40662486106157 this is correct but I want to display this as a
currency. How do just get
5.41 for the result ?

Thanks
Mark

Allen

I just wanted to add that the current sales tax rate for New York City
is 8.375%. It was 8.25% and they wanted to
raise it to 8.5% but so many people objected that they decided to
split the difference.

Mark

 

[Allen Browne]

To convert the result to currency, wrap the expression in CCur()

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Not sure if you just want a zero for null:
Nz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice
Detail].[id_Ext]),0)

Or perhaps you want to assume a tax rate (e.g. 6%) if none is
specified:
Sum(Nz([Invoice Detail].[id_SalesTaxRate], 0.06) * Nz([Invoice
Detail].[id_Ext],0))

I can't seem to get the syntax correct with trying to add Nz function
to the following.

Tax: Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice
Detail].[id_Ext])

How do I add the Nz function to id_SalesTaxRate and id_Ext in the
above expression ?

Thanks in advance
Mark

Allen

I used Nz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].
[id_Ext]),0) that you gave me
and that works great except for one problem. I get 14 digits to the
right of the decimal.

5.40662486106157 this is correct but I want to display this as a
currency. How do just get
5.41 for the result ?

Thanks
Mark

Allen

I just wanted to add that the current sales tax rate for New York City
is 8.375%. It was 8.25% and they wanted to
raise it to 8.5% but so many people objected that they decided to
split the difference.

Mark

 

[Jamie Collins]

I used Nz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].
[id_Ext]),0) that you gave me
and that works great except for one problem. I get 14 digits to the
right of thedecimal.

5.40662486106157 this is correct but I want to display this as acurrency. How do just get
5.41 for the result ?

This suggests your column [Invoice Detail].[id_SalesTaxRate] is of
type double float (Double), being an approximate data type. In my
experience, tax authorities specify very exact tax rates. Therefore, I
suggest you change your column to a fixed point decimal using the
DECIMAL type. This would coerce your result to DECIMAL, therefore you
still require to explicitly round the value to two decimal places:
clarify with your designer which rounding algorithm (bankers,
symmetrical, etc), and perhaps the appropriate trade authority rules,
account department, auditor, etc (which typically require four or five
decimal places) as these factors will influence your choice of data
type too. FWIW many regulars would suggest using the CURRENCY type
(which is has a fixed scale of *four* decimal places, not two) but I
think it would be too confusing to have a column named SalesTax*Rate*
using CURRENCY.

Jamie.

--

 

[ehorde]

I usedNz(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice Detail].

[id_Ext]),0) that you gave me
and that works great except for one problem. I get 14 digits to the
right of the decimal.

Try..

Nz(ROUND(Sum([Invoice Detail].[id_SalesTaxRate]*[Invoice
Detail].[id_Ext]),2),0)

-Eric