Rename fields in Union query?

C

CW

I have successfully merged 3 queries into one using the following:

SELECT Invoice1No, Invoice1Paid FROM qryInvoice1Paid UNION
SELECT Invoice2No, Invoice2Paid FROM qryInvoice2Paid UNION
SELECT Invoice3No, Invoice3Paid FROM qryInvoice3Paid;

However the two resulting columns are named Invoice1No and Invoice1Paid
I would like these to be just InvoiceNo and Paid.
But it seems that with a Union query one doesn't get the normal Design Grid
view, where I would usually rename a field. How can I do this?

Second point - the values in the Paid column are shown as 0 and -1 (the
source values were from checkboxes). How can I change these to display either
as checkboxes again, or as Y or N?

Many thanks
CW
 
K

KARL DEWEY

Try this --
SELECT Invoice1No AS InvoiceNo, Invoice1Paid AS Paid FROM qryInvoice1Paid
UNION SELECT ALL Invoice2No, Invoice2Paid FROM qryInvoice2Paid
UNION SELECT ALL Invoice3No, Invoice3Paid FROM qryInvoice3Paid;
 
K

KARL DEWEY

How can I change these to display either as checkboxes again, or as Y or N?
SELECT Invoice1No AS InvoiceNo, IIF([Invoice1Paid]=0, "N", "Y") AS Paid FROM
qryInvoice1Paid
UNION SELECT ALL Invoice2No, IIF([Invoice2Paid]=0, "N", "Y") AS Paid FROM
qryInvoice2Paid
UNION SELECT ALL Invoice3No, IIF([Invoice3Paid]=0, "N", "Y") AS Paid FROM
qryInvoice3Paid;
 
K

Klatuu

SELECT Invoice1No As InvoiceNo, Invoice1Paid As Paid FROM qryInvoice1Paid UNION
SELECT Invoice2No, Invoice2Paid FROM qryInvoice2Paid UNION
SELECT Invoice3No, Invoice3Paid FROM qryInvoice3Paid;

As to your second point. A check box control is only a visual
representation of the data in a Boolean field. Checked (True) is actually -1
Unchecked (False) is actually 0. In VBA, True and False are just intrinsic
constants with the value -1 and 0 to make it clear what the values mean, just
like using any other constants. So what you are seeing is the actual value
in the field. If you are using the query as the record source of a form or
report, you can format the control the field is bound to. If you are
presenting the query directly to a user, you shouldn't be.

What you can do is use a format function in your query to format the value.
There are 3 options you could use for a boolean field
Yes/No returns Yes or No
True/False returns True or False
On/Off returns On or Off

So, you would need to do this on all 3 lines:

SELECT Invoice1No As InvoiceNo, Format(Invoice1Paid, "True/False") As Paid
 
C

CW

Karl -
That's great, thanks for both your responses - nice and clear
CW

KARL DEWEY said:
SELECT Invoice1No AS InvoiceNo, IIF([Invoice1Paid]=0, "N", "Y") AS Paid FROM
qryInvoice1Paid
UNION SELECT ALL Invoice2No, IIF([Invoice2Paid]=0, "N", "Y") AS Paid FROM
qryInvoice2Paid
UNION SELECT ALL Invoice3No, IIF([Invoice3Paid]=0, "N", "Y") AS Paid FROM
qryInvoice3Paid;


--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this --
SELECT Invoice1No AS InvoiceNo, Invoice1Paid AS Paid FROM qryInvoice1Paid
UNION SELECT ALL Invoice2No, Invoice2Paid FROM qryInvoice2Paid
UNION SELECT ALL Invoice3No, Invoice3Paid FROM qryInvoice3Paid;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union query 5
SUM in a UNION query 2
Union Query 1
Duplicates in union query 3
Union Query and Field Alias 7
Union Query if Yex/No 13
UNION question 3
union query problem 16

Top