how cn we say it in query

[Frank Situmorang]

Hello,

In my supplier invoice table I have a " memo" field. In order to show an
accurate outstanding balance at the end of the year, I want to exclude if the
word " Cancel" in the memo feld.

I apprecaite your helping me how can I say it in a query for this memo field.

Thanks in advance

 

[RonaldoOneNil]

In the criteria row of the memo field in your query put

Not Like "*cancel*"

 

[Ron2006]

I believe that there is a limitation in that search.

Access gurus please correct me if I am wrong.

If the word appears farther into that field than 256 characters, the
selection logic may not operate.

I would strongly suggest that you create a new field in your table for
cancelled logic. (A cancelled date. - if it is null then the order/
record is NOT cancelled. if the field is not null than it is
cancelled.)

Also, if the word "cancellation" is in the memo field then that record
will be considered cancelled. No easy solution for all the
possibilities.

Ron

 

[John Spencer]

I believe you are wrong. The entire memo field will be checked for the
existence of the value.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Dale Fye]

Although I agree with John that the entire memo field will be checked, I
concur with Ron regarding adding some sort of Status field. I generally
include Status and StatusDate fields in this type of application, so I can
change the status of the invoice to Billed, Paid, Cancelled, ...

I don't like trying to "read" (for one, it takes longer than checking a
single numeric Status field) or store what I would consider "data" (I know,
it's all data) in a memo
field.

You might even want to consider another table with the InvoiceStatus, so you
can track Invoice mailings, payments, cancellations in a one-to-many
relationship.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Ron2006]

Thank you for the clarification on the compare logic for memos.

I have had problems every once in a while with applications I have
inherited that have memo fields. And I thought that was one of them
but was not sure.

I will add the answer to my storehouse of Access trivia.

Thank you again.

Ron

 

[Frank Situmorang]

What I meant is where ever it is in the memo field, it should be excluded. It
could be cancel... or Cancelled, should be excluded. It could be the clerk
type cancelled inv# 1234 and returned.... something like that.

Thanks for more explanation

 

[John W. Vinson]

If the word appears farther into that field than 256 characters, the
selection logic may not operate.

You are mistaken. The search will not be efficient and will not use an index,
but a LIKE clause will find the text anywhere in the memo field.

 

[John W. Vinson]

What I meant is where ever it is in the memo field, it should be excluded. It
could be cancel... or Cancelled, should be excluded. It could be the clerk
type cancelled inv# 1234 and returned.... something like that.

As noted elsewhere in the thread... *this is really a bad idea*.

Although a criterion of

LIKE "*cancel*"

will in fact work, and will find the word cancel anywhere in the memo field
(even if that field contains half a million words, as it could), it's very
inefficient, and it runs the risk of "false hits". You're talking about a
worldwide database - what if the memo field should happen to contain a phrase
like

.... the mission trip had to be cancelled because of...

or

.... we're excited about the new church planned for Viscanceli village...

A memo field is simply THE WRONG PLACE for this kind of information.