Query on memo type field

[fat controller]

Hi all,

I have a table etc that has a field [History] which is a memo field. The data that goes into it is solely from pre-defined strings depending on which command/action is carried out on the associated form, and therefore the text being entered into the field is strictly controlled (field is also locked, so no user input is available).

Part of the string that goes in contains a date in dd/mm/yyyy format.

I would like to have a query that returns all records where the field [History] contains either a single date, or a date that is between two given dates as specified by the user at the time of running the query.

The bit that is getting me is the criteria - how do I get it to pick out the desired date from within the memo field?

 

[Ranman256]

You cannot query memo fields. Sorry.

 

[The_Doc_Man]

Don't take this as a harsh criticism, but it is indicative of a design flaw. There is an "Old Programmer's Rule" that you need to consider: Access won't tell you anything you didn't tell it first.

If you wanted to sometimes look at a date that could be associated to a record, you should have provided a date field for separate date storage. Using a MEMO field, you essentially have a "hidey hole" where you can store a whole bunch of assorted stuff that you can't ever get out again as anything other than a tangled rat's nest. That's why you can't query a memo field. It is essentially unstructured, but queries use SQL - STRUCTURED Query Language.

 

[fat controller]

Thanks both - and criticism is generally a good thing, as we would never improve without it.

I admit that I was trying to be extremely economical with the design of this particular part - I shall instead add a 'last updated date' field which can be simply updated along with the other code.

Cheers