mid function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
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
 
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
 
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
 
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
 
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
 
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) & "';"
 
You have to replace strColumn with it actual value also:
SET SEQ = Mid(" & strColumn & ", 5, 8)"
 
Back
Top