parsing 1

[zrazzaq]

Hi all:
does anyone know how to parse a name such as:
"C:\Documents and Settings\razzaz\My Documents\Databases Testfiles\CMS\agentsumd_7012006.csv"
I need the 7012006 (the date part of this file name)..its wierd but the users what to do something with it.
Basically when the program runs it looks at the filename imports it into the table and then runs a report and mtd files based on the date altogether. So I just need to parse the filename and convert the date into a date.
Any suggestions?
I tried to split it but didn't work.
Thanks
Zishan

 

[Dan Freeman]

Start with JustFName() and JustStem(). (See the help file and all see alsos.)

See substr(), at() and rat() in the help file.

 

[zrazzaq]

cool I shall try that out...thanks

 

[MajP]

I am ignorant on those functions, I do not recognize them as vba or jet sql. If those do not work then simply

Public Function dateFromFile(strName As String) As Date
  Dim strDate As String
  Dim theYear As Integer
  Dim theDay As Integer
  Dim theMonth As Integer
  strDate = Mid(strName, InStrRev(strName, "_") + 1, Len(strName) - InStrRev(strName, "_") - 4)
  If Len(strDate) = 7 Then
    strDate = "0" & strDate
  ElseIf Len(strDate) > 8 Or Len(strDate) < 7 Then
    MsgBox "bad date format"
  End If
  theYear = CInt(Right(strDate, 4))
  theMonth = CInt(Left(strDate, 2))
  theDay = CInt(Mid(strDate, 3, 2))
  dateFromFile = DateSerial(theYear, theMonth, theDay)
End Function

example
?datefromfile("C:\Documents and Settings\razzaz\My Documents\Databases Testfiles\CMS\agentsumd_7012006.csv")

= 7/1/2006

 

[zrazzaq]

That is so cool...Thank you MajP