Remove a character from a field for a report Access

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?
LVL 2
beatifiedAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MacroShadowConnect With a Mentor Commented:
Try this.
EE-Demo.accdb
0
 
beatifiedAuthor Commented:
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
 
MacroShadowCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
beatifiedAuthor Commented:
The field name for the Apartment numbers is [ApartmentNum] Using Access 2010
0
 
beatifiedAuthor Commented:
[ApartmentNum] = my examples above.
0
 
MacroShadowCommented:
Left([ApartmentNum], Len([ApartmentNum]) - 1) & " Apartment " & IIf(IsNumeric(Right([ApartmentNum], 1)), Right([ApartmentNum], 1), "")
0
 
beatifiedAuthor Commented:
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
 
MacroShadowCommented:
Sorry, should be,
Left([ApartmentNum], Len([ApartmentNum]) - 1) & IIf(IsNumeric(Right([ApartmentNum], 1)), " Apartment " & Right([ApartmentNum], 1), "")
0
 
beatifiedAuthor Commented:
Ok this is what I got this time.

Also I need another formula to return the Apartment Letter.
Image-002.jpg
0
 
MacroShadowCommented:
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
 
beatifiedAuthor Commented:
When I put that in I get all kind of synax errors
0
 
MacroShadowCommented:
Here is proof of concept.
EE-Demo.accdb
0
 
beatifiedAuthor Commented:
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
 
beatifiedAuthor Commented:
The Street number or exp3 should just return the first 2 characters regardless if that's possible.
0
 
beatifiedAuthor Commented:
This is dead on just need the rest of the address on the StreetNum on the other StreetNum Fields.
Image-004.jpg
0
 
beatifiedAuthor Commented:
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
 
beatifiedAuthor Commented:
If I could give more points or a better grade I would.
0
 
MacroShadowCommented:
Thanks, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.