Left, Right Trim

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my table the field name is 'NameOfFile' . Basically data looks like this,
'C:\Folder\Data\5-22-06 this month.txt'.
I need get some portion of this data, only date, for exmple '5-22-06'.

This is only removes everything after date,
but also i need to remove everything after '\'.

Left([FILENAME],Len([FILENAME])-15)

Thank you for helping me.
 
Below depends heavily on the string always starting with 'C:\Folder\Data\'
and a space being in the string where right behind the date portion. One or
two character months and days shouldn't hurt it and neither should 2 digit
years. A null value could get ugly as it will produce a run time error 94.

Left(Mid([FILENAME],16),Instr(Mid([FILENAME],16)," ")-1)
 
If the length of the string preceding the date is always 15 characters, and
the date is always 8 characters (e.g., mm-dd-yy), you could use:

Mid([NameOfFile],16,8)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
It does work, but if my date changes then it will not work.
For example:
record showing this ''C:\Folder\Data\5-22-06 this month.txt'.' Next time
could be ''C:\Folder\Data\12-22-06 this month.txt'.
This Mid([NameOfFile],16,8) works for this data , but not for this
''C:\Folder\Data\12-22-06 this month.txt'. When i run it show this '2-22-06'
instead of this '12-22-06'.

Jeff Boyce said:
If the length of the string preceding the date is always 15 characters, and
the date is always 8 characters (e.g., mm-dd-yy), you could use:

Mid([NameOfFile],16,8)

Regards

Jeff Boyce
Microsoft Office/Access MVP


GGill said:
In my table the field name is 'NameOfFile' . Basically data looks like
this,
'C:\Folder\Data\5-22-06 this month.txt'.
I need get some portion of this data, only date, for exmple '5-22-06'.

This is only removes everything after date,
but also i need to remove everything after '\'.

Left([FILENAME],Len([FILENAME])-15)

Thank you for helping me.
Click to expand...
Click to expand...
 
GGill said:
In my table the field name is 'NameOfFile' . Basically data looks like this,
'C:\Folder\Data\5-22-06 this month.txt'.
I need get some portion of this data, only date, for exmple '5-22-06'.

This is only removes everything after date,
but also i need to remove everything after '\'.

Left([FILENAME],Len([FILENAME])-15)

Thank you for helping me.
Click to expand...

This function should work in all cases. Put it in a standard module and
call it from your query as SELECT GetFilePart(MyField). (Watch out for
line wrapping.)

Public Function GetFilePart(FromPath As String) As String
' returns the first token following the last "\" (if any)
' as delimited by spaces
Dim BackSlash As Long
Dim FirstSpace As Long
GetFilePart = FromPath
BackSlash = InStrRev(FromPath, "\")
FirstSpace = InStr(BackSlash + 1, FromPath, " ") - BackSlash
Select Case BackSlash
Case Is = Len(FromPath)
' nothing follows BackSlash; no file specified
GetFilePart = "<not specified>"
Case Is > 0:
' we have a BackSlash
If FirstSpace > 0 Then
' ... and a space: this is well-formed
GetFilePart = Mid(FromPath, BackSlash + 1, FirstSpace - 1)
Else
' we have BackSlash, but no space
GetFilePart = Mid(FromPath, BackSlash + 1)
End If
Case 0:
' no BackSlash
If FirstSpace > 0 Then
' ... but a space: this is still legit
GetFilePart = Left(FromPath, FirstSpace - 1)
End If
End Select
End Function
 
One way is to use a regular expression. This one

\d{1,2}-\d{1,2}-\d{2}(?!.*\\)

will find a date-like pattern such as
5-22-06
10-5-05
11-28-06
but only if it comes after the last \ in the string.

A simple way to use it is with the rgxExtract function at
http://www.j.nurick.dial.pipex.com/Code/index.htm

In my table the field name is 'NameOfFile' . Basically data looks like this,
'C:\Folder\Data\5-22-06 this month.txt'.
I need get some portion of this data, only date, for exmple '5-22-06'.

This is only removes everything after date,
but also i need to remove everything after '\'.

Left([FILENAME],Len([FILENAME])-15)

Thank you for helping me.
Click to expand...
 
Back
Top