Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop drive driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory missing monitor motherboard network printer problem ram random registry router slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: Access - Removing Data in Field

Reply  
Thread Tools
Dreambringer's Avatar
Computer Specs
Senior Member with 1,366 posts.
 
Join Date: Jan 2005
Location: Austin, Texas
Experience: If its broken, Reformat..
08-Feb-2010, 06:38 PM #1
Thumbs up Solved: Access - Removing Data in Field
I did a quick search and was not able to find anything that I was looking for...

Here is what I have..

I am using Access 2003 and I have a table that tracks file names. I am looking to do a Find & Replace on anything before the "/".

For example: Find and replace you can use "*/" and replace with "". That will remove anything before the "/".

Ex: /My Documents/Downloads/Files/testing.xls
/Documents And Settings/Users/Desktop/TestFile/ReadOnly/testing.doc

So what I want to end up with is just:

testing.xls
testing.doc

I have tried Replace(), that only replaces the text and will not remove it, so I am not sure where to begin on this one.

Thank you in advance!



Edit:

The closest thing I have come up with is:
RTrim(Right([F1],InStr(2,[F1] & "-","-",1)-1))

But that does not stop at the "/" and sometimes it removes part of the file name...
__________________
I adore chaos, because I love to produce order.

Last edited by Dreambringer; 08-Feb-2010 at 06:53 PM.. Reason: Added content
slurpee55's Avatar
Computer Specs
Distinguished Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
08-Feb-2010, 07:29 PM #2
Offhand, in a query this shows only the right data - but only for names the same length as "testing."
Expr1: IIf([name]<>"*/",Right([name],11),"Null")
Longer or shorter names would require you to change the value 11 to something else. I can't figure out how to find the last "/" in a string in Access....
__________________
Iowa? I could have sworn this was heaven.
Well, I think I can answer this question most successfully in mime.
My theme song... | Affero - rate me!
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
09-Feb-2010, 07:33 AM #3
This can be done in a Query using a VBA module or usinf VBA.
It might also be possible using slurpees example with the addition of a couple of extra columns, one would use the
Len([name]<>"*/")
or even include it in the IIF that would tell you how long the text was to the right of the last "/", that can then be used to subtract it from the Len([name]) to provide value that is currently 11.

Another alternative is to re-aquire the File data using File scripting and get the names from that.
__________________
OBP
I do not give up easily
turbodante's Avatar
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
09-Feb-2010, 09:45 AM #4
InStrRev will do it.

See query in attached.
Attached Files
File Type: zip extractEndnameafterslash.zip (13.1 KB, 5 views)
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
09-Feb-2010, 11:37 AM #5
Turbo, very nice where did you come across "InStrRev"?
turbodante's Avatar
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
09-Feb-2010, 11:44 AM #6
Quote:
Originally Posted by OBP View Post
Turbo, very nice where did you come across "InStrRev"?
There's a thing in Access2007 called Expression Builder where you can look up all available erm, expressions.

I was looking for the StrReverse equivalent that's available in Excel which does the same thing.
slurpee55's Avatar
Computer Specs
Distinguished Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
09-Feb-2010, 12:03 PM #7
Very cool....
Dreambringer's Avatar
Computer Specs
Senior Member with 1,366 posts.
 
Join Date: Jan 2005
Location: Austin, Texas
Experience: If its broken, Reformat..
09-Feb-2010, 01:19 PM #8
Quote:
Originally Posted by turbodante View Post
InStrRev will do it.

See query in attached.
Thats great!

I was hope'n I would not have to VBA it, which I did last night, but this will work much better then what I came up with!

Below was my solution, but I will be using yours

Code:
        Set db = CurrentDb()
    Set rs = db.OpenRecordset("tbl_data", dbOpenDynaset)
    rs.MoveFirst
    Do While Not rs.EOF
        With rs
            i = InStr(1, !F1, "/", vbTextCompare)
            Do While i > 0
                j = InStr(i + 1, !F1, "/", vbTextCompare)
                If j > i Then
                    i = j
                Else
                    Exit Do
                End If
            Loop
            
            If i > 0 Then
                length = Len(!F1)
                s = Right(!F1, length - i)
                
                .Edit
                !F1 = s
                .Update
            End If
            .MoveNext
        End With
    Loop
    rs.Close
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 03:07 AM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.