today's transactions

[Mik3]

I have a query which selects the transactions made on the current date by using Date() in the criteria. I did this so that I can make a report of today's transactions.
Problem is that the query only returns the transactions with a positive quantity (positive means item returned to stores, negatative means issued).
I hope you can make some sense out of this...I couldn't

 

[Mile-O]

Where are these positive and negative values coming from?

Do you have a criteria on the quantity field?

 

[Mik3]

positive values are coming from "Quantity Returned" in "Returns" form.
negative values come from "Quantity Issued" in "Issues" form.
I haven't set any criteria for Quantity in the query...shall I?

 

[Mile-O]

Can you post your SQL?

 

[Mik3]

SELECT [Stores (Mosta) Transactions].[Transaction Date], [Stores (Mosta) Transactions].[Reference No], [Stores (Mosta) Transactions].[Location ID], [Stores (Mosta) Transactions].Quantity, [Stores (Mosta) Transactions].ID
FROM [Stores (Mosta) Transactions]
GROUP BY [Stores (Mosta) Transactions].[Transaction Date], [Stores (Mosta) Transactions].[Reference No], [Stores (Mosta) Transactions].[Location ID], [Stores (Mosta) Transactions].Quantity, [Stores (Mosta) Transactions].ID
HAVING ((([Stores (Mosta) Transactions].[Transaction Date])=Date()))
ORDER BY [Stores (Mosta) Transactions].[Transaction Date];

hope this is what u asked for

 

[AncientOne]

How do you get the values from the two forms into [Stores (Mosta) Transactions]? is that a query or a table?

 

[Mik3]

[Stores (Mosta) Transactions] is a table.
Transaction No, Date, Tool, Location, Quantity are stored for every item issued/received.

 

[AncientOne]

Are the rows with negative quantities not appearing at all or is the quantity appearing as null? Are the negative quantities appearing correctly in the table? What happens if you run the query without the date criterion? What happens if you run the query without the Quantity?

 

[Mik3]

I managed to solve it by writing ">=Date()" in the criteria, instead of just "Date()"..don't ask me how or why but it works like that.

To answer you..
The rows with negative quantites were not appearing at all in the query, and they appeared correctly in the table.
If I run the query without the date criterion, all the transactions would appear, even those with negative quantity. If I removed the quantity column from the query, it would have no effect on the other results.

Thanks for your help guys.