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: