Edit 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.

0
shmoussa
5/25/2007 6:05:57 PM
access.formscoding 7493 articles. 0 followers. Follow

12 Replies
802 Views

Similar Articles

[PageSpeed] 25

shmoussa wrote:

>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.

pos =  InStr(filename, ".")
If pos > 0 Then
	newfilename = Left(filename, pos  - 1)
Else
	newfilename = ? ? ?
End If

-- 
Marsh
MVP [MS Access]
0
Marshall
5/25/2007 7:09:13 PM
On 25 May 2007 11:05:57 -0700, shmoussa <shmoussa@gmail.com> wrote:

>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.

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]
0
John
5/25/2007 7:32:27 PM
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?

0
shmoussa
5/29/2007 3:50:27 PM
I write very little SQL, but don't you have to tell it the name of the table
you're updating?

shmoussa wrote:
>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?

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

Answers/posts based on Access 2000

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1

0
missinglinq
5/29/2007 4:16:19 PM
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
http://I.Am/DougSteele
(no e-mails, please!)


"missinglinq via AccessMonster.com" <u28780@uwe> wrote in message 
news:72e86e25398d7@uwe...
>I write very little SQL, but don't you have to tell it the name of the 
>table
> you're updating?
>
> shmoussa wrote:
>>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?
>
> -- 
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200705/1
> 


0
Douglas
5/29/2007 4:29:12 PM
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!!

0
shmoussa
5/29/2007 4:49:10 PM
ignore the File server name, i fixed the fact that they didnt match
and still the same error.

0
shmoussa
5/29/2007 5:00:01 PM
On 29 May 2007 08:50:27 -0700, shmoussa <shmoussa@gmail.com> wrote:

>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]
0
John
5/29/2007 5:04:35 PM
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

0
shmoussa
5/29/2007 6:35:15 PM
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.

0
shmoussa
5/29/2007 6:39:27 PM
On 29 May 2007 11:35:15 -0700, shmoussa <shmoussa@gmail.com> wrote:

>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]
0
John
5/29/2007 8:30:07 PM
THANKS SO MUCH. Works perfectly.

0
shmoussa
5/29/2007 9:12:52 PM
Reply:

Similar Artilces:

Adding control boxes automatically
Hi Folks, I'm completly jammed here, I've been trying to find a way to add control box to a form by clicking a bottom. Unfortunately, I've keep failed. Could someone help me on this matter. Your help will be very appreciated That's an extremely unusual thing to have to do in a production application. Perhaps you can explain your situation, and someone will be able to suggest an alternative. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Fred's" <frederic10_mess@hotmail.com> wrote in message news:1193673833.6376...

Update Chart Source Data
I used the "Record Macro" function to get the code shown below. When I recorded the macro, the value of A1 was 392. But when I actually ran the macro, the value of A1 was 393. As you can see, the "Record Macro" function "hard coded" the value of 392. How can I make this work such that the SeriesCollection is updated with the CURRENT value of A1? Sheets("Chart2").Select Range("A1").Select Selection.Copy Sheets("Chart1").Select ActiveChart.PlotArea.Select Application.CutCopyMode = False Act...

data visualization
Hi, I have a 60k row worksheet that has three columns company customer num_orders Microsoft Maryland 150 Symantec Maryland 110 I want the chart to show that Microsoft and Symantec have a common customer. For example a circle representing Maryland would connect to two other circles representing Microsoft and Symantec. The 60k row worksheet contains hundreds different companies and thousands of different customers. If this cannot be done with Excel, does anyone have a low cost software solution? Thanks Ummm...with 60,000 records it is hard to imagine any visualizati...

look up tables and IF statements
=VLOOKUP(G2,$C$30:$F$369,2) I have managed to create a look up table using the office assistant, and the formula above dumps information into cell G3. If G2 is empty or has a zero I don't want G3 to have anything in it. At the moment it just keeps coming up with #N/A Can anyone tell me what formula I should be using please Hi Rick! Would this modification do? =IF(OR(G2<>0,G2<>""),VLOOKUP(G2,$C$30:$F$369,2),"") Hope this helps! -- Thanks and kind regards "Rick" wrote: > =VLOOKUP(G2,$C$30:$F$369,2) > I have managed to create a lo...

Open the Linked Table Manager from Button (?)
Is there a way to open the Linked Table Manager from a button click? -- Thanks, croy croy wrote: >Is there a way to open the Linked Table Manager from a >button click? DoCmd.RunCommand acCmdLinkedTableManager Even if that does what you ask, you should never expose thw link table manager to users. If they are not experts in linking tables, they can make a serious mess of things. Way better for you to use code to do the (re)linking based on a user selected file path. To select file/path see: http://www.mvps.org/access/api/api0001.htm Then to relink the ta...

