mid function

[Guest]

I am trying to parse data from a column to create another column in the same
table. Users can change what column they want to use, so the column name has
to change at run time.
The hardcode is Mid(rst!control, 5, 8). Control is the name of the column.
I created a string variable to represent the column but I get a type
mismatch error.
Revised code is Mid(strString, 5, 8)

The code for the string is strString = "rst!" & txtInputbox

Any help would be greatly appreciated.
dan

 

[Alex Dybenko]

Hi,
rst!control refers to a field in opened recordset. have you opened
recordset?
I think you can just run update query to do this task:

update mytable set newcolumn=Mid(oldcloumn, 5, 8)

 

[Guest]

Try using rst(txtInputBox)

Hope this helps.

 

[OfficeDev18 via AccessMonster.com]

Hi, Dan,

First of all, rename your column - call it a field, which is it's correct
name. 'Control' is an Access reserved word, which means it has special
meaning to Access, and you may not use a reserved word as any object,
function, or variable name. This alone would probably account for all your
trouble.

Besides this, however, where is this field, is it in a table, a query, or
what? Also, where are you putting this code, in a query, or in a form's event?
Are you trying to rename a field at run time (it's impossible - in any object!
) or are you just trying to get data from "choose your field here" and
populate another field?

Help us help you. Copy and paste all your code (or SQL) so we can have a
better idea of your needs.

Hope this is helpful,

Sam

 

[Guest]

Andrew,
I tried using this code
rst!seq = Mid(rst(txtColumn), Val(txtPosition), Val(txtLength))
and I get the following error:

3265 Item cannot be found in the collection corresponding to the requested
name or ordinal.
It looks like to me that the program can't find the correct column in the
table.

Dan

 

[Guest]

I have the code in an event and I renamed the field something different than
"control". Renaming the column did not help. The program is getting confused
on the strColumn variable.

Here is the code:

strTable = txtTable
strColumn = txtColumn

strSQL = " UPDATE " & strTable " & "SET SEQ = Mid(strColumn, 5, 8);"
DoCmd.RunSQL strSQL

thanks for all your help.
Dan

 

[Guest]

Your Mid function is inside quotes and will not be seen as a statement, but
as a string value. Try this version:

strSQL = " UPDATE " & strTable " & "SET SEQ = '" Mid(strColumn, 5, 8) &
"';"

Note the single quotes surrounding the results of the Mid function. They
need to be there for string data. Another thing that will improve
performance for you is using the Execute method rather than RunSQL. It is
much faster because it bypassess the Access UI and goes directly to Jet. You
should use the dbFailOnError or you would not know an error occured.
DoCmd.RunSQL strSQL

CurrentDb.Execute(strSQL), dbFailOnError

 

[OfficeDev18 via AccessMonster.com]

Dan,

Try removing one marking, and your SQL might do what you need.

strSQL = " UPDATE " & strTable " & "SET SEQ = Mid(strColumn, 5, 8);"

make that

strSQL = " UPDATE " & strTable & "SET SEQ = Mid(strColumn, 5, 8);"

Helps?

Sam

 

[Guest]

I had to take out the " after strTable because the compiler expected end of
statement. With that quote removed the compiler points to the Mid as the end
of statement. The code below is what I am trying to compile.
strSQL = " UPDATE " & strTable & " SET SEQ = ' "Mid(strColumn, 5, 8) & "
';"

Again, thanks for your help.
dan

 

[Guest]

That is something I missed; however, the mid function is still inside the
quotes and will not work. This should do it:

strSQL = " UPDATE " & strTable & "SET SEQ = '" & Mid(strColumn, 5, 8) & "';"

 

[Alex Dybenko]

You have to replace strColumn with it actual value also:
SET SEQ = Mid(" & strColumn & ", 5, 8)"