Rename fields in Union query?

  • Thread starter Thread starter CW
  • Start date Start date
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
 
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;
 
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;
 
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
 
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;
Click to expand...
Click to expand...
 
Back
Top