Solved

Capitalize fields in a Union Query

Posted on 2008-10-16
8
636 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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
 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

734 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