-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Expression similar to SEARCH within a query
- Thread starter jonlj
- Start date
In the raw table i have a column with data similar to
"Last Modified on 12/30/2003 at 3:09:37 PM". However the text preceeding the date is changing. I want the column in my query to just show the date. Expr1: DateValue(Mid([mod],17,10)) in this example this would give the right result for that row but not the next where the record has less text preceeding the date. I need to trap the location of the first "/" if i can so I am able to pick the date out.
"Last Modified on 12/30/2003 at 3:09:37 PM". However the text preceeding the date is changing. I want the column in my query to just show the date. Expr1: DateValue(Mid([mod],17,10)) in this example this would give the right result for that row but not the next where the record has less text preceeding the date. I need to trap the location of the first "/" if i can so I am able to pick the date out.
Upvote
0
Norie
Well-known Member
- Joined
- Apr 28, 2004
- Messages
- 76,377
- Office Version
- 365
- Platform
- Windows
Do you have any control over the Excel spreadsheet?
Are these entries being created in Excel via code?
If they are I would suggest going back to that code and altering it to seperate the date/time and action into seperate columns.
Are these entries being created in Excel via code?
If they are I would suggest going back to that code and altering it to seperate the date/time and action into seperate columns.
Upvote
0
Cant do that. I have an excel sheet per day for the last 3 years just ftp'd to me. I'm writing a looping import to append all data to a main table. I dont want to have to utilise excel for this. I guess I could try writing a new function in access to do this.. (maybe not something i should consider at 5pm on a Friday evening)
Upvote
0
Andrew Fergus
MrExcel MVP
- Joined
- Sep 9, 2004
- Messages
- 5,462
- Office Version
- 365
- 2021
- 2016
- Platform
- Windows
Hi,
this won't stop the problem from occuring, as Norie has pointed out, but assuming the string always has the words "on" and "at" around the date then one way of extracting the date would be using something like this :
Mid([text], InStr([text], "on") +3, InStr([text], "at") -InStr([text], "on") -4)
in your query. Be sure to use your actual field name where I have used 'text'.
HTH, Andrew.
this won't stop the problem from occuring, as Norie has pointed out, but assuming the string always has the words "on" and "at" around the date then one way of extracting the date would be using something like this :
Mid([text], InStr([text], "on") +3, InStr([text], "at") -InStr([text], "on") -4)
in your query. Be sure to use your actual field name where I have used 'text'.
HTH, Andrew.
Upvote
0
Similar threads
- Question
- Solved
