473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Query Criteria for Memo Field

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. Please Help.
Access 2000
Vista
Thanks,
John
Feb 26 '08 #1
8 3292
Stewart Ross
2,545 Expert Mod 2GB
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
Expand|Select|Wrap|Line Numbers
  1. 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 ChargeExplanation 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
Feb 26 '08 #2
missinglinq
3,532 Expert 2GB
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)>
Feb 26 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
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
Feb 26 '08 #4
THANK YOU'LL!! I will try it first thing in the morning.
John
Feb 26 '08 #5
missinglinq
3,532 Expert 2GB
I don't mind, Stewart, but you'd better watch out for msquared! She's a Dubliner, you know!

;0)>
Feb 27 '08 #6
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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]
  3. 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
Feb 27 '08 #7
Stewart Ross
2,545 Expert Mod 2GB
...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
Feb 27 '08 #8
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


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
Feb 27 '08 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: GorDon | last post by:
Hi, I have a report based on a query. The query grabs a memo field from my main table, yet when I display the memo field in the report it truncates the memo field (the field needs to hold more...
6
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access...
1
by: LFM | last post by:
I have a table called "employee profile" which contains a bunch of user information. In that table I have a field called "Description" which is a text/memo field. This field is not mandetory to...
2
by: Bobbie | last post by:
1. Can you do a parameter query on a Memo field? 2. Can I set up a query to find all records that do not have a date in the field. As you can probably tell, I have tried but have not been...
2
by: jacoballen | last post by:
I have a query that combines the results of three related tables. The memo fields are truncated to 255 characters, but I need all the information in them. I'm aware that removing code such as...
1
by: Jeff | last post by:
A client showed me this one. In a totals query that counts items and links to a parent record, what should be the output from a memo field shows up as Chinese characters - I kid not. He has a...
2
by: Andy_Khosravi | last post by:
My management requested that I make my search engine more user friendly by "Making it work like Google's". They don't like that they have to enter a string exactly in the order it's in when...
3
by: seagullino | last post by:
Hello, I've developed my first Form, a simple affair that enables the user to search text in the memo fields in our database. When the "search" button is pressed, it runs a macro which runs the...
26
by: kpouya | last post by:
I am going to be as clear as possible about what i got right now and what i want to achieve Table1: Libary Title - memo Last Name - memo First Name - memo Company - memo Year - number Type -...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.