macentrap
asked on
Extract latest date from tab-limited file
Hi Experts,
Please assist in extracting latest date from tab delimited file.
I get this file weekly when system are polled by agent.
This agent just adds the date to current field 'SMS_R_System.Agent Time' though not consistent.
would like to extract the latest date from this field as mentioned in desired_result tab.
Attached is the file for current data and result_data
I am importing this file in Access.
conversion formula can be in excel or during import.
Thank you
Regards
Sample.xlsx
Please assist in extracting latest date from tab delimited file.
I get this file weekly when system are polled by agent.
This agent just adds the date to current field 'SMS_R_System.Agent Time' though not consistent.
would like to extract the latest date from this field as mentioned in desired_result tab.
Attached is the file for current data and result_data
I am importing this file in Access.
conversion formula can be in excel or during import.
Thank you
Regards
Sample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you LSM Consulting and ssaqibh,
ssaqibh is yours excel based VBA, if so will try this one first over Access
Do I just paste the VBA ?
Thank You
ssaqibh is yours excel based VBA, if so will try this one first over Access
Do I just paste the VBA ?
Thank You
Right-click on the sheet tab name
Select view code
Select Insert > Module
Paste the given code in this window
Close the window
Put this function in C2 =getmaxdate(B2)
Copy it down
Select view code
Select Insert > Module
Paste the given code in this window
Close the window
Put this function in C2 =getmaxdate(B2)
Copy it down
Thoughts:
* these dates are coming in as dd/mm/yyyy so the regional settings will affect how they are treated by the VB function
* If you are working with dates, you should use Date data types to reduce the number of CDate() invocations.
* You don't need to start with an artificial date, you can use the first date in the comma-delimited list as the first date. This also reduces the number of comparisons/iterations
The following is based on the LSM code.
* these dates are coming in as dd/mm/yyyy so the regional settings will affect how they are treated by the VB function
* If you are working with dates, you should use Date data types to reduce the number of CDate() invocations.
* You don't need to start with an artificial date, you can use the first date in the comma-delimited list as the first date. This also reduces the number of comparisons/iterations
The following is based on the LSM code.
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM tmpImport")
Dim arr() As STring
Dim LatestDate As Date
Dim DateItem As Date
Dim i As Long
Do Until rst.EOF
arr = Split(rst("SMS_R_System.Agent Time"), ",")
LatestDate = CDate(arr(0))
For i =1 to Ubound(arr)
DateItem = CDate(arr(i))
If LatestDate < DateItem Then
LatestDate = DateItem
End If
Next i
'/ LatestDate would now contain the most recent date for this line
rst("Latest_Date") = LatestDate
rst.MoveNext
Loop
Wouldn't your line here fail, if there is no data in the column:
LatestDate = CDate(arr(0))
My point in pre-setting the LatestDate value was to avoid that possibility.
LatestDate = CDate(arr(0))
My point in pre-setting the LatestDate value was to avoid that possibility.
@LSM
If there is the possibility of no dates, the SQL would be changed to prevent zero-length (or Null) rows from populating the recordset. This would improve performance by reducing the amount of work to be performed and minimizing I/O.
If there is the possibility of no dates, the SQL would be changed to prevent zero-length (or Null) rows from populating the recordset. This would improve performance by reducing the amount of work to be performed and minimizing I/O.
ASKER
Apologies, for late reply.
THought i closed it.
Thank you ssaqibh and Experts for help
ssaqibh: easy to follow.
I didnt try other solutions
THought i closed it.
Thank you ssaqibh and Experts for help
ssaqibh: easy to follow.
I didnt try other solutions
Open in new window