Edit table data automatically, with InStr

  • Thread starter Thread starter shmoussa
  • Start date Start date
S

shmoussa

Okay I have my data being automatically imported to a table
(TableNew). One of my fields is Called "File Name." For the new data
imported, the layout of the information in this field is example:
John.Pictures or Maryam.Videos. After the data is imported, I want the
code to go through the data in this field and remove anything after
the decimal point, including the decimal point. So the "File Name" for
the examples above should be "John" or "Maryam". I know this involves
the InStr however I am not sure how exactly to use it. Could someone
provide the correct code I am looking for? Thank you.
 
shmoussa said:
Okay I have my data being automatically imported to a table
(TableNew). One of my fields is Called "File Name." For the new data
imported, the layout of the information in this field is example:
John.Pictures or Maryam.Videos. After the data is imported, I want the
code to go through the data in this field and remove anything after
the decimal point, including the decimal point. So the "File Name" for
the examples above should be "John" or "Maryam". I know this involves
the InStr however I am not sure how exactly to use it.
Click to expand...

pos = InStr(filename, ".")
If pos > 0 Then
newfilename = Left(filename, pos - 1)
Else
newfilename = ? ? ?
End If
 
Okay I have my data being automatically imported to a table
(TableNew). One of my fields is Called "File Name." For the new data
imported, the layout of the information in this field is example:
John.Pictures or Maryam.Videos. After the data is imported, I want the
code to go through the data in this field and remove anything after
the decimal point, including the decimal point. So the "File Name" for
the examples above should be "John" or "Maryam". I know this involves
the InStr however I am not sure how exactly to use it. Could someone
provide the correct code I am looking for? Thank you.
Click to expand...

Update the field to

Left([File Name], InStr([File Name], ".") - 1)

InStr finds the position of the period; subtract one from that and get the
desired number of characters to save. Frex

InStr("John.Pictures", ".")

will return 5; you want the leftmost four characters so subtract 1.

John W. Vinson [MVP]
 
DoCmd.RunSQL "UPDATE Left([Table New].[File Name], InStr([Table New].
[File Name]) - 1);"

I have the above in my code- and the code runs with no errors but does
not do anything? Any ideas?
 
I write very little SQL, but don't you have to tell it the name of the table
you're updating?
DoCmd.RunSQL "UPDATE Left([Table New].[File Name], InStr([Table New].
[File Name]) - 1);"

I have the above in my code- and the code runs with no errors but does
not do anything? Any ideas?
Click to expand...

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
 
Yup. The syntax for an UPDATE query is:

UPDATE table
SET newvalue
WHERE criteria;

As well, the InStr function is incorrect: it requires at least 2 parameters
(the string being searched, and what's being searched for in the string)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


missinglinq via AccessMonster.com said:
I write very little SQL, but don't you have to tell it the name of the
table
you're updating?
DoCmd.RunSQL "UPDATE Left([Table New].[File Name], InStr([Table New].
[File Name]) - 1);"

I have the above in my code- and the code runs with no errors but does
not do anything? Any ideas?
Click to expand...

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com
Click to expand...
 
Okay I may totally off...but this is what I came up with

DoCmd.RunSQL "UPDATE [TableNew] SET [File Name]=Left([File Server
Name], pos - 1) WHERE (pos = InStr([File Server Name], .)>0 "

I get an error saying "syntax error missing opperator." I also tried
putting quotes around the "." period however it also would not work.

Help? My table name is Tablenew so I did have it. Thank you!!
 
ignore the File server name, i fixed the fact that they didnt match
and still the same error.
 
I have the above in my code- and the code runs with no errors but does
not do anything? Any ideas?
Click to expand...

You need to update a Table, as noted elsethread. In addition you have LOTS of
other things left out. My Left() function was a suggestion for how to proceed,
not a complete update query!!! Try:

DoCmd.RunSQL "UPDATE [Table New] SET [File Name] = Left([Table New].[File
Name], InStr([Table New].[File Name], ".") - 1) WHERE [File Name] LIKE '*.*';"

Be sure the code is all on one line, it's wrapping in the newsreader but
should not be wrapped in your VBA code. I added a WHERE clause to keep it from
trying to update records which don't contain a period.

John W. Vinson [MVP]
 
DoCmd.RunSQL "UPDATE [Table New] SET [File Name] = Left([Table New].
[File
Name], InStr([Table New].[File Name], ".") - 1) WHERE [File Name] LIKE
'*.*';"

"."
^This gives me "expected end of statement error. I replaced it with
'*.*' and that erased all of the fields in the FILENAME column that
did not have a . ?? What else should I try? Thank you
 
DoCmd.RunSQL "UPDATE [Table New] SET [File Name] = Left([Table New].
[File Name], InStr([Table New].[File Name], ".") - 1) WHERE [Table
New] LIKE '*.*';"

PS: Above is exactly what I have and I still get a syntax error.
 
This gives me "expected end of statement error. I replaced it with
'*.*' and that erased all of the fields in the FILENAME column that
did not have a . ?? What else should I try? Thank you
Click to expand...

Sorry! My error: embedding quoted strings within quoted strings can be a REAL
hassle!

Try

DoCmd.RunSQL "UPDATE [Table New] SET [File Name] = Left([Table New].
[File
Name], InStr([Table New].[File Name], '.') - 1) WHERE [File Name] LIKE
'*.*';"


Changing the delimiters around the period in InStr from " to '.

John W. Vinson [MVP]
 
Back
Top