Solved Query not showing ANY records when criteria set to only show >0?

[ChrisC]

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.

my brain hurts!

many thanks as always

Chris

 

[arnelgp]

can you use the Calculated expression in substitute for its "Name":

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

 

[ChrisC]

Hi Arnelgp,

still returns the same results im afraid!

thank you

 

[Gasman]

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]

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]

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?

 

[ChrisC]

Thanks CJ - I see what you mean, I will have a try with that as well.

 

[Gasman]

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]

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!