Solved

Capitalize fields in a Union Query

Posted on 2008-10-16
8
632 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
DevOps Toolchain Recommendations

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

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql server function help 15 31
Need help how to find where my error is in UFD 6 30
How to get the closest date in a query in Access 2010 8 24
TSQL query to generate xml 4 35
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

777 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