Date serial criteria giving me wrong data

[Gina Maylone]

Hello all! And Happy Friday! I hope ya'll are somewhere warm and snow free because I am NOT. Anyway Access is doing its best to blow my mind again. I am creating statements where the first line is the balance forward (anything that happened previous to the current month.

SELECT Accounts.MemberID_FK, Sum((Nz([dbamount],0)-Nz([cramount],0))) AS BALFWD, Sum(Accounts.CRAmount) AS SumOfCRAmount, Sum(Accounts.DBAmount) AS SumOfDBAmount, Accounts.CRDATE, Accounts.Details, Accounts.Comments, Accounts.DBDate, Accounts.AsmtType
FROM Accounts
GROUP BY Accounts.MemberID_FK, Accounts.CRDATE, Accounts.Details, Accounts.Comments, Accounts.DBDate, Accounts.AsmtType
HAVING (((Sum((Nz([dbamount],0)-Nz([cramount],0))))<>0) AND ((Accounts.DBDate)<=DateSerial(Year([Forms]![runassessments]![DateRun]),Month([Forms]![runassessments]![DateRun]),1)) AND ((Accounts.AsmtType) Like [Forms]![RunAssessments]![AssessmentType] & "*")) OR (((Sum((Nz([dbamount],0)-Nz([cramount],0))))<>0) AND ((Accounts.CRDATE)<=DateSerial(Year([Forms]![runassessments]![DateRun]),Month([Forms]![runassessments]![DateRun]),1)) AND ((Accounts.AsmtType) Like [Forms]![RunAssessments]![AssessmentType] & "*"));

My daterun is 2/28/19. It is including a transaction posted on 2/1/2019 (an accounts.dbdate)! Even if I hard enter the date I get 2/1/19 included.

SELECT Accounts.MemberID_FK, Sum((Nz([dbamount],0)-Nz([cramount],0))) AS BALFWD, Sum(Accounts.CRAmount) AS SumOfCRAmount, Sum(Accounts.DBAmount) AS SumOfDBAmount, Accounts.CRDATE, Accounts.Details, Accounts.Comments, Accounts.DBDate, Accounts.AsmtType
FROM Accounts
GROUP BY Accounts.MemberID_FK, Accounts.CRDATE, Accounts.Details, Accounts.Comments, Accounts.DBDate, Accounts.AsmtType
HAVING (((Accounts.MemberID_FK)=1394) AND ((Sum((Nz([dbamount],0)-Nz([cramount],0))))<>0) AND (

	
	
(Accounts.DBDate)<=DateSerial(Year(#2/28/2019#),Month(#2/28/2019#),1)
) AND ((Accounts.AsmtType) Like [Forms]![RunAssessments]![AssessmentType] & "*")) OR (((Accounts.MemberID_FK)=1394) AND ((Sum((Nz([dbamount],0)-Nz([cramount],0))))<>0) AND ((Accounts.CRDATE)<=DateSerial(Year(#2/28/2019#),Month(#2/28/2019#),1)) AND ((Accounts.AsmtType) Like [Forms]![RunAssessments]![AssessmentType] & "*"));

Anyone have any thoughts? The fields are formatted short date in the table and query. In case it matters, Access 16, Windows 10. Thanks!!

[ranman256]

I see no need to use DATESERIAL,
can you not just use BETWEEEN date1 AND date2?

[pbaldy]

Your criteria is "<=", and 2/1 is "less than or equal to" 2/1. Am I missing something?

[Gina Maylone]

I just figured it out, duh. Thanks Paul. Needed another set of eyes. :-)

[pbaldy]

No problem, we've all been in that boat.

[Gina Maylone]

Because date1 can be infinite. Some people owe on their accounts back to 2016. So it's better to leave it open ended. Thanks ranman!