Access Update Query to Extract Date

I'm trying to use an Access updated query to extract the date from a field in the following formats:
#241 QIC Demo Merged Report Reopening 04-11-2018.xlsx
#003 QIC DEMO Merged Report 04-05-2016 REOPENING LOGS.xlsx
#118 QIC Demo Merged Report_08-18-2016 REOPENING LOGS.xlsx
#039 QIC Demo Merged Report 01.13.2017-Remand(1 of 2 ).xlsx
shieldscoAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
You can use this expression:

TrueDate = CDate(Mid(Replace(Replace([YourField], "Reopening ", ""), ".", "/"), 29, 10))

Open in new window

0
 
PatHartmanCommented:
I'm not sure you can do this with a query alone.  I think you will need to create a VBA function.

Since the date isn't in a fixed position or have fixed formatting, pulling it out will be more complicated.  Looking at the first three lines, I would suggest using Split() to pull out all the parts separated by spaces into an array and then looping through the array to find the element that tests positive to IsDate().  However the fourth one is problematic.  Not only is the date not a valid format, it is not surrounded by spaces.  To find that one, I would loop through the string selecting 8 characters at a time.  If the first character of the 8 is numeric, I would then replace dots with dashes and see if the field was a date.

Given that, I would probably just use the second method in all cases so I didn't have to write and test two pieces of code.
1
 
shieldscoAuthor Commented:
can you provide spit code
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
PatHartmanCommented:
Split() is a function.  

You've been a member of the site for 7 years.  If you don't know how to write VBA, you can use one of the site.s methods that allow you to hire someone to do your work for you.
0
 
shieldscoAuthor Commented:
Ok let someone else provide comments . I will close and award you 100 points. Thanks
0
 
PatHartmanCommented:
Asking people to write code that you will get paid for is rude.  Our goal here is to help people to help themselves, not to do their work for free.
0
 
shieldscoAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.