Access query results different than SQL Server query results

Question

in SQL Server 2012, this query gives me, 

SELECT 

count(RLT.CurrentlyOffWork) as LostTime, 

--tbl_OInjury.OInjuryDate, 

Format([OIJ].[OInjuryDate], 'MM') AS EntryMonth,

Format([OIJ].[OInjuryDate], 'yyyy') AS EntryYear 

--[EntryMonth] & ' ' & [EntryYear] AS EntryMonthYear

FROM tbl_OInjury OIJ LEFT JOIN Tbl_RiskLostTime RLT ON OIJ.OInjuryID = RLT.OInjuryID

where ([OIJ].[OInjuryDate] Between '2019-01-01' and '2019-09-28')

and [OIJ].[OInjuryDate] is not null

group by Format([OIJ].[OInjuryDate], 'yyyy'), Format([OIJ].[OInjuryDate], 'MM')

ORDER BY Format([OIJ].[OInjuryDate], 'yyyy') DESC, Format([OIJ].[OInjuryDate], 'MM') DESC;

this result,

LostTime EntryMonth EntryYear

0 09 2019

0 08 2019

0 07 2019

0 06 2019

0 05 2019

0 04 2019

0 03 2019

0 02 2019

0 01 2019

however in Access 2013, this query

SELECT count(RLT.CurrentlyOffWork) AS LostTime, Format([OIJ].[OInjuryDate], "MM") AS EntryMonth, Format([OIJ].[OInjuryDate], "yyyy") AS EntryYear

FROM tbl_OInjury AS OIJ LEFT JOIN Tbl_RiskLostTime AS RLT ON OIJ.OInjuryID = RLT.OInjuryID

WHERE [OIJ].[OInjuryDate] Between [forms]![frm_ReportSBdate]![Rstartdate] And [forms]![frm_ReportSBdate]![REndDate] and [OIJ].[OInjuryDate] is not null

GROUP BY Format([OIJ].[OInjuryDate], "yyyy"), Format([OIJ].[OInjuryDate], "MM")

ORDER BY Format([OIJ].[OInjuryDate], "yyyy") DESC , Format([OIJ].[OInjuryDate], "MM") DESC;

gives me this result.

6	09	2019
5	08	2019
9	07	2019
10	06	2019
13	05	2019
12	04	2019
12	03	2019
11	02	2019
9	01	2019

The LostTime counts are different and should indeed be a zero value, are both queries are connecting to the same exact database and I am passing in '2019-01-01' and '2019-09-28' in the Access dates.  there has got to be a syntax error in the Access query for the counts.  Please advise if you can.  Thanks!

Answer 1

I still am getting the same results:  the LostTime Counts should be zero as in SQL Server but Access is giving me numbers.  Will go back to the logic.  Thanks for your help!

Answer 2

You may have to specify the data type of the parameters:

PARAMETERS

    [forms]![frm_ReportSBdate]![Rstartdate] DateTime,

    [forms]![frm_ReportSBdate]![REndDate] DateTime;

SELECT

    count(RLT.CurrentlyOffWork) AS LostTime,

    Format([OIJ].[OInjuryDate], "MM") AS EntryMonth,

    Format([OIJ].[OInjuryDate], "yyyy") AS EntryYear

FROM

    tbl_OInjury AS OIJ

LEFT JOIN

    Tbl_RiskLostTime AS RLT

    ON OIJ.OInjuryID = RLT.OInjuryID

WHERE

    [OIJ].[OInjuryDate] Between [forms]![frm_ReportSBdate]![Rstartdate] And [forms]![frm_ReportSBdate]![REndDate]

    and

    [OIJ].[OInjuryDate] is not null

GROUP BY

    Format([OIJ].[OInjuryDate], "yyyy"),

    Format([OIJ].[OInjuryDate], "MM")

ORDER BY

    Format([OIJ].[OInjuryDate], "yyyy") DESC,

    Format([OIJ].[OInjuryDate], "MM") DESC;

Answer 3

Could be the same issue as this one: 

https://social.msdn.microsoft.com/Forums/office/en-US/9acb5a91-c02e-47af-8e5c-9264fddec307/ms-access-and-sql-server-give-different-results-on-the-same-query-on-same-tables