By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Query Calculation Field Criteria Not Followed

klarae99
P: 85
In Access 2003 I am creating an Inventory Database from scratch. There are two tables (tblProductInformation and tblSubProducts) that I am trying to use in a queary to determine the number of Items in Stock. The SubProducts table is linked to the Product Information table by Item # (and the information is added and viewed as a form Product Information with a Sub Form Sub Products)

I have a form (frmInventoryLocation) where a location is selected for the Queary, the queary also has criteria to return all locations if no location is choosen.

There is a calculation field in the queary that I am using to calculate the number in stock by subtracting the # Sold from the # recieved (both fields in the SubProducts table). This calculation works fine. However, I want to limit my list to those enteries that have inventory in stock (AKA In Stock > 0) I have tried adding the >0 criteria to the calculated field but the queary still returns all results, even those with no stock.

I have managed to get around this by creating a second queary (QryInventoryLocation) that has all the fields from the first queary (QryInventory) and the Criteria of >0 for the In Stock field. This works but requires that I have two quearys to do something that I think I should be able to accomplish in one. Does anyone have any ideas to fix my first queary to 'see' the >0 criteria?
Oct 1 '07 #1
Share this Question
Share on Google+
23 Replies


Scott Price
Expert 100+
P: 1,384
How about pasting in the SQL for your query? Please wrap it in the code tags by selecting the text and then clicking the # button on the top of this reply window. You can also manually edit the first tag to look just like this: [code=sql]

Regards,
Scott
Oct 1 '07 #2

klarae99
P: 85
Scott,

Thanks for your reply to this question...below I have copied and pasted the SQL view of my queary and I have used the # symbol to format the code as you requested. Please let me know if you need anything else to evaluate this issue. Thanks again for your help!!


