Same SQL statement but different results via VBA Query

[dysert]

Hi, and thanks for replying. Here is the SQL from the report:

SELECT DISTINCT LB.[Missed Check], LB.[Bill Vision], LB.[Bill Medical], LB.[Bill Dental], LB.[Leave Code], LB.[Bill Date], LB.SSN, LB.[Line Total], LB.[Due Date], LB.[Bill Amount], SBT.[Payroll Start] AS PayrollStart, SBT.[Payroll End] AS PayrollEnd, LB.[Missed Check] AS CheckDate, SBT.[Benefits Effective Date] AS BenefitsEffectiveDate, SBT.[Benefits Termination Date] AS BenefitsTermindationDate
FROM ([LOA billed] AS LB INNER JOIN [State of Indiana SMR] AS SM ON LB.SSN = SM.SSN) INNER JOIN [State of Indiana Benefits and Termination] AS SBT ON LB.[Missed Check] = SBT.[Check Date]
WHERE (((LB.PrintedFinal)<>True))
GROUP BY LB.[Bill Vision], LB.[Bill Medical], LB.[Bill Dental], LB.[Leave Code], LB.[Bill Date], LB.SSN, LB.[Line Total], LB.[Due Date], LB.[Bill Amount], SBT.[Payroll Start], SBT.[Payroll End], LB.[Missed Check], SBT.[Benefits Effective Date], SBT.[Benefits Termination Date], LB.[Group Number], LB.[Peoplesoft ID];


and here is the SQL in VBA:

bddSQL = "SELECT DISTINCT LB.[Missed Check], LB.[Bill Vision], LB.[Bill Medical], LB.[Bill Dental], " & _
"LB.[Leave Code], LB.[Bill Date], LB.SSN, LB.[Line Total], LB.[Due Date], LB.[Bill Amount], " & _
"SBT.[Payroll Start] AS PayrollStart, SBT.[Payroll End] AS PayrollEnd, LB.[Missed Check] AS CheckDate, " & _
"SBT.[Benefits Effective Date] AS BenefitsEffectiveDate, " & _
"SBT.[Benefits Termination Date] AS BenefitsTermindationDate " & _
"FROM ([LOA billed] AS LB " & _
"INNER JOIN [State of Indiana SMR] AS SM ON LB.SSN = SM.SSN) " & _
"INNER JOIN [State of Indiana Benefits and Termination] AS SBT ON LB.[Missed Check] = SBT.[Check Date] " & _
"WHERE (((LB.PrintedFinal) <> 1)) " & _
"GROUP BY LB.[Bill Vision], LB.[Bill Medical], LB.[Bill Dental], LB.[Leave Code], LB.[Bill Date], " & _
"LB.SSN, LB.[Line Total], LB.[Due Date], LB.[Bill Amount], SBT.[Payroll Start], SBT.[Payroll End], " & _
"LB.[Missed Check], SBT.[Benefits Effective Date], SBT.[Benefits Termination Date], LB.[Group Number], " & _
"LB.[Peoplesoft ID]; "


They sure look the same to me. Thanks!

 

[dysert]

Aha! You may not believe this, but I changed the report version so that it uses "1" instead of "True" in the Where clause. It now works!

 

[LPurvis]

Hi there.

Yes, sadly, ODBC connections don't always translate Boolean values from the constants that ACE supports to the traditional bit types of the server.
So you may expect
BoolField = True
in an Access query, to be parsed into
BoolField = 1
on the server. However, in a local query, your constant is still evaluated as -1, but the bit values of the column are still 0 or 1.
Comparing against zero instead of 1 (or -1) is one option of avoiding this.
It depends upon whether you've allowed Null values in your bit columns as to whether that alone is enough.
i.e. instead of your existing condition, of:
WHERE LB.PrintedFinal <> 1
you could have:
WHERE LB.PrintedFinal = 0
As "false" is zero in both RDBMS. However, if Nulls are permitted, you'd need to consider those also.

Cheers