Solved

Remove a character from a field for a report Access

Posted on 2014-03-17
18
172 Views
Last Modified: 2014-03-18
I have a field that has data like these examples

51A
25D
31B

This is refering to an apartment number and for the purpuses of pulling up a record I need to be able to type in 55A and have it return the Tenants for example that live in that apartment.

Because it is an address I need to seperate 55 and the A for the purposes of creating a report that says 1055 Harmony Lane. Apartment A.

Is there a way to do this in a query?
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
  • 11
  • 7
18 Comments
 
LVL 2

Author Comment

by:beatified
ID: 39935178
BTW there are a few situations that in which there is no apartment letter.

In this case seperating the number and letter is not needed but hopefully the code would be able to take this in to account.

For example there are also just numbers like

57
59
27
29

In these cases there is no apartment Letter just a number.

Obviously I would figure this would be done in a query.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39935187
It can be done quite easily. If you provide the sql you are using or the field names, we can provide you with a working solution.
0
 
LVL 2

Author Comment

by:beatified
ID: 39935192
The field name for the Apartment numbers is [ApartmentNum] Using Access 2010
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: 39935193
[ApartmentNum] = my examples above.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39935257
Left([ApartmentNum], Len([ApartmentNum]) - 1) & " Apartment " & IIf(IsNumeric(Right([ApartmentNum], 1)), Right([ApartmentNum], 1), "")
0
 
LVL 2

Author Comment

by:beatified
ID: 39935317
Ok thats close but this is what it ended up getting.

Really it should be two fields in the end one with just the apartment number and one with just the apartment letter.

By the way [ApartementNum] is in [tblApartment]

My guess is the Number only field should be this...

NumOnly: Left([tblApartment].[ApartmentNum],Len([tblApartment].[ApartmentNum])-1)

But this needs some sort of IIf that says if it only has 2 characters then do nothing essentially.

But I still need one for Letter only.
Image-001.jpg
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39935370
Sorry, should be,
Left([ApartmentNum], Len([ApartmentNum]) - 1) & IIf(IsNumeric(Right([ApartmentNum], 1)), " Apartment " & Right([ApartmentNum], 1), "")
0
 
LVL 2

Author Comment

by:beatified
ID: 39935397
Ok this is what I got this time.

Also I need another formula to return the Apartment Letter.
Image-002.jpg
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39935533
Try this:
SELECT tblApartment.Street, tblApartment.ApartmentNum, IIf(Not IsNumeric(Right([ApartmentNum],1)),Left([ApartmentNum],Len([ApartmentNum])-1) & " Apartment " & Right([ApartmentNum],1),"") AS Expr1, IIf(Not IsNumeric(Right([ApartmentNum],1))," Apartment " & Right([ApartmentNum],1),"") AS Expr2, IIf(IsNumeric(Right([ApartmentNum],1)),[ApartmentNum],"") AS Expr3
FROM tblApartment;

Open in new window

0
 
LVL 2

Author Comment

by:beatified
ID: 39935557
When I put that in I get all kind of synax errors
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39935562
Here is proof of concept.
EE-Demo.accdb
0
 
LVL 2

Author Comment

by:beatified
ID: 39935596
Ok now I see what you are doing the only problem I'm having is that if there is a letter at the end it wont return the Street number. as in exp3 for 55A
0
 
LVL 2

Author Comment

by:beatified
ID: 39935624
The Street number or exp3 should just return the first 2 characters regardless if that's possible.
0
 
LVL 2

Author Comment

by:beatified
ID: 39935629
This is dead on just need the rest of the address on the StreetNum on the other StreetNum Fields.
Image-004.jpg
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39936064
Try this.
EE-Demo.accdb
0
 
LVL 2

Author Comment

by:beatified
ID: 39937309
Thanks so much man. I'm a total newb at this access stuff but by giving me the answer you have taught me so much. That may not make a ton of sense to a lot of people but for me when I see the code and can understand what it does it allows me to understand how to apply it later.

Thanks again.
0
 
LVL 2

Author Closing Comment

by:beatified
ID: 39937310
If I could give more points or a better grade I would.
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39937461
Thanks, glad to help.
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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

695 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