Query Criteria for Memo Field

**Stewart Ross** · Feb 26 '08, 07:36 PM

Originally posted by John Torres

I have created a report base on a query. And on Query or report I want to remove all entry with “Set-Up Charge” under Comment field (Memo). I tried < >”Set-Up Charge” but it shows all the comments with the “Set-Up Charge”. Is there any other way to do it? I have 2 other criterias on two different fields (type the customer name) (Balance >0). Everything works except for the Memo Field....

Hi John. In the Access query grid are your three criteria all on the same criteria row of the grid? I ask because if they are not they will be ORd together (finding all records matching the first criteria OR the second OR the third) and you will not exclude all those which contain the words "Set-Up Charge". If you switch to the SQL view of your query you will know for sure, as the WHERE section should list the three conditions with ANDs between them.

If the three conditions are correctly ANDed the other possibility is that the string "Set-Up Charge" has been entered with additional spaces in places. It is not a good idea to rely on precise entry of free text phrases, because people will frequently mis-type them. As our expert contributor MissingLinq will tell you, you should never use a memo field for anything you subsequently have to process (and I am sure she will tell you so herself!).

If you do need to test for this value I would use a more general criterion than yours which is less likely to suffer from mis-typed phrases, such as

Code:

not like "*Set*"

which if ANDed correctly with your other two criteria will exclude all rows with the word Set in the memo field as well.

Anyway, as Linq would say, don't use a memo field to store this kind of value - it is not good practice at all. If you need such a value, why not include an ChargeExplanati on field that could be set to discrete values which the user picks from a combo box, say, instead of entering in a memo field?

-Stewart

**missinglinq** · Feb 26 '08, 08:24 PM

Stewart knows me too well; he just missed the fact that I have a Y chromosome!

The only problem I see with any of Stewart's comments is that if Set-Up Charge doesn't appear in the comments field, but the word set does, those records will be excluded as well. You might tell us a little more about this Comments field and its typical data.

His comments about not basing a criteria on an exact user entered phrase is dead on. Typos are a fact of life! Set-Up Charge and Setup Charge and Set Up Charge are all likely to be used. This probably would have been better handled with a checkbox to be checked if the condition Set-Up Charge was true.

Welcome to TheScripts!

Linq ;0)>

**Stewart Ross** · Feb 26 '08, 09:52 PM

Originally posted by missinglinq

Stewart knows me too well; he just missed the fact that I have a Y chromosome!

Very sorry, Linq!! I confused you with msquared... How embarassing... it's the clunky newbie in me still finding my way here and dropping myself in it from time to time.

Apologies

Stewart

**John Torres** · Feb 26 '08, 11:01 PM

THANK YOU'LL!! I will try it first thing in the morning.
John

**missinglinq** · Feb 27 '08, 12:24 AM

I don't mind, Stewart, but you'd better watch out for msquared! She's a Dubliner, you know!

;0)>

**John Torres** · Feb 27 '08, 01:25 PM

Good Morning and Thanks for the reply. I hate to change the format for it's populated with a lot of data already and lesson learned for my next project.

The comment section (Memo field) is used for a particular line i.e. "Ship ASAP, HOT, and charge like "Set-Up Charge" I want to leave all the comments except the "Set-Up Charge". All in the same row. And I agree that "Set-Up Charge" can be type different ways. They don't need this particular line to be seen for it's irrelevant for them and get them confuse. I tried the
Code Text:
Not Like "*Set*"
But it came up with nothing when I preview the Report or query. Here's the SQL view of the report:

Code:

SELECT [Purchase Order Entry].Customer, [Purchase Order Entry].[Part Number], [Part Number Log].Description, [Purchase Order Entry].Rev, [Purchase Order Entry].[Purchase Order], [Purchase Order Entry].[Date Received], [Inventory Transactions].[Due Date], [Inventory Transactions].[Qty Ordered], [Inventory Transactions].[Qty Shipped], [Qty Ordered]-[Qty Shipped] AS Balance, [Purchase Order Entry].[Special Instruction], [Inventory Transactions].Comment
FROM ([Part Number Log] RIGHT JOIN [Purchase Order Entry] ON [Part Number Log].[Part Number] = [Purchase Order Entry].[Part Number]) RIGHT JOIN [Inventory Transactions] ON [Purchase Order Entry].[Purchase Order ID] = [Inventory Transactions].[Purchase Order ID]
WHERE ((([Purchase Order Entry].Customer)=[Type  Customer's Name]) AND (([Purchase Order Entry].[Part Number])<>"FAI") AND (([Qty Ordered]-[Qty Shipped])>0) AND (([Inventory Transactions].Comment) Not Like "*Set*"));

I hope that you guys can get me a simple solution. I'm very NEW and still learning.
THANKS IN ADVANCE!!!
John

**Stewart Ross** · Feb 27 '08, 01:59 PM

Originally posted by JohnTorres

...But it came up with nothing when I preview the Report or query. Here's the SQL view of the report...

Do you mean that when you ran the SQL query it showed no lines at all - just the column headings at the top? If this is so then there are no rows that satisfy the ANDed conditions in your query.

Two tests: first, remove the 'not like...' criterion altogether and rerun the query. What do you get then? Second, put back the 'not like...' criterion but take out the word not and test what happens. This will change the condition from excluding anything with the word Set in it to including such rows instead. The result of these tests will help me understand what is going on with your query.

I note that your query uses a lot of right-joins between tables, and I wouldn't normally expect these where purchase orders and inventories are involved. I suspect there are issues with the design of your tables, but let's see if we can resolve your query issue for now.

-Stewart

**John Torres** · Feb 27 '08, 05:19 PM

Thanks again!

1st Test: I removed the 'not like...' criterion altogether and rerun the query and It shows a specific customer when I entered a customer’s name per criteria [Type Customer’s Name]- with balance greater than zero (per criteria) including the one with comments. All data are correct.

2nd Test: Like “*Set*” – It shows all records with the word Set* only (i.e., Set-Up Charge, Set Up Charge).

I removed the “<>FAI” (I don’t need it for this report) criterion but still have the Balance “>0” criterion.

It concerns me about your comment regarding the right join between tables relationship. So far it’s working as I expected. I just don’t know what kind of effect that may happen between now and then. I’m working on a dummy copy so I won’t mess up the working copy.

Very much appreciated your comments and help.
John

Originally posted by Stewart Ross Inverness

Do you mean that when you ran the SQL query it showed no lines at all - just the column headings at the top? If this is so then there are no rows that satisfy the ANDed conditions in your query.

Two tests: first, remove the 'not like...' criterion altogether and rerun the query. What do you get then? Second, put back the 'not like...' criterion but take out the word not and test what happens. This will change the condition from excluding anything with the word Set in it to including such rows instead. The result of these tests will help me understand what is going on with your query.

I note that your query uses a lot of right-joins between tables, and I wouldn't normally expect these where purchase orders and inventories are involved. I suspect there are issues with the design of your tables, but let's see if we can resolve your query issue for now.

-Stewart