Problems with Right and Left Function

[BusMgr]

I have a form that the operator can use to select the file that the database should be linked to using the Common Dialog Form. The problem is that I am then taking that file name and turning it into the job number using the Right and Left function to get only the file name out of the path and file extension.

txtFiletoOpen is the control on the form that contains the path and file to open from the common dialog form, i.e. C:\Program Files\Jobs\G457.mdb

The code is as follows -

Dim strBackEnd as string

strBackEnd = Me.txtFiletoOpen.Value
strBackEnd = Right((Me.txtFiletoOpen.value), _
len(Me.txtFiletoOpen.value) _
- len(GetDBDir(Me.txtFiletoOpen.value)))
strBackEnd = Left(strBackEnd, len(strBackEnd) - 4)

It appears that the code is working up to the last line of code as I get strBackEnd = G457.mdb , when what I should get is strBackEnd = G457

Thanks for your help in advance.

BusMgr

 

[Steve101]

BusMgr,

Here's a little routine that Ive tested. Hopefully will fix the problem, and doesnt rely on the presence of a file extention, or folder structure. Note, it does'nt have any error handling incorporated at present.

Function GetJobNo(FullName)
'-------------------------------------------------------
'Find the position of the last slash. Also find the last
'full stop (if it exists.
'-------------------------------------------------------
ExtentionStartPos = Len(FullName) + 1
For i = Len(FullName) To 1 Step -1
ThisChar = Mid(FullName, i, 1)
If ThisChar = "." Then
ExtentionStartPos = i
End If
If ThisChar = "\" Then
Exit For
End If
Next i
GetJobNo = ""
For j = i + 1 To ExtentionStartPos - 1
GetJobNo = GetJobNo & Mid(FullName, j, 1)
Next j
End Function

I found your initial post difficult to diagnose because of the reference to the GetDBDir function; did'nt understand its role in the overall scheme of things.

I'm probably being a bit naughty, verbose and 'inefficient'
by doing it without using lefts or rights or mids, but this approach seemed to come more 'naturally' to me.

Hope this helps,

Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[danvlas]

In Steve101's post:
Either delete 'Option Explicit' at the top of the module or dim the variables in the function:

Dim ExtentionStartPos$
Dim ThisChar$

Not that I'm being obnoxious, but the code works great and shouldn't raise compile errors...

Guess this function does exactly the same thing, but using a Left and losing a For...Next loop

Function GetJobNo(FullPath)
Dim i As Integer
For i = Len(FullPath) To 1 Step - 1
If Mid(FullPath, i, 1) = "\" Then Exit For
GetJobNo = Mid(FullPath, i, 1) & GetJobNo
Next
If InStr(GetJobNo, ".&quot Then GetJobNo = Left(GetJobNo, InStr(GetJobNo, ".&quot-1)
End Function

Dan

 

[Steve101]

Dan,
Of course you're being obnoxious, but you're a pussycat when complared to some,



All the best,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[danvlas]

I had an error (lucky me nobody saw it):
Dim ExtentionStartPos$
should be
Dim ExtentionStartPos%


Dan

 

[KevinMoore]

How about using the FileSystemObject ... its got lots of properties to break up file specifications, like so:

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
strBackEnd = fso.GetBaseName(Me.txtFileToOpen.Value)

This also handles the objectionable case of a period in the file base name. I use the FileSystemObject for all kinds of file specification parsing.

Kevin

 

[danvlas]

Yup...seems the fastest way to skin this cat...

 

[Steve101]

Actually Dan, did notice, but didnt want to be nit-picky, (being one who is a firm believer in not having to declare each variable).

Kevin, the scripting object based solution is certainly the way to skin this cat:


Regards,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[BusMgr]

Thanks you guys. I'll use the scripting object based solution as it works.

Regards

BusMgr

 

[jnicks]

I think the 'fastest' way to render this particular felix domesticus epidermally challenged is not to use an object but use one simple system call.

I've been doing splitpaths with those annoying backscans for awhile and when I saw this post I knew there had to be a better way, like splitpath in C.

I just worked this up.

It should be an order of magnitude or two faster than those looping backscans with dymnamic strings.

Also, according to the doc, it should handle unicode chars characters as well. I'd wanna test that one.

how it works.

DIR gives back the long file name without path.
GetLongFileName gets both
path = GetLongFileName minus DIR

Done. return the suckers. tada, no loops.

I static-ed the buffers to make it really fast, that is optional.

I ignored the DIMs for a 6 line program. They seemed pretentious.

The "Declare Function" is one line running through &quotAs Long"

DefLng A-Z
Declare Function GetFullPathName Lib "kernel32" Alias "GetFullPathNameA" (ByVal lpFileName As String, ByVal nBufferLength As Long, ByVal lpBuffer As String, ByVal lpFilePart As String) As Long
Sub main()
  ret = splitpath("c:\autoexec.bat", path$, longfilnam$)
  MsgBox path$
End Sub


Function splitpath(fil$, path$, longfilnam$)
Static buf As String * 261, dummy As String * 20

  path$ = "": splitat = -1
  
  longfilnam$ = Dir$(fil$)
  If 0 = Len(longfilnam$) Then Exit Function
  
  k = GetFullPathName(fil$ + Chr$(0), 260, buf, dummy)
  If k Then _
    path$ = Left(buf, k - Len(longfilnam$)): _
    splitat = 0
End Function

 

[KevinMoore]

Hi jnicks-

I believe that both Dir() and GetFullPathName actually read the directory structure on disk looking for the file, which might compare negatively, time wise, to string machinations in RAM. They also require the file specified to actually exist, which I'm uncertain fits in with BusMgr's overall code. Additionally, he/she is looking for just the base file name excluding the extension.

As far as the performance of the FileSystemObject "black box" is concerned, I wish I knew more about it and what it takes in VBA to actually "create" the object. It has several nifty methods though, and I've ended up using it whenever I have file spec problems to solve.

Hey, I've never heard variable declarations described as pretentious before, no matter how small the procedure. ;-)

Kevin

 

[BusMgr]

Whilst you guys are still answering the first part of this, I've moved on and wrote my error coding etc. Thank you.

I wish that someone could explain clearly the difference between text and numeric files when writing code.

Anyhow, I want to create a new record and INSERT INTO table Legals the job number.

strSQL = "INSERT INTO Legals (JobNo) Values " & "'" & strBackEnd "'" & &quot""
DoCmd.RunSQL strSQL

JobNo is a text value with all the attributes that are allowed in MS file naming convention.

Thanks
BusMgr
P.S. Sometimes its the little things that bite me in the butt!