carriage return disappearing from query criteria expression

[brendanchris]

Hi there

I have a problem that keeps occurring in a select query. Basically I have used the expression builder to set the criteria of a memo field in the query to be as follows:

Not Like "*\fs## ?*
\par }*"

It is an rtf coded field so it is important that I have the carriage return in the criteria. The works well when the return is there, but obviously does not return what I am after when the return is not in the criteria.

The carriage return seems to disappear whenever you go back into the mdb and alter some other query (not necessarily the one that contains this criteria).

It turns into the following:
Not Like "*\fs## ?* \par }*"

Is there anyway to stop this happening, or alternatively, write the crtieria is some other way to force the carriage return to stay in the "not like" criteria?

Thanks in advance

BC

 

[dhookom]

You could try something like:
Not Like "*\fs## ?*" & Chr(13) & Chr(10) & "\par }*"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[brendanchris]

Thanks for the reply dhookom, however that criteria didn't work. It returned all fields. Any other ideas?

Here is an example of a memo field that I am trying to exclude from the query:

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fswiss Verdana;}}
\viewkind4\uc1\pard\f0\fs17 Still wine.
\par }

Basically the words "still wine" could be anything, as could be the rtf codes before the text. The consistent component is the "fs" code followed by a space and some type of text and then a new line with the \par }" bit.I am therefore trying to only return those fields where there is no text (in the place of still wine).

As stated in the earlier post, the criteria:
Not Like "*\fs## ?*
\par }*"

returns what I am after, however need to stop it losing the carriage return. Any other suggestions on ways I might return what I am after?

Thanks

BC

 

[dhookom]

If you can't change the criteria, change the field to remove all the carriage returns/line feeds or whatever. Your where clause might look like:
WHERE Replace(Replace([YourField], Chr(13),""),Chr(10),"") Not Like "....."



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[brendanchris]

I may have found another work around that seems to be working well for now. I basically created a table with a memo field in it that contains the criteria statement and put a: not like [Tblrtfsupp]![testrtf] in the criteria field. Returns the correct data and hasn't seemed to have lost the formatting at all within the table.

Thanks dhookom for your help.

BC