• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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?
0
beatified
Asked:
beatified
  • 11
  • 7
1 Solution
 
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
 
beatifiedAuthor Commented:
The field name for the Apartment numbers is [ApartmentNum] Using Access 2010
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
MacroShadowCommented:
Try this.
EE-Demo.accdb
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now