Dates in a memo field

  • Thread starter Thread starter sleeplessinannapolis
  • Start date Start date
S

sleeplessinannapolis

I have a table containing a memo field. The memo field is populated by
importing an excel sheet into the table. The first line in the memo contains
a date. How can I build a query that shows me only those records where this
memo field begins with yesterday'd date?
 
I have a table containing a memo field. The memo field is populated by
importing an excel sheet into the table. The first line in the memo contains
a date. How can I build a query that shows me only those records where this
memo field begins with yesterday'd date?
Click to expand...

While you know the value as a date, as far as Access is concerned, if
it is in the Memo field it is text.
Does the date always appear at the very beginning of the field?
Is at always in mm/dd/yyyy format, i.e. 03/05/2009?
As criteria on the Memo field, write...
Left([MemoFieldName],10) Like Format(Date()-1,"mm/dd/yyyy") & "*"

If the date can be anywhere in the first line, then use:
InStr([MemoFieldName],Format(Date()-1,"mm/dd/yyyy")) > "0" And
InStr([MemoFieldName],Format(Date()-1,"mm/dd/yyyy"))<InStr([MemoFieldName],Chr(10))

The above should be all on one line.
 
"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
'====================================================
 
Thanks all..I took the suggestions and made it work. Actually it was pretty
easy because the date I'm looking for is in the same format at the same
location within the memo field...Thanks again
 
Back
Top