Same SQL statement but different results via VBA Query

[canuck]

Thanks LPurvis. I had read your earlier reply too quickly; thought it was from Gizmo again. Would have said hello had I noticed it was a new-to-this-thread-user!

 

[canuck]

Initially I did want to find the solution to this and move forward in ADO. However if the issue is ADO becoming involved in processing wildcards specified in the existing Access Query objects I am loath to change them as they have been tested.

So using DAO which either defers to access to provide the result set or processes the queries similar to access seems to be my only path short of creating intermediate tables from the queries and using ADO to query them.

I am still unsure why ADO (or DAO) becomes involved in the actuall processingin of the Access Query objetcts so I'll have to do some reading I guess!

Thank you again LPurvis.

Scott

 

[LPurvis]

It's a different provider that is providing the interface to the Jet data. The OLEDB provider for Jet has some differences to the DAO interface in syntax and implementation, for example wildcards and sensitivity to reserved words. (Conversely it has some abilities that DAO and Access don't!)

Changing your queries defintion to Alike "%... would be required (if it is what's required, I couldn't say without seeing all teers of the query request) and if you don't want to make such changes then you don't have a great deal of choice but to abandon ADO in this respect.
(You could code loading the query definition into a variable and alter it before executing it in ADO - but again, that's effort).

You'll know best how you want to proceed.

Cheers.

 

[dysert]

Is anyone still watching this thread? I hope so because I have a similar problem to the OP. I'm running Access 2013 under Windows 7. (And I'm an Access novice.)

When I run a report I get a wildly different result set versus using the same SQL in VBA. Can someone please help me figure out what's going on?

Thanks!

 

[gemma-the-husky]

I would start a new thread.

You can't be using the same query definition, or you would get the same result. Can you post the SQL for the queries?

 

[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