473,289 Members | 2,040 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,289 software developers and data experts.

Query Calculation Field Criteria Not Followed

klarae99
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
23 2873
Scott Price
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
My most humble apologies! I mistyped the greater-than-or-equal-to comparison operator ;-(

It is actually >=.

Regards,
Scott
Oct 1 '07 #8
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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

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

Similar topics

6
by: paii | last post by:
I have a table that stores job milestone dates. The 2 milestones I am interested in are "Ship Date" TypeID 1 and "Revised Ship Date" TypeID 18. All jobs have TypeID 1 only some jobs have TypeID 18....
3
by: Steve | last post by:
Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's criteria is set ats: Forms!FrmRestock!LastXDays. LastXDays on the form is a combobox where the selections are 30, 60...
3
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
8
by: Tony Williams | last post by:
I have a database that hold s figures for each quarter. I want to create a query whereby the user can ask for the running total of the data upto the quarter they enter into a prompt. The database...
5
by: SuffrinMick | last post by:
Hello - I'm a newbie to coding! I'm working on an access 2000 database which has three tables: tblContacts - A list of customer contacts. tblOrgTypes - A list of organisational types....
14
by: Crimsonwingz | last post by:
Need to calculate a sum based on a number of factors over a period of years. I can use formula ^x for some of it, but need totals to carry over in the sum and have only been able to do this thus...
1
by: Ivan Carey | last post by:
How can a query display multiple fields with diferent condition on the same field example I have a field name of reason and a field name of duration. I would like to display 2 fileds of total...
3
by: martlaco1 | last post by:
Trying to fix a query that (I thought) had worked once upon a time, and I keep getting a Data Type Mismatch error whenever I enter any criteria for an expression using a Mid function. Without the...
22
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
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: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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)...

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.