Solved

Capitalize fields in a Union Query

Posted on 2008-10-16
8
631 Views
Last Modified: 2013-11-27
I have a Union Query in my Access 2003 DB that is combining two tables for a complete list of "Employees" and "Non-Employees".  Both of the separate tables are set in the "Format" with a ">" for Last Name and First Name.  So, when I open the tables directly, the First and Last Name fields are all Caps.  However, when I run the Union Query to achieve a combined list, the names are no longer capitalized.  When setting up the Union Query, it forces you to use SQL instead of Access' pretty GUI setup screen, and I am unfamiliar with SQL coding or how to alter the code below so that these fields stay capitalized.  Can anyone help me with this?  I don't care if every field in the Union Query is capitalized.  So, a blanket statement would work just as well as something that specifies the individual fields for the name.
SELECT [KEY#], [LSTNAME], [FSTNAME], [DOH], [HOMEDEPT], [EMPTYPE], [LOOKUP_TAG]

FROM [tbl_Hire_Date]
 

UNION SELECT  [KEY#], [LSTNAME], [FSTNAME], [DOH], [HOMEDEPT], [EMPTYPE], [LOOKUP_TAG]

FROM [tbl_Non_Employee]

ORDER BY [LSTNAME];

Open in new window

0
Comment
Question by:jmcclosk
  • 4
  • 2
  • 2
8 Comments
 
LVL 23

Accepted Solution

by:
irudyk earned 50 total points
ID: 22730084
Try using UCase()
SELECT [KEY#], UCase([tbl_Hire_Date].[LSTNAME]) AS [LSTNAME], UCase([tbl_Hire_Date].[FSTNAME]) AS [FSTNAME], [DOH], [HOMEDEPT], [EMPTYPE], [LOOKUP_TAG]

FROM [tbl_Hire_Date]

 

UNION SELECT  [KEY#], UCase([tbl_Non_Employee].[LSTNAME]) AS [LSTNAME], UCase([tbl_Non_Employee].[FSTNAME]) AS [FSTNAME], [DOH], [HOMEDEPT], [EMPTYPE], [LOOKUP_TAG]

FROM [tbl_Non_Employee]

ORDER BY [LSTNAME];

Open in new window

0
 
LVL 11

Expert Comment

by:RgGray3
ID: 22730252
Two ways to correct this...

1st the easiest...  run an update query on both tables using the proper() function and store the data properly...
  (while you are at it you could modify your input forms to save new data in the Proper case)

2nd...  and more to the point to get your query out fast...
     Use the Proper on the fields within the query

SELECT [KEY#], Proper([LSTNAME]) as LastName,  ...  in each of the queries

BTW...  easy way to edit a Union Query in the Access QBE environment is to load each portion into designer...  make your changes and copy the SQL back into the Union Query

ALSO...  in a union query ONLY the first query would need the Alias name inserted.   following queries are positionally orgainized (1st field to 1st field, 2nd to 2nd)

Hope this helps...

0
 
LVL 11

Expert Comment

by:RgGray3
ID: 22730272
sorry...  misread the original question...   irudyk is correct with the function... UCase...  
However, replacing that... the rest of my advice is worth reading and understanding
0
 

Author Comment

by:jmcclosk
ID: 22730339
irudyk:
I get the following error when I try your way:

Syntax error (missing operator) in query expression 'UCase([tbl_Hire_Date]. [LSTNAME] AS [LSTNAME],UCase([tbl_Hire_Date]. [FSTNAME]) AS [FSTNAME], [DOH], [HOMEDEPT], [EMPTYPE], [LOOKUP_TAG]
FROM [tbl_Hire_Date]
UNION SELECT  [KEY#],UCase([tbl_Non_Employee]. [LSTNAME]) AS [LSTNAME],UCase([tbl_Non_Employe'
When I click on "Help" I get the following detail:
 <Message> in query expression <expression>. (Error 3075)
The expression you typed is not valid for the reason indicated in the message. Make sure you have typed field names and punctuation correctly, and then try the operation again.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 23

Expert Comment

by:irudyk
ID: 22730356
You are missing a closing bracket ) on
UCase([tbl_Hire_Date]. [LSTNAME] AS [LSTNAME]
should be
UCase([tbl_Hire_Date]. [LSTNAME]) AS [LSTNAME]
0
 

Author Comment

by:jmcclosk
ID: 22730530
Yup, you're right.  I corrected that and it got me past that error.  However, it gave me another:
Invalid use of '.', '!', or '()'. in query expression 'UCase([tbl_Hire_Date].[LSTNAME])'.
When clicking on Help I get:
<Message> in query expression <expression>. (Error 3075)
The expression you typed is not valid for the reason indicated in the message. Make sure you have typed field names and punctuation correctly, and then try the operation again.
0
 

Author Comment

by:jmcclosk
ID: 22730595
I got it... The syntax it accepted was this:
UCase(tbl_Hire_Date.[LSTNAME]) AS [LSTNAME],UCase(tbl_Hire_Date.[FSTNAME]) AS [FSTNAME]
I am going to give irudyk credit for the solution.  I had to play with syntax a little, but he basically gave me the right answer!  Thanks for your help!
0
 

Author Closing Comment

by:jmcclosk
ID: 31506688
Thank you for the help!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now