RunTime Error 3021 No Current Record

[dyrueta]

Hi All --

It seems as though identical queries are not producing identical results.

The query below was produced by a QueryDef statement in VBA and results in a null recordset, or the error posted in my title:

SELECT CHECKS.EMPLOYEE, Sum(TAXES.AMOUNT) AS EXEMPTION
FROM dbo_PRT_CURRENT__CHECK AS CHECKS INNER JOIN dbo_PRT_CURRENT__CHECK_TAX AS TAXES ON (CHECKS.CHECK_SEQUENCE = TAXES.CHECK_SEQUENCE) AND (CHECKS.PERIOD_END_DATE = TAXES.PERIOD_END_DATE) AND (CHECKS.EMPLOYEE = TAXES.EMPLOYEE)
WHERE (((TAXES.[TAX_ID])='SOC_E') AND ((CHECKS.[CHECK_DATE])>=#1/1/2010# And (CHECKS.[CHECK_DATE])<=#12/31/2010#))
GROUP BY CHECKS.EMPLOYEE;

This didn't make sense, so I went into the Access GUI, examined the query in Design View, refreshed the Check_Date field selector column, and ended up with this query statement, which does return a recordset --

SELECT CHECKS.EMPLOYEE, Sum(TAXES.AMOUNT) AS EXEMPTION
FROM dbo_PRT_CURRENT__CHECK AS CHECKS INNER JOIN dbo_PRT_CURRENT__CHECK_TAX AS TAXES ON (CHECKS.CHECK_SEQUENCE = TAXES.CHECK_SEQUENCE) AND (CHECKS.PERIOD_END_DATE = TAXES.PERIOD_END_DATE) AND (CHECKS.EMPLOYEE = TAXES.EMPLOYEE)
WHERE (((TAXES.[TAX_ID])='SOC_E') AND ((CHECKS.Check_Date)>=#1/1/2010# And (CHECKS.Check_Date)<=#12/31/2010#))
GROUP BY CHECKS.EMPLOYEE;

The only difference between the two is the brackets [] around the Check_Date field in the first, which are absent in the second. Is this the cause of the problem? If so, why, and how do I get it to go away?

Thanks!

 

[dyrueta]

I've refined the query def to the point where the exact same text produces both the null and the not null recordset.

Before the column selection refresh --

SELECT CHECKS.EMPLOYEE, Sum(TAXES.AMOUNT) AS EXEMPTION
FROM dbo_PRT_CURRENT__CHECK AS CHECKS INNER JOIN dbo_PRT_CURRENT__CHECK_TAX AS TAXES ON (CHECKS.CHECK_SEQUENCE = TAXES.CHECK_SEQUENCE) AND (CHECKS.PERIOD_END_DATE = TAXES.PERIOD_END_DATE) AND (CHECKS.EMPLOYEE = TAXES.EMPLOYEE)
WHERE (((TAXES.[TAX_ID])='SOC_E') AND ((CHECKS.Check_Date)>=#1/1/2010# And (CHECKS.Check_Date)<=#12/31/2010#))
GROUP BY CHECKS.EMPLOYEE;

-- which produces the null set --

--and after the column selector refresh in the Design View --


SELECT CHECKS.EMPLOYEE, Sum(TAXES.AMOUNT) AS EXEMPTION
FROM dbo_PRT_CURRENT__CHECK AS CHECKS INNER JOIN dbo_PRT_CURRENT__CHECK_TAX AS TAXES ON (CHECKS.CHECK_SEQUENCE = TAXES.CHECK_SEQUENCE) AND (CHECKS.PERIOD_END_DATE = TAXES.PERIOD_END_DATE) AND (CHECKS.EMPLOYEE = TAXES.EMPLOYEE)
WHERE (((TAXES.[TAX_ID])='SOC_E') AND ((CHECKS.Check_Date)>=#1/1/2010# And (CHECKS.Check_Date)<=#12/31/2010#))
GROUP BY CHECKS.EMPLOYEE;

--which produces a valid recordset. Anyone have any ideas? Is this what I have to look forward to with VBA?

Thanks!