Union query is displaying Chinese characters for one of the fields

[Guest]

I have included the SQL of this union query. For some reason when I run the
query the "Withdrawal" field looks like Chinese or Wingding characters.

However, if I run each half of the union query individually the resulting
records are just fine - no Chinese characters in the "withdrawal" field.

I thought it was a busted formula, yet when I ran the report based on the
union query it displays the Chinese characters, but calculated fields based
on the "withdrawal" field with Chinese character are calculating properly.

It is like something is screwed up with the displaying of that field in a
union query - yet the value of this field is prevailing since calculations
are still functioning correctly.

All help is appreciated.

Seth


SELECT tblProject.ProjectNo, tblProject.Name, tblProject.Date AS
ProjectDate, tblDeposit.Date AS TransDate,
IIf(IsNull([lname]),[business],[lname] & ", " & [fname]) AS FullName,
tblDeposit.Amount AS Deposit, Null AS Withdrawl
FROM (tblClients INNER JOIN tblProject ON tblClients.ClientNo =
tblProject.Clientno) INNER JOIN tblDeposit ON tblProject.ProjectNo =
tblDeposit.ProjectNo
WHERE (((tblProject.Complete)=False))

UNION SELECT tblProject.ProjectNo, tblProject.Name, tblProject.Date AS
ProjectDate, tblPayment.Date AS TransDate,
IIf(IsNull([lname]),[business],[lname] & ", " & [fname]) AS FullName, Null AS
Deposit, tblPayment.Amount AS Withdrawl
FROM ((tblClients INNER JOIN tblProject ON tblClients.ClientNo =
tblProject.Clientno) INNER JOIN tblOrder ON tblProject.ProjectNo =
tblOrder.ProjectNo) INNER JOIN tblPayment ON tblOrder.OrderNo =
tblPayment.OrderNo
WHERE (((tblProject.Complete)=False));

 

[Ken Snell [MVP]]

Reverse the two queries. The Null in the first query for the Withdrawal
field can cause problems for ACCESS in the second query. Put that query as
the second one in the union query.

 

[Guest]

I reversed the two queries and the Chinese went away in the "withdrawl", but
in now in the "payment" field. The payment field and the withdrawl field
each pull from different tables. So every record is going to have to store a
Null somehow - either in the "Payment" or "Withdrawl" field.

Any other thoughts

Thanks,

Seth

Reverse the two queries. The Null in the first query for the Withdrawal
field can cause problems for ACCESS in the second query. Put that query as
the second one in the union query.

--

Ken Snell
<MS ACCESS MVP>

I have included the SQL of this union query. For some reason when I run
the
query the "Withdrawal" field looks like Chinese or Wingding characters.

However, if I run each half of the union query individually the resulting
records are just fine - no Chinese characters in the "withdrawal" field.

I thought it was a busted formula, yet when I ran the report based on the
union query it displays the Chinese characters, but calculated fields
based
on the "withdrawal" field with Chinese character are calculating properly.

It is like something is screwed up with the displaying of that field in a
union query - yet the value of this field is prevailing since calculations
are still functioning correctly.

All help is appreciated.

Seth


SELECT tblProject.ProjectNo, tblProject.Name, tblProject.Date AS
ProjectDate, tblDeposit.Date AS TransDate,
IIf(IsNull([lname]),[business],[lname] & ", " & [fname]) AS FullName,
tblDeposit.Amount AS Deposit, Null AS Withdrawl
FROM (tblClients INNER JOIN tblProject ON tblClients.ClientNo =
tblProject.Clientno) INNER JOIN tblDeposit ON tblProject.ProjectNo =
tblDeposit.ProjectNo
WHERE (((tblProject.Complete)=False))

UNION SELECT tblProject.ProjectNo, tblProject.Name, tblProject.Date AS
ProjectDate, tblPayment.Date AS TransDate,
IIf(IsNull([lname]),[business],[lname] & ", " & [fname]) AS FullName, Null
AS
Deposit, tblPayment.Amount AS Withdrawl
FROM ((tblClients INNER JOIN tblProject ON tblClients.ClientNo =
tblProject.Clientno) INNER JOIN tblOrder ON tblProject.ProjectNo =
tblOrder.ProjectNo) INNER JOIN tblPayment ON tblOrder.OrderNo =
tblPayment.OrderNo
WHERE (((tblProject.Complete)=False));

 

[Guest]

I got that sucker!

Instead of Null I used a zero (since it is a currency field). In my report
I did not want zeroes showing up, I wanted Null (nothing) - so I changed my
control source for each of the fields to an iif() statement. IIf([Field] >
0, [Field]).

