Topic: Strings Separate and Extensions

R

rebelscum0000

Dear all,

This has to be simple but something I am doing wrong

I have a Tbl called TempData_Tbl with more than 10000 records
I want to separate the string before the space, the File name and the
extension
In order to update them (separately) in the same Tbl in other field
names

The string before the space does not have the same length
(d4bd504fb63bd00454296a65d283a94c), I only can do this using somehow
The Chr Function (32) or " " but my code is not working this is my
first problem

The second is that some files names has 2 extension (.zip.log) I know
how to use InStrRev Function but I do not know how to deal when my
recodset finds a File name with one or two extensions or dots

This is an example of the entire string.

d4bd504fb63bd00454296a65d283a94c Default.doc
f12cd10a64d9c838de3346e7427f6d07811 accwebfaq-10-10-00-A9.zip.log
45e1f4a0bf8859f02536c6 AccWebFaqv9.mdb

Any idea, suggestion or help is more than welcome

Thanks in advance
Regards,
Antonio Macias

This is my code which is not working

'Initialize Variables
MyCount = 0
MyTempDataCount = DCount("*", "TempData_Tbl")

Set ccgdb = CurrentDb
Set ccgrs = ccgdb.OpenRecordset("TempData_Tbl", dbOpenDynaset)

For V = 1 To MyTempDataCount

MyTempDataID = ccgrs.Fields("ID")
MyDirHashFiles = ccgrs.Fields("DirHashFiles")
HasFilesLen = Len(MyDirHashFiles)
'Debug.Print "ID = " & MyTempDataID & " And DirHashFiles = " &
MyDirHashFiles & " Len: " & HasFilesLen

MyPos = 1

'Do

MyStart = MyPos + MyCount
MidWords = Left(MyDirHashFiles, MyStart)

If MidWords = Chr(32) Then '<- Why this line do not detect an space?
Debug.Print "DO SOMETHING"
Else

MyCount = MyCount + 1

'Infinite Loop check
If MyCount > 20 Then
End
End If

ccgrs.MoveNext

End If
Next V

ccgrs.Close
ccgdb.Close
Set ccgdb = Nothing
 
G

George Nicholson

MidWords = Left(MyDirHashFiles, MyStart)

Left("ABCDE",3) will return "ABC"

Since you are testing for a single character, I think you want to use Mid()

MidWords = Mid(MyDirHashFiles, MyStart,1)

Mid("ABCDE",3,1) will return "C"

HTH,
 
D

Douglas J. Steele

No need for the loop. To get what's before the space, use:

Left$(MyDirHashFiles, InStr(MyDirHashFiles, " ") - 1)

To get what's after the space, use:

Mid$(MyDirHashFiles, InStr(MyDirHashFiles, " ") + 1)

If you want to separate the extension from the file name, try:

strFileAndExt = Mid$(MyDirHashFiles, InStr(MyDirHashFiles, " ") + 1)
strFile = Left$(strFileAndExt, InStr(strFileAndExt, ".") - 1)
strExt = Mid$(strFileAndExt, InStr(strFileAndExt, ".") + 1)
 
R

rebelscum0000

Dear Doug Steele,

As always thank you very much, I having still some problems I hope you
can help me:

In very few records I get:
5f45f7def5629d39a Online.TV.Player.v2.6.WinALL.GTA-DVT.rar

After space Line:
'Gets Hash or what is before the space
MyHash = Left$(MyDirHashFiles, InStr(MyDirHashFiles, " ") - 1)
5f45f7def5629d39a

'Gets File with its extension or what is after the space
MyFileExtensions = Mid$(MyDirHashFiles, InStr(MyDirHashFiles, " ") +
1)
Online.TV.Player.v2.6.WinALL.GTA-DVT.rar

'Only File
MyFileOnly = Left$(MyFileExtensions, InStr(MyFileExtensions, ".") - 1)
Online

'Only Extension Or Extensions
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions, ".")
+ 1)
TV.Player.v2.6.WinAL

-

Code working perfect:
d5c25c0cca2aedc9d83b50c412df3823 ea2ksetup.exe
After space Line:
'Gets Hash or what is before the space
MyHash = Left$(MyDirHashFiles, InStr(MyDirHashFiles, " ") - 1)
d5c25c0cca2aedc9d83b50c412df3823