Date entry automatically
I am creating a spreadsheet containing stats for a weeks work, broken down by day. I will use the same spreadsheet each week, just replacing the dates. If I enter Monday's date, is there a way to make the rest of the week's dates automatically appear? And would this work if the Monday was the end of the month, like Mon 30 May? Thank you, Rob, Teesside, UK You need only add 1 to Monday's date to get Tuesday, 2 for Wednesday, etc. Thus, if you will place your Monday starting date in A2 - say 4/11/05, then B2 would contain =A2+1 and C2 would contain =B2+1 ...

sample data for CRM 4.0?
I read in the CRM 4.0 implementation guide (yeah, I am reading it!) that CRM 4.0 sample data was supposedly available for download... has anyone located the link for this? The resource center isn't coming up with anything. Thanks! Nick -- Nick Doelman www.readybms.com The sample data will be released soon (for English) and other languages will follow in due course. Philip Richardson [MSFT] "Nick Doelman" wrote: > I read in the CRM 4.0 implementation guide (yeah, I am reading it!) that CRM > 4.0 sample data was supposedly available for download... has anyone loca...

help with data collection!
Hello all! Here is a c/p of partial of my post from the other forum. Any and all help is greatly appriceated guys! Thanks! So I have this list that has product numbers in column A, product prices in column B, and order number for the products in column C ... most products have more than once instance.. for example col a col b col c producta 10.99 345353 producta 11.99 3445453 producta 10.99 657567 producta 8.99 345353 producta 10.99 5464646 so what I'm trying to do now is , when i type in the product number(products are alphanumeric w/ no spaces - the a...

How do I change the size of the text box of data labels?
Excel does not give you the ability to resize these boxes. If you need more control, you have to use regular text boxes. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "rmashton" <rmashton@discussions.microsoft.com> wrote in message news:2779C336-3FB7-407B-A7AC-0773BE51A735@microsoft.com... > ...

Problem with creating two variable data table
Presently I am unable to set up a formula in a cell for two inpu variable. I have gone through EXCEL help on how to create two variabl data table but unable to do so. There may something wrong in formula. I have done the following : Inserted a formula in cell D20 which read as : D21^2+E20^2-4. Keyed in a set of inputs in same column below the cell in which formul is written i.e. D21:D26 and keyed in another set of input in same ro in the cell that is at the right of formula cell i.e. E20:J20. Always one message comes "Input cell reference is not valid". Ther must be something wro...

Advanced Find records without certain data
I can't figure this out so I was hoping somebody could help me. We have a bunch of contacts on a marketing list. We created an activity on a subset of those contacts. I need to now find all the remaining contacts on that list that do not have the activity created. I can't find how to do this search. I can find all active contacts on the list, I just don't know how to find contacts that do not have the activity created with the certain name. It's a "does not contain an activity named this" type of search. Thanks for any help You may do it in reserve order...

What tables forthis database
I have a excel spreadsheet at work for shipments comming in to the country for us, and then delivered by a transport company to a said power station. Among some of the 17 column headers, there are: Customer Project Ship from Ship to Ship date Customs in date Customs clearance date Delivery date Commercial Value The other column headers are dates of receipt of POD, who signed etc, and hyperlink cells to documents for the said shipment. I want to put all thisinformation into a Access 2007 database and have had sometought on how to do it, with separtate tables for CustomerID...

Problem with Pivot Table
Hiyee all~~ I'm a newbie of Excel with VBA. I got a question that need help fro you all. When I create the pivot table, I record the macro. After complet building the pivot table, I stop my macro. Hence, the next time I wan to built my pivot table, I will just click on macro. When I recorded it with macro, I can built it with the same amount o data. The problem here is, when I expanded my data, I cant get th correct pivot table. For example, my data initially from R1C1:R10C4 and I modify it to R1C1:R20C4, I cant get the correct pivot table buil from the macro. What can I do to it? Anyone...

Grouping in a Pivot Table
I have created a Pivot Table off of a spreadsheet containing sales related information. I am trying to group the "Due Date" field into months and I keep receiving an error that I cannot group the selection. I have used this feature before with success and I am stumped as to why it is not working this time. Help! Most often it has to do with having blank cells or text mixed in with your dates. Check out this link. Once you have changed all of your items into dates you may have to refresh your pivot table twice before you will be able to group by dates. http://www...

Pivot Table Wizard "Layout" button disabled (greyed out)
I am trying to use the Pivot Table wizard in Excel 2007 (using "alt-d, p" to launch it), however in step 3 of 3, the Layout button is ALWAYS disabled (greyed out) regardless of how I set the preceding options. Any suggestions on how to enable this? Click Finish and and an Excel 2007 layout window will appear. Also reupload my response to your last post for automation options. Hi, Bad news! - this feature is disabled for regular pivot tables in 2007. If you are creating pivot tables from certain kinds of external data sets you will have the ability to use Layout, but not with r...

