"The date is always in the first line in the memo field"
Choose One of the following:
-- Is the date the ONLY thing in the first line?
-- Is the date at the start of the first line and always separated by a
space from the rest of the data in the first line?
-- Is the date somewhere in the first line?
If you chose the second or third option, is the date always in the same
format? If so what is that format?
Here are some of the formats that a date could be in.
Dec 3 2008
3 Dec 2008
2008/12/03
12/03/2008
12/3/2008
December 3, 2008
The first option is the simplest to handle. You can use an expression
like the following to get the date string and convert it to a date.
IIF(IsDate(Left(MemoField,Instr(1,MemoField & Chr(13) & Chr(10),Chr(13)
& Chr(10))-2),DateValue(Left(MemoField,Instr(1,MemoField & Chr(13) &
Chr(10),Chr(13) & Chr(10))-2),Null)
Once you have done that in a column in the query, you can apply criteria
against the calculated field.
=Date()-1
Another option is to use some criteria like the following against the
memo field. Assuming that the date field is somewhere in the memofield
and consists of numbers separated by slashes and is in month, day, year
format then the following should work for you.
Where Memofield Like Format(Date()-1,"\*mm/dd/yy\*")
OR Memofield Like Format(Date()-1,"\*m/d/yy\*")
OR Memofield Like Format(Date()-1,"\*mm/dd/yyyy\*")
OR Memofield Like Format(Date()-1,"\*m/d/yyyy\*")
If the date is somewhere in the memofield then add "\*" to the beginning
of the format string.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================