Difference in query results - ADO vs. Access query

[jender624]

I have an SQL generated ADO recordset that is returning a different value than an Access query with the SAME SQL statement. I know that the Access query results are right - for some reason the ADO recordset isn't. Here is my code:

Dim rsTotals As ADODB.Recordset
Dim sSql as String

sSql = "SELECT Sum(ext_variance) AS ext_variance_total "
sSql = sSql & "FROM variance_wdo "
sSql = sSql & "WHERE month Between #" & sDateFrom & "# And #" & sDateTo & "#"

Set rsTotals = CreateObject("ADODB.Recordset&quot
rsTotals.Open sSql, con, 1

lblReportDollarTotal.Caption = Format(rsTotals!ext_variance_total, "Currency&quot

As you can see, I'm calculating a sum. I've stopped the code after the SQL statement is generated, copied the value of sSql in the debug window, and pasted it into an Access query. When I do this and run the query, I get the correct value ($161,505.23). However, if I let the code open the recordset, I get the wrong value ($136,109.28) returned.

The "variance_wdo" query I'm pulling data from is a union, if that makes a difference.

Has anybody else had a problem with this? Thanks.

jender624

 

[psemianonymous]

Union queries have caused problems with me because they type-convert dates to text. Thus the "between" operator may be failing because the text "3/1/2003" is not "BETWEEN 2/1/2003 AND 2/1/2004" - do you see what I'm saying?

So to fix this, you would have to format your dates in a text-comparison-friendly manner, i.e. YYYY-MM-DD-AM/PM-HH-MM


Then again, I could be wrong. Either way, it's interesting that the ADODB connection is different from the (internal?) querydef result.

 

[bgledet]

I have had this happen to me also. If you run through the entire recordset before writing to the Control it should provide the correct value. I.E:

rstTotals.moveFirst
rstTotals.moveLast

I am not sure what causes this problem except that unlike a standard query, ADO does not always loop through the entire recordset on it's own.

 

[jender624]

I'm getting consistant values between the access query and the ado recordset now; Thanks so much, bg! I've been trying to figure this out for a day and a half!

Thanks again,

jender624

 

[cmmrfrds]

The Open method is probably best used when you want to perform recordset maintenance (need to be aware of cursor location, cursor type, and lock type these all effect how the recordset will be processed). For other types of recordsets it is better to use the Execute method. I would guess your results would be correct.

Set rsTotals = CreateObject("ADODB.Recordset&quot
Set rsTotals = con.Execute(sSql)

 

[jender624]

I'll keep that in mind, cmmrfrds. Thanks for the tip!

jender624

 

[JoanneM]

Hello there

can I put in my penny worth just as a check for curiosity sake

foolio I believe has the right end of the stick because Access in its infinite window will default to US date format if there is ambiguity ie 02/07/2003 will be read by the query as 7th of feb not 2nd of July

unless you specifically format it - I had 2 & half days of
murder calculating numbers of tasks between dates until I typed the switched dates physically in a sample query and it produced the wrong results - proving a default US date format reading

the simplest way to check is to put a date parameter that cannot be taken except as it is eg 15/03/2003 and 25/03/2003 and see if the calcs come out accurate - if they do then try it with a lower date eg 02/03/2003 and 10/03/2003 this would be read as 3rd Feb to 3rd Sept by the query and would give you a much greater total than the first even though it is still for 10 days ( you think)

Hope this helps
& sorry if I wasted your time

regards

Jo

 

[jender624]

I'm sorry about this guys, but it looks like the query results aren't as consistant as I thought. I'm still getting differences. I've included code to loop through the recordset (movefirst, movelast), and I've completely taken out the date criteria in the sql statement to make sure that isn't the problem. Even with all of that, I'm still getting different results. Here is my code now:

sSql = "SELECT Sum(ext_variance) AS ext_variance_total "
sSql = sSql & "FROM variance_wdo "

Set rsTotals = CreateObject("ADODB.Recordset&quot
rsTotals.Open sSql, con, 1

rsTotals.MoveFirst
rsTotals.MoveLast

lblVarianceTotal.Caption = Format(rsTotals!ext_variance_total, "Currency&quot

If anybody had any additional ideas, I would appreciate them.

Sorry again - thought I had this resolved.

jender624

 

[markgrizzle]

Try looping through the query and doing the sums in the loop? I know it's slower but...

WHERE month Between #" & sDateFrom & "# And #" & sDateTo & "#"

What are you storing in your date variables? You may need to wrap the month() function around each to make sure you're parsing properly.

Mark

 

[jender624]

I'm currently storing 6/1/2003 in sDateFrom and 6/30/2003 in sDateTo.

Will the month() function take year into account also? I'm allowing the user to define the date range based on a couple combo boxes, so they could potentially have a range of say 7/1/2002 to 6/30/2003.

I'm going to try looping through and manually adding up the value as you suggested.

Thanks,

jender624

 

[jender624]

I think I may be closer to a solution. I tried adding up the values in the recordset manually, and found that the ADO recordset isn't pulling in all the resulting records of the union query (variance_wdo).

So if my union query is set up like this:

STATEMENT1
UNION ALL STATEMENT2

my STATEMENT1 contains 117 records and STATEMENT2 contains 67 records. The recordcount of my recordset is 117, which is only the first part of the union. If I run the exact same SQL in an access query, it pulls in the total number of records, that being 184.

So, the problem is that the recordset is only looking at the first half of the union query. Why, I don't know. I'm not sure how to make the code work as is, so I may open 2 recordsets - 1 for each part of the union, and add up the values that way. I'll see if that works, and post back.

 

[jender624]

OK, I figured it out. Part of my union query has a LIKE condition in it. I was using a * for the wild card character, which is correct in an access query. However, the wildcard character for an ADO recordset is a % sign. I switched the * to a %, and now I'm getting all the records returned via the recordset. I think that should do it - thanks to everybody for all your help.

jender624