Union query garbles ID field

M

Matthew

I can't figure out why this happens. In some of my union queries, an ID
field gets garbled and displayed as a non-western character, or a box shape
of some sort. It's like it's encoded? Does anyone know why this might
happen? I can post back with table & query descriptions.

Thank you!!

Matthew
 
M

Matthew

Here's my structure, simplified:

tblContacts
*ContactID
LastName
....

tblAffiliations
*AffiliationID
ContactID
Title
....

qryA:
SELECT tblAffiliations.AffiliationID, tblAffiliations.Title, Null AS
ContactID, Null AS LastName
FROM tblAffiliations;

qryC:
SELECT tblContacts.ContactID, tblContacts.LastName, Null AS AffiliationID,
Null AS Title
FROM tblContacts;

UnionQuery:
SELECT qryA.AffiliationID, qryA.Title, qryA.ContactID, qryA.LastName FROM
qryA

UNION SELECT qryC.AffiliationID, qryC.Title, qryC.ContactID, qryC.LastName
FROM qryC;

In the query results, all records that come from tblContacts have an
illegible, non-western character in the ContactID field.

Many thanks!!

Matthew
 
G

giorgio rancati

Hi Matthew,

It's a problem of datatype allocation.
modify the queries like these
----
qryA:
SELECT
tblAffiliations.AffiliationID,
tblAffiliations.Title,
Cvar(Null)+0 AS ContactID,
Null AS LastName
FROM tblAffiliations;

qryC:
SELECT
tblContacts.ContactID,
tblContacts.LastName,
Cvar(Null)+0 AS AffiliationID,
Null AS Title
FROM tblContacts;
 
M

Matthew

Fantastic!

I don't (yet) know what Cvar means, but it works!!

Thank you!!

Matthew


giorgio rancati said:
Hi Matthew,

It's a problem of datatype allocation.
modify the queries like these
----
qryA:
SELECT
tblAffiliations.AffiliationID,
tblAffiliations.Title,
Cvar(Null)+0 AS ContactID,
Null AS LastName
FROM tblAffiliations;

qryC:
SELECT
tblContacts.ContactID,
tblContacts.LastName,
Cvar(Null)+0 AS AffiliationID,
Null AS Title
FROM tblContacts;
----

bye
--
Giorgio Rancati
[Office Access MVP]


Matthew said:
Here's my structure, simplified:

tblContacts
*ContactID
LastName
...

tblAffiliations
*AffiliationID
ContactID
Title
...

qryA:
SELECT tblAffiliations.AffiliationID, tblAffiliations.Title, Null AS
ContactID, Null AS LastName
FROM tblAffiliations;

qryC:
SELECT tblContacts.ContactID, tblContacts.LastName, Null AS
AffiliationID, Null AS Title
FROM tblContacts;

UnionQuery:
SELECT qryA.AffiliationID, qryA.Title, qryA.ContactID, qryA.LastName FROM
qryA

UNION SELECT qryC.AffiliationID, qryC.Title, qryC.ContactID,
qryC.LastName FROM qryC;

In the query results, all records that come from tblContacts have an
illegible, non-western character in the ContactID field.

Many thanks!!

Matthew
Click to expand...
Click to expand...
 
Top