MS-Access: text expression appends null character

185 views Asked by At

I have a simple query in ms-access that combines two text fields into one:

SELECT AllNames.Name, AllNames.Authority, [Name] & " " & [Authority] AS Expr1
FROM AllNames;

The query-result is not entirely what I expect, for the expression column appends an extra character \0 to the result string:

Name                        Authority           Expr1
Acrophorus dissectus        Ching & S. H. Wu    Acrophorus dissectus Ching & S. H. Wu\0
Acrophorus emeiensis        Ching               Acrophorus emeiensis Ching\0
Acrophorus exstipellatus    Ching & S. H. Wu    Acrophorus exstipellatus Ching & S. H. Wu\0

Funny thing is that AllNames is a linked table. If I run the same query in the database that owns AllTables, the null-character is not appended.

Adding Trim() around the expression or replacig the ampersands with + doesn't help.

All text fields in AllNames have been scanned for null-characters and cleaned if necessary. I could do the same to the query result whenever I use it, but I'd rather not for this is where I need the speed.

Would there be any fix to this problem, or is the only solution to add a redundant field to AllNames (and risk compronising data integrity)?

MSAccess 2003 Windows Server 2003/64

edit What happens to the field lengths? If I change to query to

select [Name], Len([Name]) As N_Len, 
[Authority], Len([Authority]) As A_Len, 
[Name] & " " & [Authority] As Expr1, Len([Name] & " " & [Authority]) As E1_Len 
From AllNames

then the result is

Name                     N_Len  Authority        A_Len  Expr1                                   E1_Len
(no name)                    9                          (no name)                                   10
Pteridophyta                12  Schimper             8  Pteridophyta Schimper                       21
Acrophorus diacalpioides    24  Ching & S. H. Wu    17  Acrophorus diacalpioides Ching & S. H. Wu   42
Acrophorus dissectus        20  Ching & S. H. Wu    17  Acrophorus dissectus Ching & S. H. Wu       38
Acrophorus emeiensis        20  Ching                6  Acrophorus emeiensis Ching                  27

So is the null character really there? Not as we believe the field lengths (len(name) + len(author) + 1 for the space in between). However, it is there whe I with pyodbc use the query in Python (not so with other queries not involving the linked table, or using the same table from its own database). Also it shows up in Access's GUI when the cursor us right behind the text:

screendum from access

0

There are 0 answers