Date Calculation and Totals in Query Does not return anything

[adnanebrahimi]

Hi Expert,



I have Query for Calculate Remaining Money in a Account, for Report

This Query must Return reaming money from Yesterday and show it on Header of Report,

I wrote these Query but it doesn't returning any Result:

================================================== =========
SELECT (Sum(Transaction.Deposit)-Sum(Transaction.Withdrawal)) AS Remain
FROM [Transaction]
WHERE (((Transaction.ReportDate)=Date()-1));
================================================== =========

I think there is some problem with using Totals and Date calculation in a same Query

[JoeM]

It works fine for me. Can you post a small sampling of your data?
I assume that ReportDate is formatted as Date, and your Deposit and Withdrawal fields are formatted as a numeric option.

[adnanebrahimi]

Yes Sure.

ID	ReportDate	Customer Name	Transaction Date	Transaction Code	Bank/Branch	Transaction Type	Deposit	Withdrawal	F/T Account
2	9/19/2012	Adnan Ebrahimi	9/18/2012	R01	Bank X1/1	Creadit		9,999,999,999.00	21088722

Withdrawal & Deposit data type are Currency

ReportDate are Date/Time (Short Formatted)

[JoeM]

Two things to check, both with regard to the ReportDate:
1. Do you have any records with yesterday's date (meaning, confirm that there are records that should meet your criteria and be returned)?
2. Is there a time component stored with ReportDate? If so, you will need to drop it, or else your "matching criteria" will be trying to match on that exact time.

[adnanebrahimi]

Answer of 1 : Yes i have, i just post a sample here
Answer of 2 : Yest Report Date is a Required and it's fill Automatically with Now() function.

[JoeM]

Answer of 1 : Yes i have, i just post a sample here

The sample you posted has a ReportDate of 9/19/2012. Yesterday was 9/26/2012. Since your query only pulls back records from yesterday, the record you posted would NOT be returned.

Answer of 2 : Yest Report Date is a Required and it's fill Automatically with Now() function.

Here is the source of your problem. The Now() function not only returns current date, but it also returns the current time.

Let's say you have a ReportDate of "9/26/2012 10:34 AM".
Whenever time is not specificed, 12:00 Midnight is assumed. So Date()-1 returns "9/26/2012 12:00 AM".
"9/26/2012 10:34 AM" does not equal "9/26/2012 12:00 AM", so it would not be returned. The only records that would be returned are those where the time component equals exactly 12:00 AM.

Try changing your criteria to:

WHERE Int(Transaction.ReportDate)=Date()-1;

The reason why this works is because dates are stored in Access and Excel as the number of days since 1/1/1900. Time components are fractional parts of days. So to remove the time from a day, we can remove the fractional part using the INT function.

[adnanebrahimi]

Thanks i Solved it,

Here is my mistake:

I fill Report Date with Now() function which means it fills with Full date and time value and of course it's not match with my criteria!

[adnanebrahimi]

Thanks for your help

[JoeM]

Here is my mistake:

I fill Report Date with Now() function which means it fills with Full date and time value and of course it's not match with my criteria!

Yep, I alluded to the issues the time piece presents in my two prior replies.
You will either want to fill it using Date() instead of Now(), or you will need to use criteria like I posted in my last reply.

Glad it is working now.