Problem using the MID Function

[cricket]

I am sure this is something basic that I am missing . . .

I have a MS Access 2003 (SP1) table, DSPLOG2, with one column, columna. columna is derived from a message file on an iSeries machine, and contains a variety of data that I would like to extract into separate columns for reporting purposes.

After doing some research I felt that the "MID" function would be the best way to acheive the desired result, but I have been having problems acheiving success while creating the first query.

I am trying to extract 10 characters starting at character 24 from columna, and put them in a new column, Expr1. (These are not the actual field names.)

This is what I have so far:

 SELECT MID (columna, 24, 10) AS Expr1
FROM DSPLOG2; 

I keep receiving compile errors: Compile error. in query expression "MID (DSPLOG, 24, 10)'.

Any ideas as to what I am missing? I have spent several hours scouring books, the internet, etc for guidance and I can not find the error in my ways.

Thank you!

[Rabbit]

There shouldn't be a space between Mid and ().

[cricket]

Thanks for the quick reply Rabbit. The information you and the others on this board have dispensed has been extremely helpful to me while I have been working with MS Access.

Unfortunately I still receive the same error if I remove the space between MID and "(".

I have played around with the spaces, and have not found a combo that seems to fix the error, as of yet.

[puppydogbuddy]

Thanks for the quick reply Rabbit. The information you and the others on this board have dispensed has been extremely helpful to me while I have been working with MS Access.

Unfortunately I still receive the same error if I remove the space between MID and "(".

I have played around with the spaces, and have not found a combo that seems to fix the error, as of yet.

Try this:

SELECT MID([columna], 24, 10) AS Expr1
FROM DSPLOG2;

Or This if the above doesn't work:

SELECT columna, MID([columna], 24, 10) AS Expr1
FROM DSPLOG2;

[cricket]

I tried the first one previously to no avail. Just tried the second option with the same compile error. Could it be something to do with the setup of the table? Here are the parameters for it:
Type - text
Field size - 150
Required - No
Allow Zero Length - Yes
Indexed - No
Unicode Compression - No
IME Mode - No Control
IME Sentence Mode - None

I only listed the parameters that were populated.

Thanks again . . .

[puppydogbuddy]

I tried the first one previously to no avail. Just tried the second option with the same compile error. Could it be something to do with the setup of the table? Here are the parameters for it:
Type - text
Field size - 150
Required - No
Allow Zero Length - Yes
Indexed - No
Unicode Compression - No
IME Mode - No Control
IME Sentence Mode - None

I only listed the parameters that were populated.

Thanks again . . .

Of the table setup, I don't think any of the settings are problematic as far as the Mid function. If any of the settings were a problem, I would think it could be allow zero length string.

I am beginning to wonder if maybe you are missing a vb library reference to DAO or ? check your vb library references and see if any are shown as missing.

Another test you could do is hit CTRL + G to bring up the immediate window, type the line below and see if it works:
?Mid("ThisIsATest", 8, 4)

I am going to research problems with the mid function later and get back to you, if I find anything.

[cricket]

You are a genius.

When I went to the submit immediate window it also pulled up all of this garbage I was toying with a long time ago. The statement you gave me did not work at first, and it gave me a compile error. I removed the junk module and your statement worked. I swiched back to my query and low and behold it works.

Thanks a million!!! I was a couple of minutes away from rocking back and forth in the corner mumbling to myself. This has been hours of making minute changes to the code, and that was before I posted. I would have never looked there. Thanks for everyone's input and help!!!!!

[puppydogbuddy]

You are a genius.

When I went to the submit immediate window it also pulled up all of this garbage I was toying with a long time ago. The statement you gave me did not work at first, and it gave me a compile error. I removed the junk module and your statement worked. I swiched back to my query and low and behold it works.

Thanks a million!!! I was a couple of minutes away from rocking back and forth in the corner mumbling to myself. This has been hours of making minute changes to the code, and that was before I posted. I would have never looked there. Thanks for everyone's input and help!!!!!

You are most welcome! Glad you got your problem resolved, and that we could help you..