 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / May 2007Edit table data automatically, with InStr
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. >Okay I have my data being automatically imported to a table >(TableNew). One of my fields is Called "File Name." For the new data [quoted text clipped - 4 lines] >the examples above should be "John" or "Maryam". I know this involves >the InStr however I am not sure how exactly to use it. pos = InStr(filename, ".") If pos > 0 Then newfilename = Left(filename, pos - 1) Else newfilename = ? ? ? End If  SignatureMarsh MVP [MS Access] >Okay I have my data being automatically imported to a table >(TableNew). One of my fields is Called "File Name." For the new data [quoted text clipped - 5 lines] >the InStr however I am not sure how exactly to use it. Could someone >provide the correct code I am looking for? Thank you. 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? SignatureThere's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000 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) SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I write very little SQL, but don't you have to tell it the name of the >table [quoted text clipped - 5 lines] >>I have the above in my code- and the code runs with no errors but does >>not do anything? Any ideas? 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? 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 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] THANKS SO MUCH. Works perfectly. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.