NZ function returning non-printable character symbol

[Daniel Klann]

Hi everyone,

I've searched the knowledge base and Deja for this problem and cannot find a
solution. Hope someone can help!

I have a query which uses the NZ function to ensure that instead of a null,
the query will return a zero :

SELECT Nz([Table1]![LocationID],0) AS LocID, Table1.LocationDescription FROM
Table1;

This is an example, not the actual query, but I have the same problem.
LocationID in Table1 is defined as long integer.

On my machine the query runs as expected. The LocID field returns the
location number and a zero if there are any null values. However, on a
couple of colleagues machines the field is populated entirely with the

character (i.e. the symbol for an unprintable character), even if the
underlying value is not null. All other fields are fine.

Both my colleagues and myself are using Access 2000 on Windows NT4. I've
created a database from scratch and the same problem exists on their
machines so I don't think it's a problem related to the specific database.
I've also checked for any missing references in the VB editor and everything
is fine. It is possible that we have different service packs installed for
Office/Windows.

I will investigate further tomorrow but would appreciate it if anyone has an
idea of what might be happening.

Thanks,
Daniel

 

[Daniel Klann]

Allen,

Thanks for your reply. I neglected to mention one
important point, the query that was actually being used
had a GROUP BY clause in it. I then searched Deja for 'nz
group by bug' and found this (couldn't find anything on
MSDN).

http://www.mvps.org/access/bugs/bugs0039.htm

I also checked the service packs - I'm on SP6 and the
colleagues in question are on SP5 so there's a possibility
as to why it works on my machine and not theirs.

Cheers,
Daniel

Sydney, NSW, Australia

-----Original Message-----
Service packs would be my first thought.

Locale could have something to do with it:
Tools | Options | New Database Sort Order
or
http://support.microsoft.com/?id=210455

It may be instructive to know which unprintable character is being returned:
Asc(DLookup("LocationID", "MyRequery"))

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

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Hi everyone,

I've searched the knowledge base and Deja for this

problem and cannot find
a

solution. Hope someone can help!

I have a query which uses the NZ function to ensure

that instead of a
null,

the query will return a zero :

SELECT Nz([Table1]![LocationID],0) AS LocID,

Table1.LocationDescription

FROM
Table1;

This is an example, not the actual query, but I have the same problem.
LocationID in Table1 is defined as long integer.

On my machine the query runs as expected. The LocID field returns the
location number and a zero if there are any null values. However, on a
couple of colleagues machines the field is populated entirely with the

character (i.e. the symbol for an unprintable character), even if the
underlying value is not null. All other fields are fine.

Both my colleagues and myself are using Access 2000 on Windows NT4. I've
created a database from scratch and the same problem exists on their
machines so I don't think it's a problem related to the specific database.
I've also checked for any missing references in the VB

editor and
everything

is fine. It is possible that we have different service

packs installed
for

Office/Windows.

I will investigate further tomorrow but would

appreciate it if anyone has
an

idea of what might be happening.

.