Solved Query not showing ANY records when criteria set to only show >0? (1 Viewer)

ChrisC

Registered User.
Local time
Today, 09:05
Joined
Aug 13, 2019
Messages
90
Just when I think im finished, something else utterly confounds me!

I have 2 queries; one of which shows a list of all parts, the opening stock, how many have been issued, how many have been receipted and finally the current balance. In this query I am happy for the Current Balance to show zero's.

here is the SQL:
SELECT Inventory.WHLocatID, Inventory.PartID, Inventory.OpeningQty, Nz([qryTransLogTotal].[TotalReceipt],0) AS TotalReceipt, Nz([qryTransLogTotal].[TotalIssuance],0) AS TotalIssuance, [openingqty]+[totalReceipt]-[TotalIssuance] AS CurrentBalance
FROM Inventory LEFT JOIN qryTransLogTotal ON (Inventory.WHLocatID = qryTransLogTotal.Origin) AND (Inventory.PartID = qryTransLogTotal.PartID)
ORDER BY Inventory.PartID;

This works perfectly!

The second query is essentially the same BUT I do not want to display any records where the CurrentBalance is showing as zero. However when I run it, NO records are displayed at all and I really can't see why!

SQL:
SELECT Inventory.WHLocatID, Inventory.PartID, Inventory.OpeningQty, Nz([qryTransLogTotal]![TotalReceipt],0) AS TotalReceipt, Nz([qryTransLogTotal]![TotalIssuance],0) AS TotalIssuance, [openingqty]+[totalReceipt]-[TotalIssuance] AS CurrentBalance
FROM Inventory LEFT JOIN qryTransLogTotal ON (Inventory.WHLocatID = qryTransLogTotal.[Origin]) AND (Inventory.PartID = qryTransLogTotal.PartID)
WHERE ((([openingqty]+[totalReceipt]-[TotalIssuance])>0));

As you can see, apart from the ORDER BY function in the first query, and of course the WHERE function in the 2nd, everything is the same. If I remove the >0 then the query behaves exactly like the first.

1599039942952.png


my brain hurts!

many thanks as always

Chris
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:05
Joined
May 7, 2009
Messages
19,170
can you use the Calculated expression in substitute for its "Name":

CurrentBalance: Nz(OpeningQty, 0) + Nz(qryTansLogTotal.TotalReceipt, 0) - Nz(qryTransLogTotal.TotalIssuance, 0)
 

ChrisC

Registered User.
Local time
Today, 09:05
Joined
Aug 13, 2019
Messages
90
Hi Arnelgp,

still returns the same results im afraid!

thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:05
Joined
Sep 21, 2011
Messages
14,050
Put the calculated formula into another field with no criteria and see what that produces. Then add the criteria to that field in the design view.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:05
Joined
Feb 19, 2013
Messages
16,553
not sure it is the reason for the problem but there is another difference - in your first query you are using [qryTransLogTotal].[TotalReceipt], in the second you are using [qryTransLogTotal]![TotalReceipt] (dot and bang). The accepted grammar is to use the . when referring to fields, you use the ! when referring to objects such as forms.

You are also naming your aliased calculations, the same as the field name - so the query engine doesn't know which one to use in the criteria

e.g.Nz([qryTransLogTotal].[TotalReceipt],0) AS TotalReceipt

suggest rename to TtlReceipt or TotalReceipts - and for currentbalance calc, refer to the actual field, not the alias. However you do not need to use the nz function for the receipts and issuances, only in the currentbalance calc
 

ChrisC

Registered User.
Local time
Today, 09:05
Joined
Aug 13, 2019
Messages
90
That works Gasman - thank you.

The only thing now is that I get the following appear when the query is run; is there a way to avoid this?
1599042820811.png
 

ChrisC

Registered User.
Local time
Today, 09:05
Joined
Aug 13, 2019
Messages
90
Thanks CJ - I see what you mean, I will have a try with that as well.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:05
Joined
Sep 21, 2011
Messages
14,050
That works Gasman - thank you.

The only thing now is that I get the following appear when the query is run; is there a way to avoid this?
View attachment 84666
That means Access cannot find that field. Spelling mistake is the normal cause I believe. However your spelling looks correct.?
Have you tried TotalReceipt on it's own?
 

ChrisC

Registered User.
Local time
Today, 09:05
Joined
Aug 13, 2019
Messages
90
That means Access cannot find that field. Spelling mistake is the normal cause I believe. However your spelling looks correct.?
Have you tried TotalReceipt on it's own?

that appears to have sorted it! thanks very much to all for the help! im off for a lie down!
 

Users who are viewing this thread

Top Bottom