'Gets File with its extension or what is after the space
MyFileExtensions = Mid$(MyDirHashFiles, InStr(MyDirHashFiles, " ") +
1)
ea2ksetup.exe

'Only File
MyFileOnly = Left$(MyFileExtensions, InStr(MyFileExtensions, ".") - 1)
ea2ksetup

'Only Extension Or Extensions
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions, ".")
+ 1)
exe

In my short experience I only have seen a file with two extension like
obp5setup.exe.log
Better than me you know that the extensions has a period and 3
characters
'Only Extension Or Extensions
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions, ".")
+ 1)
exe.log

Once the process is finish (takes more than 9 minutes) I would like
the code checks again for this “very few records†that I am getting
and try to fix them with the criteria if MyExtensionsOnly <> (Not
equal to) period + 3 characters (In the case the file has only one
extension) or period + 3 characters + period + 3 characters (In the
case the file has two extensions) update them again in order to get
the results I need

In other words I need the periods also (.exe or .rar.log)

Is this possible?

Thanks in advance

Regards,
Antonio Macias

This is my code:

'Initialize New Event
Dim sQL19 As String
Dim sQL20 As String
Dim MyCount As Integer
Dim MyTempDataCount As Integer
Dim MyTempDataID As Integer
Dim MyDirHashFiles As String
Dim MyHash As String
Dim MyFileExtensions As String
Dim MyFileOnly As String
Dim MyExtensionsOnly As String
Dim V As Integer

'Initialize Variables
MyCount = 0

sQL19 = _
"SELECT TempData_Tbl.ID, TempData_Tbl.DirHashFiles,
TempData_Tbl.DataProcess " & _
"From TempData_Tbl " & _
"WHERE TempData_Tbl.DirHashFiles Not Like 'DIR*' AND
TempData_Tbl.DataProcess = 0 "

'Initialize Variables
MyTempDataCount = DCount("[ID]", "TempData_Tbl", "[DirHashFiles] Not
Like 'DIR*' AND [DataProcess] = 0 ")

Set ccgdb = CurrentDb
Set ccgrs = ccgdb.OpenRecordset(sQL19, dbOpenDynaset)

For V = 1 To MyTempDataCount

MyTempDataID = ccgrs.Fields("ID")
MyDirHashFiles = ccgrs.Fields("DirHashFiles")
'MsgBox "My iD = " & MyTempDataID & " And My Hash is " &
MyDirHashFiles
'Gets Hash or what is before the space
MyHash = Left$(MyDirHashFiles, InStr(MyDirHashFiles, " ") - 1)
'Gets File with its extension or what is after the space
MyFileExtensions = Mid$(MyDirHashFiles, InStr(MyDirHashFiles, " ") +
1)
'Only File
MyFileOnly = Left$(MyFileExtensions, InStr(MyFileExtensions, ".") -
1)
'Only Extension Or Extensions
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions, ".")
+ 1)

