combine fields (1 Viewer)

[kitty77]

I have a field on a form that has a value of "\files\pdf\abc.pdf
How can I create an unbound field that takes just the "abc.pdf" value and combines it.
I want the new field value to look like this: www.abc.com/abc.pdf

="www.abc.com/" & "abc.pdf"

 

[Ranman256]

usage:
getDirname sFullFilePath, sDir, sName

'given filepath, passes back: Dir name , filename
Public Sub getDirName(ByVal psFilePath, ByRef prvDir, Optional ByRef prvFile)
'psFilePath: full file path given
'prvDir : directory name output
'prvFile: filename only output
Dim i As Integer, sDir As String
i = InStrRev(psFilePath, "\")          'not available in '97
If i > 0 Then
   prvDir = Left(psFilePath, i)
   prvFile = Mid(psFilePath, i + 1)
End If
End Sub

 

[kitty77]

I don't follow?

 

[kitty77]

I just want to use the value after the last "\" which is the abc.pdf value

Then I could use something like this: ="www.abc.com/" & "abc.pdf"

 

[GPGeorge]

I have a field on a form that has a value of "\files\pdf\abc.pdf
How can I create an unbound field that takes just the "abc.pdf" value and combines it.
I want the new field value to look like this: www.abc.com/abc.pdf

="www.abc.com/" & "abc.pdf"

The control on the form is bound to a field which contains a string with the value ""\files\pdf\abc.pdf"
The task is to extract ONLY the final portion of that string and concatenate (that's the Access term we want here), the initial string to it.

strNewConcantenatedstring = "www.abc.com/" & Mid([YourFieldNameGoesHere], InstrRev([YourFieldNameGoesHere], "\") -1)

That ought to do it.

However, this should NOT be stored in another field in the table. We don't store redundant data. DISPLAY it in a control on the form.

 

[kitty77]

Yes, that's what I'm trying do. The field that has the "\files\pdf\abc.pdf" is Mlink and the unbound field is Text95.
So, what do I replace for YourFieldNameGoesHere. What goes where? I'm putting the code in a command button.

 

[kitty77]

this is what I tried in a command button
strNewConcantenatedstring = "www.abc.com/" & Mid([Text99], InStrRev([Mlink], "\") - 1)

Nothing happens...

 

[The_Doc_Man]

There are two parts to this problem. First, get the file name. Second, use it in some constructive way.

I'm going to refer you to the File System Object (FSO) that is part of the Windows Scripting library. Where you want to use this, you have to make a reference to it. So from the VBA code window, Tools >> References and then scroll through the list of the unchecked libraries until you find Windows Scripting. Check that. (If it isn't checked already.)

Now you can create a windows FSO in code. This can help you to manipulate file information.

FileSystemObject object

Office VBA reference topic

docs.microsoft.com

Once you have an object variable that is an FSO and you have that fully qualified file spec, you can extract parts from the spec. For example...

GetFileName method (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Once you have the path and name, you can concatenate them as you need. I would advise that you browse from the top-level page that explains the FSO and its many subroutines. This is an invaluable resource if you have to work on files.

You might have had better results using Right rather than Mid to do that filename extract, but the FSO.GetFileName method is more reliable.

 

[kitty77]

I thought I could find a simple solution...