How do I label data points in a scatter graph
Hello, I am trying to label a data point in a scatter plot but it keeps returning the x value. I'd like to have the store # instead. Basically I have Store #, Acceptance % and $ Value Eligible. Right now I have Acceptance % as my x-value and $ Value Eligible as my y- value. I'd like the plot point to return the store # assoicated with the x and y value. On Oct 26, 8:43 am, Justin <JustinFeh...@iowatelecom.net> wrote: > Hello, I am trying to label a data point in a scatter plot but it > keeps returning the x value. I'd like to have the store # instead. > Basical...

OL2007
Running OL2007 SP2 under Vista Home Premium SP2. It appears that when I'm replying to a plain text message, editing functions (bold, underline, etc) are disabled. Is there a way to enable them? You'd need to switch to HTML format - plain text is, well, plain text. That means no formatting. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchan...

Obtain Top Value from Table
I am attempting to pull a random set of records using a Random Number and the Top Value function. The Top Value changes depending on the specific group I am looking at so I would like to obtain the Top Value from a table rather than entering manually. First, the appropriate statistically significant sample size is determined for each group in my table by the query below. The sample size and group information are added to [tbl_tt_sample_size]. INSERT INTO tbl_tt_sample_size ( MonthSample, Code, Fac, Sample ) SELECT DateSerial(Year(Date()),Month(Date()),0) AS MonthSer, tbl_t...

Pivot table
Can I develop a formula that I can add to those which you pick from whe using the wizard ie sum, average, min, max etc Specifically, I want to add an IF statement to give me a 'flag' i which to summarize the data with elsewhere. The data behind the pivo changes (sales data) and I am trying to flag new customers that hav never worked with us before.....once they have traded with us then the dissappear as they are now an old customer To -- Message posted from http://www.ExcelForum.com Hi no you can't do this -- Regards Frank Kabel Frankfurt, Germany > Can I develop a for...

Sot data and make it apperar in other sheet?
Hi. i have all my data in a "List table" in sheet1. How can i sort that data and make it appear in sheet2 automatically? is there a simply and obvious way that im missing? Thanks a lot SpeeD Sorting on a sheet won't magically make data appear on another sheet. Is the second sheet linked to the first? Sorting sheet1 will sort linked data on sheet2. Elsewise you will need VBA code to copy sorted data to sheet2 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:25:02 -0700, SpeeD <SpeeD@discussions.microsoft.com> wrote: >Hi. > >i ha...

data migration wizard can't import closed cases?
Hello, I am trying to use the data migration wizard to bring in some cases, but it will not allow import of case records with a status/status reason for closed cases. I get the following message for each record: "This message can not be used to set the state of incident to Closed. In order to set state of incident to Closed, use the CloseIncidentRequest message instead." I have tried looking at a way to import the cases as open and then import case resolutions, but I can't figure out how to make that work either. Has anyone solved how to do this? Thanks! Jon The C...

Import data from FileMaker
I have a School Suspension file that is in FilemakerPro. It works very well. I want to automate a monthly report for our business office and want to be able to import data from the db into Excel. On Mac's I can do so quite nicely, simply Open Excel, then Open the FMPro db which launches a wizard and all is done in a few key strokes. Obviously I'm not as adept in XP. Using Excel 2003 and FileMaker Pro 6.ov4 how can I accomplish this task? I don't speak FileMakerPro, so you can try this or wait for a real response! Open excel. File|Open Look at all the fileformats that excel ...

missing data points causes my line graph not to connect
Hi there, I am doing a simple line graph for data points over 200 days, however for some of the days I do not have data, therefore when I create a graph it where there is missing data the line does not connect, there should be an easy way to fix it, but I can't figure it out. I tried to just select the data I want in the series but it only holds so many and I can't get all the data points in. Please help Tools menu > Options > Chart tab. Choose the interpolation option for how Excel should treat a blank cell. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Techn...

Table Relationships: An Understanding is necessary...
Hi there, I thought I understood table relationships in Access (2007 btw) but clearly I'm missing something... so hope someone can assist me: I have two tables that need to be linked: tblPatients, and tblWoundDetails. tblWoundDetails can contain many records of a patient. The PatientID in tblWoundDetails is selected off a drop-down lookup linking to tblPatients. I have set up a table relationship as well, linking these two tables on PatientID. It shows a one-to-many relationship type, and I am setting it up to say that I want all the records from tblPatients and only thos...

Pivot tables
I have built a report that links multiple pivot tables to various acces queries, it is working exactely as I need it to. Only problem now is need to change the drive and directories the access database and th pivot table report actually sit on. What is the best method for moving the report and the queries -- Message posted from http://www.ExcelForum.com ...