Nz Issue - changes cell to Text

[mattc66 via AccessMonster.com]

I need help.. When I add the Nz to the below cell in my query it changes it
from currency to text. What can I do to resolve this issue. I am adding Nz
because I have some records in this cell that contain no data and I am trying
to sum the data in my form.

SLS 2006: Nz([SumOfSLS_2006],0)

 

[Jeanette Cunningham]

Matt,
it would probably be better if a nothing or null amount in currency is left
as a null.
This lets you see where the value for in the currency field is unknown
versus a value or $0.00 that was entered.

try:
SLS 2006: Nz([SumOfSLS_2006],"")

Jeanette Cunningham

 

[George Nicholson]

SLS 2006: Ccur(Nz([SumOfSLS_2006],0))

 

[Allen Browne]

Use IIf() instead of Nz(), like this:
IIf([SumOfSLS_2006] Is Null,0,[SumOfSLS_2006])

Nz() requires a VBA function call, and returns a Variant, which JET
misinterprets as Text (as you said.)

JET handles IIf() differently than the IIf() in VBA.
It's more efficient, less error prone, and gets the data type right.

 

[Jeanette Cunningham]

Thanks Allen,
I have learned so much since answering questions here.
I wouldn't have thought of using IIf because I thought it would make the
query a lot slower.

Jeanette Cunningham

Use IIf() instead of Nz(), like this:
IIf([SumOfSLS_2006] Is Null,0,[SumOfSLS_2006])

Nz() requires a VBA function call, and returns a Variant, which JET
misinterprets as Text (as you said.)

JET handles IIf() differently than the IIf() in VBA.
It's more efficient, less error prone, and gets the data type right.

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

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

I need help.. When I add the Nz to the below cell in my query it changes
it
from currency to text. What can I do to resolve this issue. I am adding
Nz
because I have some records in this cell that contain no data and I am
trying
to sum the data in my form.

SLS 2006: Nz([SumOfSLS_2006],0)

 

[Allen Browne]

Yes, have been watching you answer lots of questions, Jeanette.

Like you, I have learned much from these discussion forums over the years.

One day I hope to write an article on the functions that JET knows about.
IIf() is one of them. AFAIK, they are not documented anywhere, but they are
the ones that work in table design (e.g. validation rule), whereas the other
VBA functions don't.

IIf() is a nifty way to letting JET know the data type. For example in a
Make Table query, if you want a field that's always null, JET has no idea
what data type it should by. So, you use an expression like this:
IIf(False, 0, Null)
False is never true, so you always get the Null and never the 0, but the
presence of the alternative is enough to get JET to create a Number field,
not a Text field.

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

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

Thanks Allen,
I have learned so much since answering questions here.
I wouldn't have thought of using IIf because I thought it would make the
query a lot slower.

Jeanette Cunningham

Use IIf() instead of Nz(), like this:
IIf([SumOfSLS_2006] Is Null,0,[SumOfSLS_2006])

Nz() requires a VBA function call, and returns a Variant, which JET
misinterprets as Text (as you said.)

JET handles IIf() differently than the IIf() in VBA.
It's more efficient, less error prone, and gets the data type right.

I need help.. When I add the Nz to the below cell in my query it changes
it
from currency to text. What can I do to resolve this issue. I am adding
Nz
because I have some records in this cell that contain no data and I am
trying
to sum the data in my form.

SLS 2006: Nz([SumOfSLS_2006],0)

 

[Pat Hartman]

Jeanette,
"" is NOT null, it is a zero-length-string and should not be used to
substitute for numeric values or dates.

Nz() does return a variant so you will most likely need to supply a format
property for the control that displays it in a form or report. Do not use
the Format() function however, since that will convert the numeric value to
a text string.

Matt,
it would probably be better if a nothing or null amount in currency is
left as a null.
This lets you see where the value for in the currency field is unknown
versus a value or $0.00 that was entered.

try:
SLS 2006: Nz([SumOfSLS_2006],"")

Jeanette Cunningham

I need help.. When I add the Nz to the below cell in my query it changes
it
from currency to text. What can I do to resolve this issue. I am adding
Nz
because I have some records in this cell that contain no data and I am
trying
to sum the data in my form.

SLS 2006: Nz([SumOfSLS_2006],0)

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Thanks that worked great.. I have learned alot as well. I appeaciate all the
help over the years.

Yes, have been watching you answer lots of questions, Jeanette.

Like you, I have learned much from these discussion forums over the years.

One day I hope to write an article on the functions that JET knows about.
IIf() is one of them. AFAIK, they are not documented anywhere, but they are
the ones that work in table design (e.g. validation rule), whereas the other
VBA functions don't.

IIf() is a nifty way to letting JET know the data type. For example in a
Make Table query, if you want a field that's always null, JET has no idea
what data type it should by. So, you use an expression like this:
IIf(False, 0, Null)
False is never true, so you always get the Null and never the 0, but the
presence of the alternative is enough to get JET to create a Number field,
not a Text field.

Thanks Allen,
I have learned so much since answering questions here.

[quoted text clipped - 21 lines]

SLS 2006: Nz([SumOfSLS_2006],0)

 

[Jeanette Cunningham]

Thanks Pat

Jeanette Cunningham

Jeanette,
"" is NOT null, it is a zero-length-string and should not be used to
substitute for numeric values or dates.

Nz() does return a variant so you will most likely need to supply a format
property for the control that displays it in a form or report. Do not use
the Format() function however, since that will convert the numeric value
to a text string.

Matt,
it would probably be better if a nothing or null amount in currency is
left as a null.
This lets you see where the value for in the currency field is unknown
versus a value or $0.00 that was entered.

try:
SLS 2006: Nz([SumOfSLS_2006],"")

Jeanette Cunningham

I need help.. When I add the Nz to the below cell in my query it changes
it
from currency to text. What can I do to resolve this issue. I am adding
Nz
because I have some records in this cell that contain no data and I am
trying
to sum the data in my form.

SLS 2006: Nz([SumOfSLS_2006],0)

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com

 

[Jamie Collins]

One day I hope to write an article on the functions that JET knows about.
IIf() is one of them. AFAIK, they are not documented anywhere

FWIW Brendan Reynolds has done a series of blogs documenting the VBA
functions that may be used in Jet SQL expressions. See:

http://brenreyn.blogspot.com/

IIf() is a nifty way to letting JET know the data type. For example in a
Make Table query, if you want a field that's always null, JET has no idea
what data type it should by. So, you use an expression like this:
IIf(False, 0, Null)
False is never true, so you always get the Null and never the 0, but the
presence of the alternative is enough to get JET to create a Number field,
not a Text field.

It's a good idea which sadly does not work for DECIMAL data (which is
particularly important because the CDEC() expression is broken in Jet)
e.g. this:

SELECT IIF(False, 0.1, NULL) AS decimal_col
INTO DropMe
FROM AnyTable;

noting that a native decimal such a 0.1 is considered as being DECIMAL
by the engine e.g.

SELECT TYPENAME(0.1);

returns 'Decimal'.

Something that does work is to create a derived table being the UNION
of a DECIMAL value and the NULL value then removing the non-NULL
values using a WHERE clause e.g.

SELECT DT1.decimal_col
INTO TargetTable
FROM
(
SELECT 0.1 AS decimal_col, 0 AS keeper
FROM SourceTable
UNION ALL
SELECT NULL, 1
FROM SourceTable
) AS DT1
WHERE DT1.keeper = 1;

Note that neither approach seems to work with REAL (Single). In a
quick test, for me both result in a column of type FLOAT (Double).

Jamie.

--