Find values in Parenthesis

[colmtourque]

I am trying to search a field to find every value that is between ( and )
is there a way to do this?

 

[Kodo]

you'll need to make a reference to the VBscript for regular expressions version 5 in order for this to run

Function GetParens(myvar As String) As String




Set objregexp = New RegExp
        
        objregexp.pattern = "\((\W*\w*)\)"
        
        objregexp.Global = True
        objregexp.ignorecase = True
        
Set Matches = objregexp.Execute(myvar)
        
 For Each Item In Matches
 Debug.Print objregexp.Replace(Item, "$1")
 Next
 





Set Matches = Nothing
Set objregexp = Nothing


End Function

 

[colmtourque]

Thanks, but one other question

How do I use this as part of a query?
Or do I call it from a macro?

Sorry, not the best with modules, I've only learned to use them recently.

 

[Kodo]

how do you want it displayed.

 

[colmtourque]

And I believe this just got a little more complicated

Sorry, I think I need to explain a little more. I thought this would be easy and that I was missing something simple.

I am actually importing and excel spread sheet into a table (was able to get that far).


So far I have a button that runs a module that does this and a query that I am hoping to invoke as well that cleans out empty rows and irrelevent data. The second to last problem is that one column contains Names and id's in this fomrat

Last Name, First Name (Id Number)

OF course each name is a different Length, and then to make matters worse the id numbers are 5-6 characters

I managed to run a query that strips the ids down to (id number) and I was hoping to append the data after that.

The last problem is that I then need to append aa to all the numbers and a 0 in front of any numbers with 5 characters.

I was hoping to do this all with queryis summoned by macros within the module.

But it is looking like this is gonna take a little more.

Any ideas?
Sorry I was not more clear on the front end I thought I had most of this figured out.

 

[pdx_man]

Here is my thoughts for your query:

YourAns: "AA" & PadZeros(Mid([field1],InStr([field1],'(')+1,Len([field1])-InStr([field1],'(')-1),6)

Public Function PadZeros(TheStr As String, MyLen As Byte) As String

    While Len(TheStr) < MyLen
        TheStr = "0" & TheStr
    Wend
    PadZeros = TheStr

End Function

If you might have info after the closing paren, then use:
YourAns: "AA" & PadZeros(Mid([field1],InStr([field1],'(')+1,InStr([field1],')')-InStr([field1],'(')-1),6)

See if that will work for you.

 

[colmtourque]

This is what I currently have

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 8, "Table1", "M:\teststuff\mtd.XLS", True, "Sheet1!b1:q48"
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", "M:\teststuff\mtd.XLS", False, "Sheet1!b5:q48"
DoCmd.OpenQuery "DeleteNullValues", acViewNormal, acEdit
End Sub

PDX_Man I also got your query to work, I'll be adding it when I get the append query to work
Thanks!

Edited: Call query in the sub.

 

[colmtourque]

Thanks

If anyone is looking to soemthing like this. It appears that this works
Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 8, "Table1", "M:\teststuff\mtd.XLS", True, "Sheet1!b1:q48"
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table1", "M:\teststuff\mtd.XLS", False, "Sheet1!b5:q48"
DoCmd.OpenQuery "DeleteNullValues", acViewNormal, acEdit
DoCmd.OpenQuery "append ids", acViewNormal, acEdit
End Sub


With DeleteNullValues being:

DELETE Table1.[_name], Table1.start_date, Table1.[_life], Table1.[_ann], Table1.[_di], Table1.[_LAD], Table1.[_LTC], Table1.[_SPLR], Table1.[_t2 Ann], Table1.[_rs], Table1.[_lcm], Table1.[_mmlisi], Table1.[_mmliai], Table1.[_total], Table1.lad_renewal, Table1.pers_rate
FROM Table1
WHERE (((Table1.[_name]) Is Null)) OR (((Table1.[_name]) Not Like "*(*"));

With append ids being:

UPDATE Table1 LEFT JOIN [get numbers] ON Table1.[_name] = [get numbers].[_name] SET Table1.[id number] = [get numbers].[yourans];


Refering back to get numbers:

SELECT "AA" & PadZeros(Mid([Table1].[_name],InStr([Table1].[_name],'(')+1,Len([Table1].[_name])-InStr([Table1].[_name],'(')-1),6) AS YourAns, Table1.[_name]
FROM Table1;


Refering back to padzeros which was mentioned above but it is:

Public Function PadZeros(TheStr As String, MyLen As Byte) As String

While Len(TheStr) < MyLen
TheStr = "0" & TheStr
Wend
PadZeros = TheStr

End Function



And now I also know that I can call a function in a query, pretty cool.
Thanks.

It also occures to that this was what Kodo was trying to get at as well.
(sorry kodo I misunderstood)