Solved

Remove characters from property numbers in access

Posted on 2007-03-22
4
182 Views
Last Modified: 2012-05-05
I have a table column that contains the property number, however some of the results are 22A if its a set of flats for eample. I wasnt to be able to split this column into 2 and have only numbers in one which will be property number and the letters into another column which will be called propsuffix. How can i do this. I have used the Mid and InStr to get this table in the first place but not sure if i can use this to get rid of letters only.

Thanks
0
Comment
Question by:martgriff
  • 2
4 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
Comment Utility
Use:
   Val(PropertyNo)

and

   Right(PropertyNo, 1)

/gustav
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
MyNumber:Val([PropertyNo])
MyLetter: Right([PropertyNo], Len([PropertyNo]) - Len(Val([PropertyNo])))

0
 

Author Comment

by:martgriff
Comment Utility
Thanks alot for that :D
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

744 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

17 Experts available now in Live!

Get 1:1 Help Now