Expand|Select|Wrap|Line Numbers
  1. SELECT tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.[Item#], tblProductInformation.Description, tblProductInformation.Category, tblProductInformation.SubCategory, tblProductInformation.Discontinued, Sum(tblSubformProducts.[# Recieved]) AS [SumOf# Recieved], Sum(tblSubformProducts.[# Sold]) AS [SumOf# Sold], Sum(tblSubformProducts![# Recieved]-tblSubformProducts![# Sold]) AS [On Hand]
  2. FROM tblProductInformation INNER JOIN tblSubformProducts ON (tblProductInformation.[Item#] = tblSubformProducts.[Item #]) AND (tblProductInformation.[Item#] = tblSubformProducts.[Item #]) AND (tblProductInformation.[Item#] = tblSubformProducts.[Item #])
  3. GROUP BY tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.[Item#], tblProductInformation.Description, tblProductInformation.Category, tblProductInformation.SubCategory, tblProductInformation.Discontinued
  4. HAVING (((tblSubformProducts.EventLocation)=[Forms]![FrmInventoryLocation]![Text37]) AND ((Sum([tblSubformProducts]![# Recieved]-[tblSubformProducts]![# Sold]))>0)) OR ((([Forms]![FrmInventoryLocation]![Text37]) Is Null))
  5. ORDER BY tblSubformProducts.EventLocation;
Oct 1 '07 #3

Scott Price
Expert 100+
P: 1,384
Scott,

Thanks for your reply to this question...below I have copied and pasted the SQL view of my queary and I have used the # symbol to format the code as you requested. Please let me know if you need anything else to evaluate this issue. Thanks again for your help!!


Expand|Select|Wrap|Line Numbers
  1. SELECT tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.[Item#], tblProductInformation.Description, tblProductInformation.Category, tblProductInformation.SubCategory, tblProductInformation.Discontinued, Sum(tblSubformProducts.[# Recieved]) AS [SumOf# Recieved], Sum(tblSubformProducts.[# Sold]) AS [SumOf# Sold], Sum(tblSubformProducts![# Recieved]-tblSubformProducts![# Sold]) AS [On Hand]
  2. FROM tblProductInformation INNER JOIN tblSubformProducts ON (tblProductInformation.[Item#] = tblSubformProducts.[Item #])
  3. GROUP BY tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.[Item#], tblProductInformation.Description, tblProductInformation.Category, tblProductInformation.SubCategory, tblProductInformation.Discontinued
  4. HAVING (((tblSubformProducts.EventLocation)=[Forms]![FrmInventoryLocation]![Text37]) AND ((Sum([tblSubformProducts]![# Recieved]-[tblSubformProducts]![# Sold]))>0)) OR ((([Forms]![FrmInventoryLocation]![Text37]) Is Null))
  5. ORDER BY tblSubformProducts.EventLocation;
For your calculated field, place it within an IIf statement: i.e. iif([tblSubformProducts]![# Recieved] => [tblSubformProducts]![# Sold], Sum([tblSubformProducts]![# Recieved] - [tblSubformProducts]![# Sold], "")

Regards,
Scott
Oct 1 '07 #4

klarae99
P: 85
Scott,

I went back to my queary and inserted the text at the point I thought it should go and when I tried to save the changes I got the following error message:

Sintax error (missing operator) in queary expression '(((tblSubformProducts.EventLocation)=[Forms][FrmInventoryLocation]![text37]) AND (iif([tblSubformProducts]![#Recieved]=>[tblSubformProducts]![#Sold], SUM ([tblSubformProducts]![#Recieved]-[tblSubformProducts![#Sold], "") Or ((([Forms]!FrmInventoryLoca'.

When I click OK it returns me to the code and the > symbol is highlighted.

I have very little knowledge of how SQL works and appreciate your continuing help with this issue.
Oct 1 '07 #5

Scott Price
Expert 100+
P: 1,384
Scott,

I went back to my queary and inserted the text at the point I thought it should go and when I tried to save the changes I got the following error message:

Sintax error (missing operator) in queary expression '(((tblSubformProducts.EventLocation)=[Forms][FrmInventoryLocation]![text37]) AND (iif([tblSubformProducts]![#Recieved]=>[tblSubformProducts]![#Sold], SUM ([tblSubformProducts]![#Recieved]-[tblSubformProducts![#Sold], "") Or ((([Forms]!FrmInventoryLoca'.

When I click OK it returns me to the code and the > symbol is highlighted.

I have very little knowledge of how SQL works and appreciate your continuing help with this issue.
The SQL should replace the calculated field, not be used as a WHERE criteria. In other words, if you go into the query design view and look at the Fields grid, add one that looks like this:
Expand|Select|Wrap|Line Numbers
  1. In Stock: IIf([tblSubformProducts]![#Recieved]... etc etc.
(obviously you'll need to retype the etc etc part :-)

Regards,
Scott
Oct 1 '07 #6

klarae99
P: 85
OK, I am now working in the right area anyway, I have removed my previous calculation field and have replaced it with:

In Stock: IIf([tblSubformProducts]![#Recieved] => [tblSubformProducts]![#Sold], Sum([tblSubformProducts]![#Recieved]-[tblSubformProducts]![#Sold], "")

When I try to run the queary there is an error message:

The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier.

When I click OK the > symbol is highlighted.

I really appreciate your continued help!
Oct 1 '07 #7

Scott Price
Expert 100+
P: 1,384
My most humble apologies! I mistyped the greater-than-or-equal-to comparison operator ;-(

It is actually >=.

Regards,
Scott
Oct 1 '07 #8

klarae99
P: 85
Scott,

Thank you for your continued patients with me. I have made the change from => to >= in my queary and now I get a new error message.

You tried to execute a queary that does not include the specified expression 'IIf(that line of text we've been working with)Sold], "")' as part of an aggregate function.

When I click on OK there is a curser in the table field of my grid under the calculation expression. Again, I am at a loss as to how to correct this error.

On a side note, your last post commented on the greater-than-or-equal-to comparison. If this translates the same in access as I remember it translating in math than the # recieved can be the same as the # sold, and when we subtract them from each other we would get 0 in stock. I am trying to eliminate the locations with 0 in stock from the queary, so is this the function that we want?

Again, thank you for your continued help with this issue. I really appreciate your assistance.
Oct 2 '07 #9

Scott Price
Expert 100+
P: 1,384
Not a problem... If you wish to eliminate the 0 results, then you can just change the >= to > (greater than). Putting your IIf() statement inside the Sum() function in that same field should resolve the next error message (instead of having the calculation performed inside the IIf). In Stock: Sum(IIf([tblSubformProducts]![#Recieved] > [tblSubformProducts]![#Sold], [tblSubformProducts]![#Recieved]-[tblSubformProducts]![#Sold], ""))

Regards,
Scott
Oct 2 '07 #10

klarae99
P: 85
Hello Scott,

This is not going away easily.

I moved the iff clause into the sum clause like you suggested and am now getting a different set of messages when I click run. First I am getting two dialoge boxes, the first asking for tblSubformProducts!#Recieved and the second asking for #Sold. (I also get one asking for location but I know that is because I am running in without the form for input.) If I leave the text boxes blank I get the following error message:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

If I enter 1 for the #Recieved and 0 for the #Sold The queary will run but it does not give me the correct In Stock amount, and it also does not give me an answer of 1.

Could this error be because I am using the groupby Sum feature on the # Recieved and #Sold? My reasoning for doing this was to get only one listing per item per location but if it is causing things to be more complicated than they should be maybe there is a different way I could obtain the same result?

Sorry to be such a pain, I had thought that this would be an easier fix. I appreciate all the time you have dedicated to my question.
Oct 2 '07 #11

Scott Price
Expert 100+
P: 1,384
Again, not a problem! Happy to help out...

How about pasting in the sql you have now; then I'll set this up in my test database and get back to you.

Regards,
Scott
Oct 2 '07 #12

Scott Price
Expert 100+
P: 1,384
Ok, I put this into my test database and came up with this SQL. This is only using one table: tblSubformProducts. It returns one value for each productID and eliminates products that have a balance of 0 in stock...

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW tblSubformProducts.ProductID, Sum([tblSubformProducts].[#Recieved]-[tblSubFormProducts].[#Sold]) AS [In Stock]
  2. FROM tblSubformProducts
  3. GROUP BY tblSubformProducts.ProductID
  4. HAVING (((Sum([tblSubformProducts].[#Recieved]-[tblSubFormProducts].[#Sold]))<>0));
  5.  
It could also be useful to you turning this around, to find out which product is sold out, eh? To do that just change the <>0 to =0...

Regards,
Scott
Oct 2 '07 #13

klarae99
P: 85
Scott,

Here is the current version of my SQL code.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.[Item#], tblProductInformation.Description, tblProductInformation.Category, tblProductInformation.SubCategory, tblProductInformation.Discontinued, Sum(tblSubformProducts.[# Recieved]) AS [SumOf# Recieved], Sum(tblSubformProducts.[# Sold]) AS [SumOf# Sold], Sum(IIf(tblSubformProducts![#Recieved]>tblSubformProducts![#Sold],tblSubformProducts![#Recieved]-tblSubformProducts![#Sold],"")) AS [In Stock]
  2. FROM tblProductInformation INNER JOIN tblSubformProducts ON (tblProductInformation.[Item#] = tblSubformProducts.[Item #]) AND (tblProductInformation.[Item#] = tblSubformProducts.[Item #]) AND (tblProductInformation.[Item#] = tblSubformProducts.[Item #])
  3. GROUP BY tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.[Item#], tblProductInformation.Description, tblProductInformation.Category, tblProductInformation.SubCategory, tblProductInformation.Discontinued
  4. HAVING (((tblSubformProducts.EventLocation)=[Forms]![FrmInventoryLocation]![Text37])) OR ((([Forms]![FrmInventoryLocation]![Text37]) Is Null))
  5. ORDER BY tblSubformProducts.EventLocation;
I hope this helps!

I tried to use the code you posted in the last post but it prompted me for #Recieved,#Sold,Etc. even after I opened it in design view and checked that it was linked to my correct table. If you have any questions or need any other information let me know.
Oct 2 '07 #14

Scott Price
Expert 100+
P: 1,384
Recheck the spelling carefully, I notice you have it # Recieved, and # Sold once, then the second time is without the space. (By the way... Recieved is spelled ei, as in i before e except after c... Not a real problem, just as long as it's spelled consistently throughout your references :-)

Regards,
Scott
Oct 2 '07 #15

klarae99
P: 85
Scott,

You were correct in evaluating my space issue (I thought I had removed all the spaces from my field names in the tables but I had not.) Once I put the spaces into the expression the two diologe boxes asking for # Recieved and # Sold stopped popping up but I still got the Error message:

This expression is incorrectly typed, or it is to complex to be evaluated, Etc, etc.

Who thought something so simple could cause so many problems?

Thanks for your continuing efforts.
Oct 4 '07 #16

Scott Price
Expert 100+
P: 1,384
Try copying and pasting this sql directly into a new query:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW tblProductInformation.[Item#], Sum(tblSubformProducts.[#Recieved]-tblSubFormProducts.[#Sold]) AS [In Stock], tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.Discontinued, tblProductInformation.Category, tblProductInformation.Subcategory, tblProductInformation.Description
  2. FROM tblProductInformation INNER JOIN tblSubformProducts ON tblProductInformation.[Item#] = tblSubformProducts.[Item#]
  3. GROUP BY tblProductInformation.[Item#], tblSubformProducts.EventLocation, tblProductInformation.VendorCode, tblProductInformation.Discontinued, tblProductInformation.Category, tblProductInformation.Subcategory, tblProductInformation.Description
  4. HAVING (((Sum([tblSubformProducts].[#Recieved]-[tblSubFormProducts].[#Sold]))<>0)) AND (((tblSubformProducts.EventLocation)=[Forms]![FrmInventoryLocation]![Text37]));
  5.  
I set up the two tables in my test database, wrote this query up, and it gives no errors.

I did notice in looking more closely at your last posted sql that you have several duplications, as well as an unnecessary where criteria. Give this one a try and see if it's what you're after.

Regards,
Scott

For the copy and paste, you'll have to paste it into Notepad first to strip out the line numbers.
Oct 4 '07 #17

klarae99
P: 85
Scott,

I copied the new code into a new queary and did not get any error messages (yeah!), however the number in stock is not seperated by the Items, all the items list the same amount in stock (and I think it is the total # of items per each location) but I need to have the stock per item per location. I hope that makes sense to you. Your additional help is appreciated!
Oct 4 '07 #18

Scott Price
Expert 100+
P: 1,384
Hi Klara,

I'm sorry, but I can't seem to reproduce that problem in my test db :-( When I populate with test data, using the sql I posted, it does what I think you want done!

I.e. I put three items into the tblProductInformation, all with the same vendorID, with varying category, subcategory and descriptions.

Then in the tblSubFormProducts I posted a bunch of numbers for these items (at least two of each #Recieved and #Sold). Running the query results in two (I made sure the third of my test items would sum to 0, to 'test' the criteria) items being summed and grouped together accurately.

I'm at a loss right now to know what to tell you!

Regards,
Scott
Oct 4 '07 #19

klarae99
P: 85
Scott,

Thanks for all your help with this, its good to know that its not just me that runs into a wall.

I also wanted to let you know that I have used scripts to solve many of my other issues with access by reading other posts that are similar to what I need done. Many of those posts were responded to by you and your advice there helped me progress to this point.

Thanks again for all your help, I'll update this post if I do figure out what was going on, or if I determine another way to achive my goal. I'm sure that I'll come up with more questions and I'll see you back here!
Oct 5 '07 #20

Scott Price
Expert 100+
P: 1,384
Sorry we couldn't get this one solved, Klara.

Thanks for posting back, and good luck on the rest of your app!

Regards,
Scott
Oct 5 '07 #21

klarae99
P: 85
Just a quick up-date on this issue.

I ended up having to modify my table structures for this project so I elected to import the tables with the new structures into a new database and recreate all of my quearys, forms and reports using the new tables instead of trying to fix all my references in the existing database.

When I did this I was able to create the query discussed here, putting the criteria >0 in the In Stock field. The query works fine and I did not get the same issue that occurred before.

I do not know why it didn't work the first time. The only thing I can figure is that the first time I created the query I was not 100% sure of what fields I needed in the queary and how I wanted them manipulated so I entered and cut fields several times and I also edited the criteria using the build feature many times so perhaps I created some sort of reference problem as a result (Scott had mentioned that I had repeats of information in my code). However it happened the new query works fine and I do not have the issue anymore.

I still really appreciate the time dedicated to trying to solve this problem, I learned also that I will hopefully be able to apply in the future to other situations.
Oct 18 '07 #22

Scott Price
Expert 100+
P: 1,384
I'm very glad that you got it working after all!

Like you found, it's quite possible to confuse the little Access gnomes that do all the calculations :-) That's quite an undertaking to re-create your database, but as you found, it can fix a number of issues.

Thanks for posting back with what worked.

Regards,
Scott
Oct 19 '07 #23

klarae99
P: 85
I was working on a different query today and ran into the exact same situation that had plaged me with this original query. I figured out a solution that probably would have worked for me here.

I was working on a query to return the number of items sold within a specific date. I wanted to be able to select a location from an option group on a form and have the query return all results if the option group was left blank. I had designed my queary to select all items with the Sold >0 and between two dates, it worked fine.

Then I added my criteria for the location, (in design view I used the Criteria line to reference the form selection and I used the Or line to include Like Is Null). The query then returned the results I wanted when I selected a location but returned all results (even those with 0 sold) when no location was selected.

My solution was to put the >0 criteria for the number sold into both the Criteria and the Or lines of the design grid and it now works great.
Oct 19 '07 #24

Post your reply

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