Solved

Capitalize fields in a Union Query

Posted on 2008-10-16
8
630 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 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

15 Experts available now in Live!

Get 1:1 Help Now