sQL20 = _
"UPDATE TempData_Tbl SET TempData_Tbl.Hash = """ & MyHash & """,
TempData_Tbl.FileExtensions = """ & MyFileExtensions & """,
TempData_Tbl.FileOnly = """ & MyFileOnly & """,
TempData_Tbl.ExtensionOnly = """ & MyExtensionsOnly & """ " & _
"WHERE TempData_Tbl.ID = " & MyTempDataID & " AND
TempData_Tbl.DirHashFiles Not Like 'DIR*' AND TempData_Tbl.DataProcess
= 0 "

CurrentDb.Execute sQL20, dbFailOnError

ccgrs.MoveNext

Next V

ccgrs.Close
ccgdb.Close
Set ccgdb = Nothing
 
D

Douglas J. Steele

Actually, your statement that "extensions has a period and 3 characters"
isn't actually correct. .html and .jpeg are very common extensions. The
latest version of Access (Access 2007) uses .accdb and .accde

Try using:

MyExtensionsOnly = Mid$(MyFileExtensions, InStrRev(MyFileExtensions, ".")+
1)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dear Doug Steele,

As always thank you very much, I having still some problems I hope you
can help me:

In very few records I get:
5f45f7def5629d39a Online.TV.Player.v2.6.WinALL.GTA-DVT.rar

After space Line:
'Gets Hash or what is before the space
MyHash = Left$(MyDirHashFiles, InStr(MyDirHashFiles, " ") - 1)
5f45f7def5629d39a

'Gets File with its extension or what is after the space
MyFileExtensions = Mid$(MyDirHashFiles, InStr(MyDirHashFiles, " ") +
1)
Online.TV.Player.v2.6.WinALL.GTA-DVT.rar

'Only File
MyFileOnly = Left$(MyFileExtensions, InStr(MyFileExtensions, ".") - 1)
Online

'Only Extension Or Extensions
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions, ".")
+ 1)
TV.Player.v2.6.WinAL

-?

Code working perfect:
d5c25c0cca2aedc9d83b50c412df3823 ea2ksetup.exe
After space Line:
'Gets Hash or what is before the space
MyHash = Left$(MyDirHashFiles, InStr(MyDirHashFiles, " ") - 1)
d5c25c0cca2aedc9d83b50c412df3823

'Gets File with its extension or what is after the space
MyFileExtensions = Mid$(MyDirHashFiles, InStr(MyDirHashFiles, " ") +
1)
ea2ksetup.exe

'Only File
MyFileOnly = Left$(MyFileExtensions, InStr(MyFileExtensions, ".") - 1)
ea2ksetup

'Only Extension Or Extensions
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions, ".")
+ 1)
exe

In my short experience I only have seen a file with two extension like
obp5setup.exe.log
Better than me you know that the extensions has a period and 3
characters
'Only Extension Or Extensions
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions, ".")
+ 1)
exe.log

Once the process is finish (takes more than 9 minutes) I would like
the code checks again for this "very few records" that I am getting
and try to fix them with the criteria if MyExtensionsOnly <> (Not
equal to) period + 3 characters (In the case the file has only one
extension) or period + 3 characters + period + 3 characters (In the
case the file has two extensions) update them again in order to get
the results I need

In other words I need the periods also (.exe or .rar.log)

Is this possible?

Thanks in advance

Regards,
Antonio Macias

This is my code:

'Initialize New Event
Dim sQL19 As String
Dim sQL20 As String
Dim MyCount As Integer
Dim MyTempDataCount As Integer
Dim MyTempDataID As Integer
Dim MyDirHashFiles As String
Dim MyHash As String
Dim MyFileExtensions As String
Dim MyFileOnly As String
Dim MyExtensionsOnly As String
Dim V As Integer

'Initialize Variables
MyCount = 0

sQL19 = _
"SELECT TempData_Tbl.ID, TempData_Tbl.DirHashFiles,
TempData_Tbl.DataProcess " & _
"From TempData_Tbl " & _
"WHERE TempData_Tbl.DirHashFiles Not Like 'DIR*' AND
TempData_Tbl.DataProcess = 0 "

'Initialize Variables
MyTempDataCount = DCount("[ID]", "TempData_Tbl", "[DirHashFiles] Not
Like 'DIR*' AND [DataProcess] = 0 ")

Set ccgdb = CurrentDb
Set ccgrs = ccgdb.OpenRecordset(sQL19, dbOpenDynaset)

For V = 1 To MyTempDataCount

MyTempDataID = ccgrs.Fields("ID")
MyDirHashFiles = ccgrs.Fields("DirHashFiles")
'MsgBox "My iD = " & MyTempDataID & " And My Hash is " &
MyDirHashFiles
'Gets Hash or what is before the space
MyHash = Left$(MyDirHashFiles, InStr(MyDirHashFiles, " ") - 1)
'Gets File with its extension or what is after the space
MyFileExtensions = Mid$(MyDirHashFiles, InStr(MyDirHashFiles, " ") +
1)
'Only File
MyFileOnly = Left$(MyFileExtensions, InStr(MyFileExtensions, ".") -
1)
'Only Extension Or Extensions
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions, ".")
+ 1)

sQL20 = _
"UPDATE TempData_Tbl SET TempData_Tbl.Hash = """ & MyHash & """,
TempData_Tbl.FileExtensions = """ & MyFileExtensions & """,
TempData_Tbl.FileOnly = """ & MyFileOnly & """,
TempData_Tbl.ExtensionOnly = """ & MyExtensionsOnly & """ " & _
"WHERE TempData_Tbl.ID = " & MyTempDataID & " AND
TempData_Tbl.DirHashFiles Not Like 'DIR*' AND TempData_Tbl.DataProcess
= 0 "

CurrentDb.Execute sQL20, dbFailOnError

ccgrs.MoveNext

Next V

ccgrs.Close
ccgdb.Close
Set ccgdb = Nothing
 
R

rebelscum0000

Actually, your statement that "extensions has a period and 3 characters"
isn't actually correct. .html and .jpeg are very common extensions. The
latest version of Access (Access 2007) uses .accdb and .accde
Click to expand...

As always you are right, I forget about that
Try using:
Click to expand...
MyExtensionsOnly = Mid$(MyFileExtensions, InStrRev(MyFileExtensions, ".")+
1)
Click to expand...

Only returns the first or last extension
myfile.exe.log

Returns only
log

So what I did was add a period to the SQL string

TempData_Tbl.ExtensionOnly = '.' & """ & MyExtensionsOnly & """ "

sQL20 = _
"UPDATE TempData_Tbl SET TempData_Tbl.Hash = """ & MyHash & """,
TempData_Tbl.FileExtensions = """ & MyFileExtensions & """,
TempData_Tbl.FileOnly = """ & MyFileOnly & """,
TempData_Tbl.ExtensionOnly = '.' & """ & MyExtensionsOnly & """ " & _
WHERE TempData_Tbl.ID = " & MyTempDataID & " AND
TempData_Tbl.DirHashFiles Not Like 'DIR*' AND TempData_Tbl.DataProcess
= 0 "

'Debug.Print sQL20
CurrentDb.Execute sQL20, dbFailOnError

And it works as I wanted, now the problem is the String is toooooo
loooooooong how can I separate?

Finally in your experience is "common" a file with more than 3
extensions?
let say
Myfile.jpeg.accdb.log.rar (This is an example)

Thanks in advance for your help

Regards,
Antonio Macias
 
D

Douglas J. Steele

rebelscum0000 said:
Only returns the first or last extension
myfile.exe.log

Returns only
log
Click to expand...

Realistically, the extension for myfile.exe.log IS log. Windows doesn't care
about the bits of text in there that could be extensions (like "exe") when
it's determining the default application to use for the file.
So what I did was add a period to the SQL string
Click to expand...

You could have used this instead:

MyExtensionsOnly = Mid$(MyFileExtensions, InStrRev(MyFileExtensions, "."))
TempData_Tbl.ExtensionOnly = '.' & """ & MyExtensionsOnly & """ "

sQL20 = _
"UPDATE TempData_Tbl SET TempData_Tbl.Hash = """ & MyHash & """,
TempData_Tbl.FileExtensions = """ & MyFileExtensions & """,
TempData_Tbl.FileOnly = """ & MyFileOnly & """,
TempData_Tbl.ExtensionOnly = '.' & """ & MyExtensionsOnly & """ " & _
WHERE TempData_Tbl.ID = " & MyTempDataID & " AND
TempData_Tbl.DirHashFiles Not Like 'DIR*' AND TempData_Tbl.DataProcess
= 0 "

'Debug.Print sQL20
CurrentDb.Execute sQL20, dbFailOnError

And it works as I wanted, now the problem is the String is toooooo
loooooooong how can I separate?
Click to expand...

What's your definition of "toooooo loooooooong"? Is the problem that it's
too big to fit into the field in your table? How big did you make the field?
The default size of a text field is 50 bytes, but you can increase that to
255 bytes. If that's not big enough, use a Memo field. If you separated it,
how would you want it to be?
Finally in your experience is "common" a file with more than 3
extensions?
let say
Myfile.jpeg.accdb.log.rar (This is an example)
Click to expand...

Couldn't say: I've never bothered checking.
 
R

rebelscum0000

Dear Doug Steele,
The above statement is wrong
Myfile.exe.rar.log (example)
Was returning only .rar or .log I do not remember at this moment
Right!
Click to expand...

I will try!

I am sure one of my problems is that I do not understand how some of
Returning Strings from Functions works like Mid$, Left$, Right$ ect...
I have Office 2002 w/SP3 English Version, when I click on Microsoft
visual basic help And search for let's say Mid$, display the Mid
Function! (Also I searched in this group for a good example with any
luck)
Could you please be so kind to explain me what this function works &
does? Only the basic Mid$, Left$ or let me know where I can learn
more?

Nop, the Sql is too long to see it in my small monitor when I am
coding in a module, how to split a long SQL like
sQL20 = _
"UPDATE TempData_Tbl SET TempData_Tbl.Hash = """ & MyHash & """,
TempData_Tbl.FileExtensions = """ & MyFileExtensions & """,
TempData_Tbl.FileOnly = """ & MyFileOnly & """,
TempData_Tbl.ExtensionOnly = '.' & """ & MyExtensionsOnly & """ " & _
"WHERE TempData_Tbl.ID = " & MyTempDataID & " AND
TempData_Tbl.DirHashFiles Not Like 'DIR*' AND TempData_Tbl.DataProcess
= 0 "

I see this SQL string in 2 lines I want something like

sQL16 = _
"INSERT INTO TempInfo_Tbl ( ID, DirFolder ) " & _
"SELECT TempData_Tbl.ID, TempData_Tbl.DirHashFiles " & _
"FROM TempData_Tbl " & _
"WHERE TempData_Tbl.DirHashFiles Like 'DIR*' "

Every time I try to split, separate or divide my SQL turns red!
Finally in your experience is "common" a file with more than 3
extensions?
Click to expand...

either I, that why I am asking LOL, but if I found out I will let you
know :)
Thank you very much in advance

Regards,
Antonio Macias
 
L

Larry Linson

The $ appended to some functions specifies "this is a string function", but
given Access' built-in type converstions, you'll find that Mid and Mid$
return the same values:

Function RetVal(pstrInput As String, pintPos As Integer) As String
RetVal = Mid$(pstrInput, pintPos, 1)
End Function

Function RetVal(pstrInput As String, pintPos As Integer) As String
RetVal = Mid(pstrInput, pintPos, 1)
End Function

Function RetVal(pstrInput As String, pintPos As Integer) As Variant
RetVal = Mid$(pstrInput, pintPos, 1)
End Function

Function RetVal(pstrInput As String, pintPos As Integer) As Variant
RetVal = Mid(pstrInput, pintPos, 1)
End Function

Execute each of these variations from the Immediate Window with:

? RetVal("ABC",2)

and each will return the value

B

Larry Linson
Microsoft Access MVP
 
R

rebelscum0000

What do you not understand?

MyFileOnly = Left$(MyFileExtensions, InStr(MyFileExtensions, ".") - 1
MyExtensionsOnly = Mid$(MyFileExtensions, InStr(MyFileExtensions,
".")

How I can understand this lines? why -1 if I change it for + 2 what
the function does?

I have an example

MyFile Only = "#AnyString#"
I Want to remove the # from the variable or

MyFile Only = "AnyString "

I want to remove the extra space at the end of the "AnyString" without
using Trim, searching
with the function InStr or maybe InStrRev...

In a few words I want to understand it not only cut and paste into my
code

Thanks in advance for any help
Regards,
Antonio Macias
 
D

Douglas J. Steele

In

MyFileOnly = Left$(MyFileExtensions, InStr(MyFileExtensions, ".") - 1)

InStr(MyFileExtensions, ".") returns the position of the first period in the
text contained in MyFileExtensions.

Subtracting one from that tells you how many characters are in the string in
front of that first period.

Telling the Left function that number means that MyFileOnly will contain
only the characters in front of the first period.

As I believe I've told you elsewhere though (at least, I think it was you
I've had this discussion with already), you likely want

MyFileOnly = Left$(MyFileExtensions, InStrRev(MyFileExtensions, ".") - 1)

InStrRev(MyFileExtensions, ".") will return the position of the last period
in the text contained in MyFileExtensions, so that the Left function will
return the characters in front of the last period.

For more information, check
http://office.microsoft.com/en-us/access/HA012288811033.aspx?pid=CH100728911033
(and follow the links from there to the details for the other functions such
as Left, Right, Len and so on). See also
http://office.microsoft.com/en-us/access/HA012288571033.aspx to learn about
the InStr function (and follow the link to learn about InStrRev)
 
R

rebelscum0000

Dear Doug Steele,

Thank you very much for your help, Finally I Understood :)

Regards,
Antonio Macias
 
Top