?
Solved

Capitalize fields in a Union Query

Posted on 2008-10-16
8
Medium Priority
?
641 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 150 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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