Isolating numbers in brackets via query (or macro)

[pandorah]

Hi there, I have a table with a field called 'Description'. It is a free form field so there are a variety of formats, character length, etc. I'm trying to build a query that will isolate any row that has a number within a bracket at the end of the character line; then add that number to another column (so the number is isolated on it's own).

Example:
'Description' Column
1. This is one row of data (VE)
2. This is a second row of data (136)
3. This is a third row of data with text but no brackets
4. This is a fourth row of (data) with brackets in the middle

I only want to isolate the second row; putting the 136 in a separate column. So final output:
'Description' Column = remains as-is
'Number of Lines' Column =


1. 0
2. 136
3. 0
4. 0

Hopefully this makes sense. I'm really not sure how to go about this so any ideas are helpful. Thank you

[Gicu]

Here is one way:

Expr1: IIf(Right([YourField],1)=")",Replace(Mid([YourField],InStrRev([YourField],"(")+1),")",""),0)

Cheers,

[pandorah]

Thank you. I'll give it a try and let you know how it goes.

[Micron]

How are you at writing code? That's the only approach I see.
I'd find the first ( using Instr function, then find ) and then use Mid with the starting point being the first value and the number of characters to take being the difference between the two. Then I'd test the result to see whether or not it can be interpreted as a number, in which case it will not be if it looks like "123?"
Then you could write the value to a table.

Alternatively, one could loop over the first value until the second and test if it's a number but I don't think that's the approach I would use.

EDIT - seems I misunderstood the requirement as I thought that (123) in the middle would be needed as well.

[pandorah]

Thank you. This is working but it's not removing anything that isn't a number. For example if the string ends with (V)...it is returning V in the other column. I only want to see numbers...if it's not a number it should show as either blank or 0 in the other column. Do you know how I can add to your code below to do that extra step?

Here is one way:

Expr1: IIf(Right([YourField],1)=")",Replace(Mid([YourField],InStrRev([YourField],"(")+1),")",""),0)

Cheers,

[pandorah]

Hi there, yes I only need the number that is at the end. If there is a number in the middle of the string of characters then it can be ignored. See my other comment above in reply to Gicu. And I would call myself intermediate at code. I've got the basics down but need help when it gets to things that are more complex.

How are you at writing code? That's the only approach I see.
I'd find the first ( using Instr function, then find ) and then use Mid with the starting point being the first value and the number of characters to take being the difference between the two. Then I'd test the result to see whether or not it can be interpreted as a number, in which case it will not be if it looks like "123?"
Then you could write the value to a table.

Alternatively, one could loop over the first value until the second and test if it's a number but I don't think that's the approach I would use.

EDIT - seems I misunderstood the requirement as I thought that (123) in the middle would be needed as well.

[Gicu]

Just wrap it in IsNumeric in another calculated field:

MyNumber:Iif(IsNumeric([Expr1]),[Expr1],0)

Cheers,

[Micron]

Just wrap it in IsNumeric in another calculated field:
MyNumber:Iif(IsNumeric([Expr1]),[Expr1],0)Cheers,

Nice solution. Will work as long as it's not a problem showing the Exp1 field in the query but that should not be an issue.

[Gicu]

One could use the original expression instead of Expr1 but would make for a long, ugly and hard to follow calculation...

Cheers,

[pandorah]

This solved it for me. THANK YOU so much for the quick help!!!!

One could use the original expression instead of Expr1 but would make for a long, ugly and hard to follow calculation...

Cheers,

[Gicu]

You're vey welcome, good luck with your project!

Cheers,

[accesstos]

Here is one way:

Expr1: IIf(Right([YourField],1)=")",Replace(Mid([YourField],InStrRev([YourField],"(")+1),")",""),0)

Cheers,

Here is an other way:

Expr1: IIf([YourField] Like "*)",Val(Mid([YourField],InStrRev([YourField],"(")+1));0)

Cheers,
john