Find and Replace Query or Macro

[dlbDennis]

ne1 help me with a find and replace query or macro. What I'm trying to do is shorten the path to external PDF files after I inport them into field TxtPDFPath.

Example; after inport of files path reads
c:\w2kdata\pdffiles\2005\somepdf.pdf
c:\w2kdata\pdffiles\2005\january\someother.pdf
c:\w2kdata\pdffiles\2006\another.pdf

I would like to shorten all imported paths to
pdffiles\whatever

Example;
pdffiles\2005\somepdf.pdf
pdffiles\2005\january\someother.pdf
pdffiles\2006\another.pdf
and so on

 

[ajetrumpet]

What I'm trying to do is shorten the path to external PDF files after I inport them into field TxtPDFPath.

Example; after inport of files path reads
c:\w2kdata\pdffiles\2005\somepdf.pdf
c:\w2kdata\pdffiles\2005\january\someother.pdf
c:\w2kdata\pdffiles\2006\another.pdf

I would like to shorten all imported paths to
pdffiles\whatever

Example;
pdffiles\2005\somepdf.pdf
pdffiles\2005\january\someother.pdf
pdffiles\2006\another.pdf
and so on

You will do an update query here, and if all of your file locations are going to start with "c:\w2kdata\", then just trim that portion of the value off for the new updated value to be stored...

UPDATE...

  table.TxtPDFPath = Right([TxtPDFPath], 

    Len([TxtPDFPath]) - Len(Left([TxtPDFPath], 11)))

"c:\w2kdata\" is 11 characters long...

 

[badermsb]

Not sure how to do that with a query/macro, but here is some code that will work.

Sub UpdateRecords()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table_Name", dbOpenTable)

If Not rs.BOF And Not rs.EOF Then
Do Until rs.EOF
If Left(rs!Col_Name, 11) = "c:\w2kdata\" Then
rs.Edit
rs!Col_Name = Mid(rs!Col_Name, 12)
rs.Update
End If
rs.MoveNext
Loop
End If

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

 

[ajetrumpet]

If Not rs.BOF And Not rs.EOF Then
Do Until rs.EOF
If Left(rs!Col_Name, 11) = "c:\w2kdata\" Then
rs.Edit
rs!Col_Name = Mid(rs!Col_Name, 12)
rs.Update
End If
rs.MoveNext
Loop
End If

It's funny you wrote a loop here. DoCmd.RunSQL does the same thing for actions queries in VBA, and I'm sure it's a lot faster.

 

[dlbDennis]

You will do an update query here, and if all of your file locations are going to start with "c:\w2kdata\", then just trim that portion of the value off for the new updated value to be stored...

UPDATE...

  table.TxtPDFPath = Right([TxtPDFPath], 

    Len([TxtPDFPath]) - Len(Left([TxtPDFPath], 11)))

"c:\w2kdata\" is 11 characters long...

What I'm wanting is to remove the first part of the path so the path always starts with pdffiles/subfolders.
In doing this I can run it from any drive letter

 

[ajetrumpet]

What I'm wanting is to remove the first part of the path so the path always starts with pdffiles/subfolders.
In doing this I can run it from any drive letter

I know that Dennis, and the above statement I gave you will work perfectly if you just put it in an update query. Do you know how to update tables using queries?

 

[dlbDennis]

Not sure how to do that with a query/macro, but here is some code that will work.

Sub UpdateRecords()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Table_Name", dbOpenTable)

If Not rs.BOF And Not rs.EOF Then
Do Until rs.EOF
If Left(rs!Col_Name, 11) = "c:\w2kdata\" Then
rs.Edit
rs!Col_Name = Mid(rs!Col_Name, 12)
rs.Update
End If
rs.MoveNext
Loop
End If

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

This looks like asp code and I'm just running an access database...

 

[badermsb]

This looks like asp code and I'm just running an access database...

Access has code modules

 

[dlbDennis]

Access has code modules

Ok so how do I get it to remove the first part of the path and leve eveything starting with pdffiles/subfolders?
The subfolders change as data is added.

 

[ajetrumpet]

If you're talking to me Dennis, then do this...

Create a new query, click on "SQL view" under the appropriate menu option, and type this statement into the box that you see...

UPDATE [I]yourtable[/I] SET 

  yourtable.TxtPDFPath = Right([TxtPDFPath], 

    Len([TxtPDFPath]) - Len(Left([TxtPDFPath], 11)));

If you're not talking to me, nevermind.

 

[dlbDennis]

If you're talking to me Dennis, then do this...

Create a new query, click on "SQL view" under the appropriate menu option, and type this statement into the box that you see...

UPDATE [I]yourtable[/I] SET 

  yourtable.TxtPDFPath = Right([TxtPDFPath], 

    Len([TxtPDFPath]) - Len(Left([TxtPDFPath], 11)));

If you're not talking to me, nevermind.

This removed everything at the beginning of the path including pdffiles/subfolders and part of some of the file name on some of them

 

[ajetrumpet]

Did you make a backup of your data before you updated this?

I don't know what to tell you, other than the fact that the statement would not return what you described. It tells Access to return all characters from the right side of the string (value), until it reaches the 11th character from the left (side).

 

[dlbDennis]

Did you make a backup of your data before you updated this?

I don't know what to tell you, other than the fact that the statement would not return what you described. It tells Access to return all characters from the right side of the string (value), until it reaches the 11th character from the left (side).

Thanks that does work. Yes I always backup my data. Where I made my mistake was not running it on the imported table, so of course if you take 11 spaces from the path when it doesn't need it... it don't work. I'm just learning here thanks for the great help...
Great forum.

 

[ajetrumpet]

Sure....glad to hear you didn't lose anything!