Finding date values in an alphanumeric field?

[Sarah]

I am looking for a way to find date values in an alphanumeric field in a
table. Here is an example of what fields might be:

em to rd 4/3/08 rvw 4/10/08 hold repairs 3/5/07

rvw 7/5/08 em to rd 06/23/08 rvw for funds

How do I figure out how to write an expression that would find these date
values in these strings and display them? Thank you.

 

[Roger Carlson]

How exactly do you plan to display them?

 

[Larry Linson]

I am looking for a way to find date values in an alphanumeric field in a
table. Here is an example of what fields might be:

em to rd 4/3/08 rvw 4/10/08 hold repairs 3/5/07

rvw 7/5/08 em to rd 06/23/08 rvw for funds

How do I figure out how to write an expression that would find these date
values in these strings and display them? Thank you.

You would have to try to determine the "configurations" that might represent
dates, write code to search for those "configurations", and display them...
it's easy to read the text and say, "Ah, that's a date" but with all the
potential formats (and finger-fumble errors) possible, not as easy to
recognize them in code.

From your examples, the verbal description would be "a delimiter followed
one or two numbers followed by a forward slash followed by one or two
numbers followed by a forward slash followed by one to four numbers,
followed by a delimiter or end of string". But that's not the only way the
data might have been entered, and you'll have to decide what you are going
to do with the dates after you detect them, and how you present them to the
user (in context would be best), so the user can decide if the application's
tenative guess at its being a date is correct, and what to do with that
particular date.

Full-text search (of which this is a subset) can be a full-time, lifetime
career. Hope for more or less well structured notes, and play it from
there.

Larry Linson
Microsoft Office Access MVP

 

[Sarah]

Probably in a report, but the presentation isn't extremely vital either.

 

[Roger Carlson]

On the contrary, it's extremely important.

Are you just going to have a bunch of numbers on your report? Or do they
mean something (that is, will they have a label)? Do you mean to display
them as separate date fields or as a single string? If the former, will
they be in separate lines of the report? If the latter, do you plan to
separate (delimit) them somehow?

So do you want them to look like any of these:

4/3/084/10/083/5/07
4/3/08; 4/10/08; 3/5/07
4/3/08 4/10/08 3/5/07

or maybe this:

4/3/08
4/10/08
3/5/07

or what?

Computers don't deal in abiguities, you have to tell it exactly what you
want.

 

[KARL DEWEY]

Try this --
SELECT Sarah.Text, Trim(Right(Left([Text],InStr([Text],"/")+5),8)) AS
Left_Most_Date,
Trim(Right(Left(Mid([Text],InStr([Text],"/")+8),InStr(Mid([Text],InStr([Text],"/")+8),"/")+5),8))
AS 2nd_From_Left_Date,
Trim(Right(Trim(Mid([Text],InStrRev([Text],"/")-5,8)),Len(Trim(Mid([Text],InStrRev([Text],"/")-5,8)))-InStr(Trim(Mid([Text],InStrRev([Text],"/")-5,8)),"/")+3)) AS Right_Most_Date
FROM Sarah;

The right-most will be the same as the 2nd from left if there is only two
dates.