On my Property Set Definitions I have a attribute table called "ROOM_NO". This is where I enter the full number/character of the room, in my case the value usually looks like "101A", and sometimes "A101B" for Room number.
I was asked to do another table to separate the fields.
ROOM_PREFIX ROOM_NUM. ROOM_SUFFIX.
Separating the room number in 3 attribute tables to "A 101 B"
I was thinking referencing the ROOM_NO table in each box and filtering out the numbers and character. For example in ROOM_PREFIX I would reference ROOM_NO and filter out the number and the suffix letter through a formula. And in ROOM_NUM I would Filter both alphabetical character.
Does anyone know how to write such formula?
Thanks in advance.
Solved! Go to Solution.
While it is possible to filter out the different portions I think it would be better to create the different portions as property defintions and then use those defintions to create your room name.
For instance, create these these manual defintions.
Then create a fourth formula defintion called Room_No and set it equal to Room_Prefix & Room_Num & Room_Suffix. This would be my first choice on how to approach this problem. If you cannot approach the problem in this manner then you will need to split your Room_No into three seperate property defintions using the MID function. For this to work, the length of the three defintions will need to be constant. Room_Prefix will always be 1 Characters, Room_Num will be 3 characters, and Room_Suffix will be 1 character.
Create a formula defintion called Room_Prefix and put this into it:
MID([Room_No], 1, 1) 'Start at the first character of the string and return 1 character
Create a formula defintion called Room_Num and put this into it:
MID([Room_No], 2,3) 'Start at the second character of the string and return 3 characters
Create a formula defintion called Room_Suffix and put this into it:
Mid([Room_No], 4, 1) 'Start at the fourth character of the string and return 1 charactor.
As you can see in order for this technique to work you will need to know the length of the strings beforehand. This is why I think you should manually enter in the three strings and concatenate them together to create your Room_No. It really is the best way. Since you already know the three values before hand you are just entering in three seperate values instead of the one.
I hope this information helps you out.
Thank you that worked. I am using Version 2. But what do i do if the one of the fields is left at 0, how do i make the interger go BLANK?
That is the downside to using version 2. Each field has to have a value in order for this to work. Your string needs to have a 1 character to represent a number, 3 characters to represent a string and then 1 character to represent a number.
It is possible to write a property set that can parse the individual characters and determine if it is a number or a character and act appropriately. It is not simple to write and would take me a couple hours. I am sorry but i don't have a couple of hours to spare until maybe next week. But here is the idea that I am thinking.
Room_Prefix Property Definition
Parse each character of Room_No one at a time. We should expect to see something like num, text, text, text, num. This would be best case scenario. So we parse the first character. If it is a number, then return this number as Room_Prefix, if it is a character then return "". No number is present
Room_Num Property Definition
Parse each character of the string. If the first character is a number then discard it. If it is a letter then set a temp varialbe equal to it and set your count to 1 and parse the next two characters setting the temp variable equal to itself pllus the next charactor for each parse of the main string. Once the count equals 3, return the temp varialbe as our reselt
Room_Suffix Property Definition
Parse the first character of the string. If the first character is a number then parse the next three characters, if it is a letter then parse the next two. If there is a next character then return that as your Room_Suffix result otherwise return "" as the number is not present.
That is the psuedo code of how I would write the property statement. You can use a case statement to determine if the character is a number and another statement to determine if it is a string. These would have to be put into functions in your property definitions. Its a little involved but it is doable. Which is the reason why i said you should go with version 1!!!
Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register
Start with some of our most frequented solutions to get help installing your software.