Why is Nz Function in expression converting my data to a string?

[pmjewett]

Hello,



I don't have a lot of experience with Access and I'm having trouble determining the results received from a field expression that is using the NZ function. The expression is M-BEGIN INVENT: Nz([current.begin invent],0) where "current.begin invent" is datatype "number" in the source table. The issue is that this expression is leaving the format of the field as a "string" so when exported to Excel it must be converted back in order to run mathematical calculations on the results.

Trying to read up on this but not getting anywhere.

Any assistance would be greatly appreciated!

Thanks

[Minty]

It's a coercive type conversion. Which could be interpreted as a bug . Try Adding 0 to your result

 M-BEGIN INVENT: Nz([current.begin invent],0)+0

[isladogs]

Also try putting the field itself in []

M-BEGIN INVENT: Nz([current].[begin invent],0)

[pmjewett]

"It's a coercive type conversion. Which could be interpreted as a bug . Try Adding 0 to your result"
Weird. That appeared to work however the values in the filed are "invisible" and only appear when I click on them. See attached screenshot.

[pmjewett]

"Also try putting the field itself in []"

Hi, This unfortunately had no effect. Still getting a string. Thanks though.

[pmjewett]

Dupe response.

[JoeM]

It's a coercive type conversion. Which could be interpreted as a bug

I don't know if I would call it a "bug". It is just more understanding how it works, and what it returns.

pmjewettm,
If you cannot see it, how do you know it is still a string?
Can you post the SQL code of your query?

[pmjewett]

JoeM,

Screwed up my responses on this thread..apologies. Below clarification...
M-BEGIN INVENT: Nz([current.begin invent],0)+0 (This works but the values in the field are "hidden" and only visible when I click on them.
I know this correctly leaves them as numbers because they are right-justified and when right-clinking on the field I get the "numbers filter" in the contextual filter.

However my colleague just tried flipping the order of operation on the above...
M-BEGIN INVENT: 0+Nz([current.begin invent],0)





and that worked. Meaning correctly numbers now AND visible. Weird

pmjewettm,

If you cannot see it, how do you know it is still a string?
Can you post the SQL code of your query?[/QUOTE]

[pmjewett]

Spoke too soon. Changing the order of operation 0+ Nz([current.begin invent],0) still leaves me with an "invisible field" that I can only see when I click on it.

JoeM,

Screwed up my responses on this thread..apologies. Below clarification...
M-BEGIN INVENT: Nz([current.begin invent],0)+0 (This works but the values in the field are "hidden" and only visible when I click on them.
I know this correctly leaves them as numbers because they are right-justified and when right-clinking on the field I get the "numbers filter" in the contextual filter.

However my colleague just tried flipping the order of operation on the above...
M-BEGIN INVENT: 0+Nz([current.begin invent],0)





and that worked. Meaning correctly numbers now AND visible. Weird

pmjewettm,

If you cannot see it, how do you know it is still a string?
Can you post the SQL code of your query?

[/QUOTE]

[isladogs]

I confess to be somewhat bemused by this thread but suggest you try 'forcing' a number value using CInt or Clng or CSng or CDbl depending on the type of number you will have. Doing that means you can remove the +0 component again.

e.g. CInt(Nz([current].[begin invent],0))

[ssanfu]

Just so you are aware,

you should NOT use spaces in object names. (Better options are "BeginInvent" or "Begin_Invent")

"Current" is a reserved word in Access and shouldn't be used for object names. Plus it is not very descriptive.

[JoeM]

I don't think the "hidden" think has anything to do with the calculation itself, but you may have something else going on there (what kind of Formatting do you have on this field?).

See if you can create a new database, with a stripped down version of the problem.
Then if the problem still exists, upload the database for analysis.
If the problem does not exist, then you know that the "hidden" issue has nothing to do with your calculation.