Date comparison...date now to date in a text field

[Guest]

I have a table that is created from an imported call log. The call log puts
the date and text into one field...not separately.

For example the resulting field in my new table after an import is "10/12/05
sales meeting went good."

I want to compare todays actual date Date() to the date in the text field.

What's the best way to do this since the date "10/25/05" is sitting in a
text field...not a date field?

 

[tina]

if the date is always in the same format - example: always mm/dd/yy, rather
than sometimes mm/dd/yy and sometimes mm/dd/yyyy - and is always at the
beginning of the field, then you can extract the date value from the field
using

Left(FieldName, 8)

to make Access "see" it as a date, rather than a string, so it can be
compared to the current date, add the following

CDate(Left(FieldName, 8))

hth

 

[Guest]

thanks...that was exactly what I was looking for and it happens to be always
fixed at mm/dd/yy

thanks again for the quick response

 

[Rick Brandt]

thanks...that was exactly what I was looking for and it happens to be
always fixed at mm/dd/yy

thanks again for the quick response

Be aware though that CDate() will interpret the format of your date string based
on the regional settings in Windows. That means that on one user's PC it could
assume mm/dd/yy and on another's it might be dd/mm/yy. This would not be a
problem for any value where the day is a higher numeric value than 12, but on
those where the day is 12 or less you could have inconsistencies.

If that is a concern you could use DateSerial instead...

=DateSerial(Mid(FieldName, 7,2), Left(FieldName, 2), Mid(FieldName, 4,2))

 

[tina]

you're welcome

thanks...that was exactly what I was looking for and it happens to be always
fixed at mm/dd/yy

thanks again for the quick response