I need to split a text field into 2 fields in access 2007

I have inherited a really bad access database that I need to move the data out of it into a MySQL database. I have a field that has some string data in it followed by an oddly formatted date in parentheses at the end. I am trying to craft a query that has a field that contains only the string up to the open parenthese "(". And a second field that contains only the contents of the parentheses.

Following advice I have found here a开发者_如何学编程nd elsewhere I have tried

note: Left(notefield, InStr(notefield, "("))

but I get the error "Undefined function 'left' in expression." even though I built it using the Builder. So any ideas what I should use in my access query to extract this data? And it has to be in access.

VB(A) is funny this way (I say it that way because this same problem shows up in VB6 as well as the VBA in Office applications)....

When built-in functions such as Left, 'right$, InStr, &c. start throwing "Undefined Function" errors, it almost always means that you've got a problem with References. Some library that you have defined a reference to is missing or broken, and it's not necessarily the one that's reporting errors.

Check Tools | References... and make sure that nothing that's checked says it's "MISSING". If it is, either remove it or fix the link (you can Browse... to the .dll file if you know where it's stored).