Solved

Insert a character into an access field in a report

Posted on 2014-03-11
9
324 Views
Last Modified: 2014-03-17
I need to display a hyphen in an access report that is not in the data itself.

So in the example I included in the screen cap. 51D should end up as 51-D in the report.

This ones got me running in circles any help would be appreciated.

Thanks,
BTW I'm a newbie Access users.
Image-004.jpg
Image-005.jpg
Image-006.jpg
0
Comment
Question by:beatified
[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
  • 5
  • 4
9 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39921925
Looking at the representative data, you have the number is always two number and a letter. So iIn the query that you use to bind the data, you can use the LEFT and RIGHT functions.

ApartNumber: (LEFT([ApartmentNumber], 2) & '-' & RIGHT([ApartmentNumber],1))
0
 
LVL 2

Author Comment

by:beatified
ID: 39921954
Ok that worked but its converting the Uppercase "D" to lower case "d". Is there a way to fix this?
Image-007.jpg
0
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 500 total points
ID: 39921967
You can use UCase to change it to upper case.

ApartNumber: (LEFT([ApartmentNumber], 2) & '-' & UCase(RIGHT([ApartmentNumber],1)))
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 2

Author Comment

by:beatified
ID: 39921973
Thank you so much for giving me the newb version exactly what I needed.
0
 
LVL 2

Author Comment

by:beatified
ID: 39921981
I will start another question if you have an answer for this since it is a different question. But the goal in this is to autofill a form I have in both word and PDF. Is it possible to overlay the data directly onto the PDF in Access. So that I only have to print once and not once for the form and once for the data? If so just answer Yes and I will post a new question.
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39921995
I am sure it is possible, but a little out of what I have ever done. I know that you can export a report. Check out this link and see if it fits what you want to do.

http://office.microsoft.com/en-us/access-help/distribute-a-report-electronically-HA010206320.aspx
0
 
LVL 2

Author Comment

by:beatified
ID: 39922010
Ok thanks I will probably post a question anyway so your welcome to take a stab at it if you want.
0
 
LVL 2

Author Comment

by:beatified
ID: 39922014
One last thing everything is working fine except I'm getting 2 query reqests for data to match. As seen in the sreen caps. Any Idea what this is?
12
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39922382
What does your query look like? Those are asking for parameters like you are expecting user input.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

690 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