It works great!

Seth

Reverse the two queries. The Null in the first query for the Withdrawal
field can cause problems for ACCESS in the second query. Put that query as
the second one in the union query.

--

Ken Snell
<MS ACCESS MVP>

I have included the SQL of this union query. For some reason when I run
the
query the "Withdrawal" field looks like Chinese or Wingding characters.

However, if I run each half of the union query individually the resulting
records are just fine - no Chinese characters in the "withdrawal" field.

I thought it was a busted formula, yet when I ran the report based on the
union query it displays the Chinese characters, but calculated fields
based
on the "withdrawal" field with Chinese character are calculating properly.

It is like something is screwed up with the displaying of that field in a
union query - yet the value of this field is prevailing since calculations
are still functioning correctly.

All help is appreciated.

Seth


SELECT tblProject.ProjectNo, tblProject.Name, tblProject.Date AS
ProjectDate, tblDeposit.Date AS TransDate,
IIf(IsNull([lname]),[business],[lname] & ", " & [fname]) AS FullName,
tblDeposit.Amount AS Deposit, Null AS Withdrawl
FROM (tblClients INNER JOIN tblProject ON tblClients.ClientNo =
tblProject.Clientno) INNER JOIN tblDeposit ON tblProject.ProjectNo =
tblDeposit.ProjectNo
WHERE (((tblProject.Complete)=False))

UNION SELECT tblProject.ProjectNo, tblProject.Name, tblProject.Date AS
ProjectDate, tblPayment.Date AS TransDate,
IIf(IsNull([lname]),[business],[lname] & ", " & [fname]) AS FullName, Null
AS
Deposit, tblPayment.Amount AS Withdrawl
FROM ((tblClients INNER JOIN tblProject ON tblClients.ClientNo =
tblProject.Clientno) INNER JOIN tblOrder ON tblProject.ProjectNo =
tblOrder.ProjectNo) INNER JOIN tblPayment ON tblOrder.OrderNo =
tblPayment.OrderNo
WHERE (((tblProject.Complete)=False));

 

[Ken Snell [MVP]]

Good work! I didn't look closely enough at your initial SQL to note that you
were doing the same Null syntax in the other query too.

--

Ken Snell
<MS ACCESS MVP>

I got that sucker!

Instead of Null I used a zero (since it is a currency field). In my
report
I did not want zeroes showing up, I wanted Null (nothing) - so I changed
my
control source for each of the fields to an iif() statement. IIf([Field]0, [Field]).

It works great!

Seth

Reverse the two queries. The Null in the first query for the Withdrawal
field can cause problems for ACCESS in the second query. Put that query
as
the second one in the union query.

--

Ken Snell
<MS ACCESS MVP>

I have included the SQL of this union query. For some reason when I run
the
query the "Withdrawal" field looks like Chinese or Wingding characters.

However, if I run each half of the union query individually the
resulting
records are just fine - no Chinese characters in the "withdrawal"
field.

I thought it was a busted formula, yet when I ran the report based on
the
union query it displays the Chinese characters, but calculated fields
based
on the "withdrawal" field with Chinese character are calculating
properly.

It is like something is screwed up with the displaying of that field in
a
union query - yet the value of this field is prevailing since
calculations
are still functioning correctly.

All help is appreciated.

Seth


SELECT tblProject.ProjectNo, tblProject.Name, tblProject.Date AS
ProjectDate, tblDeposit.Date AS TransDate,
IIf(IsNull([lname]),[business],[lname] & ", " & [fname]) AS FullName,
tblDeposit.Amount AS Deposit, Null AS Withdrawl
FROM (tblClients INNER JOIN tblProject ON tblClients.ClientNo =
tblProject.Clientno) INNER JOIN tblDeposit ON tblProject.ProjectNo =
tblDeposit.ProjectNo
WHERE (((tblProject.Complete)=False))

UNION SELECT tblProject.ProjectNo, tblProject.Name, tblProject.Date AS
ProjectDate, tblPayment.Date AS TransDate,
IIf(IsNull([lname]),[business],[lname] & ", " & [fname]) AS FullName,
Null
AS
Deposit, tblPayment.Amount AS Withdrawl
FROM ((tblClients INNER JOIN tblProject ON tblClients.ClientNo =
tblProject.Clientno) INNER JOIN tblOrder ON tblProject.ProjectNo =
tblOrder.ProjectNo) INNER JOIN tblPayment ON tblOrder.OrderNo =
tblPayment.OrderNo
WHERE (((tblProject.Complete)=False));