Syntax error (comma) in query expression

[tomaccess]

Thanks for all (especially PH) who have helped me thus far on splitting 1 field with 12 numbers separated by semicolons into 12 separate numeric fields. I'll provide an update so others might be able to help me figure this out!

Data field is called MEB-lyr, data looks like:
5782.7;5782.7;11408.92;7586.75;9598.69;10659.02;2939.58;2939.58;7474.24;11353.24;13616.41;9420.71

Per PH's instructions I put the following function in a class module called splitfield:

Option Compare Database
Public Function mySplit(str, delim, n)
On Error Resume Next
mySplit = Split(str, delim)(n - 1)
End Function

Then I opened a new query and in the Query gridbox I have:
Field: MEB1-lyr: mySplit([MEB-lyr],';',1)
Table: PUB_gla
Sort:
Show: Checked

When I run the query I get the following message:
Syntax error (comma) in query expression 'PUB_gla.[MEB1-lyr: mySplit([MEB-lyr],';',1)].

I looked at a previous post by PH noting that the ControlSource might be a possible solution but I'm not really exactly sure what to do at this point.

Can anyone provide me with any troubleshooting ideas? I feel like I'm so close!

Thanks!

 

[PHV]

Sorry, my bad:
Table:

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tomaccess]

When I eliminate the reference to the table the new message that comes up when I try to run the query is:

undefined function 'mysplit' in expression

I've searched for this error message on the forum with limited success.

 

[PHV]

The mysplit function must resides in a standard code module (not a form nor report module).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tomaccess]

PH,

Thanks - making it a standard code module helped but what ultimately got the query to run was taking "Option Compare Database" out of the standard module statement. Thanks for all your help - that's a great approach to busting up comma delimited files.

TW