Differing results from query in Access and VB6 2

[WBH1138]

hi

I am running some queries in VB6 using ADO from an Access d/b.

I run one query, then another to compare to the other query and add/amend records, then want to run a 3rd query to add further information.

The problem is that the third query is not returning the expected results.

The SQL is to return the last payment date and amount for each client....
SELECT T.[Invoice date] AS InvoiceDate, T.[Amount in currency] AS Amount
FROM Transactions AS T
WHERE T.[Invoice date] IN
(
SELECT MAX([Invoice date])
FROM Transactions
WHERE ([Procedure ID] = 9 OR ([Procedure ID] = 8 AND Details LIKE '*payment*'))
AND [Amount in currency] <> 0
AND [Currency abbreviation] <> 'IGN'
AND [Invoice date] < #10/23/2007 23:59:59#
AND [Client ID] = 409382903
)
AND T.[Client ID] = 409382903

(this is one of many different ways of getting the data that I've tried)
Works fine in Access but get a different result from within VB?

As the SQL obviously works is there something stopping the correct records being found?

Wayne

 

[HarleyQuinn]

How do the returned result sets differ?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[WBH1138]

In Access I get the correct result, i.e. the last payment date and the last amount paid

When running it in my VB code I get back an earlier date and it's associated payment.

 

[HarleyQuinn]

Can you show the code you use to trun the query from VB?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[WBH1138]

1st query is passed to a command object in a DataReport
2nd and 3rd queries are passed to this routine

' **************************************************************************************************
' UseDatabase
'
' Routine to set an ADO command object and return an ADO recordset object.
' **************************************************************************************************
Public Function UseDatabase(ByVal strQuery As String, ByVal blnReadOnly As Boolean, ByVal bytCommandType As Byte) _
As ADODB.Recordset

'Defines our command object
Set m_cmdCommand = New ADODB.Command
With m_cmdCommand
.ActiveConnection = m_conConnection
.CommandText = strQuery
.CommandType = bytCommandType
End With

'Defines our RecordSet object.
Set m_rstRecordSet = New ADODB.Recordset
With m_rstRecordSet
.CursorType = adOpenStatic
.CursorLocation = adUseClient
If blnReadOnly = True Then
.LockType = adLockReadOnly
Else
'.LockType = adLockOptimistic
.LockType = adLockBatchOptimistic
End If
.Open m_cmdCommand
End With

Set UseDatabase = m_rstRecordSet

End Function

The connection is set up initially so only done once

Wayne

 

[genomon]

Er, still missing a piece here.
What is the string value in .CommandText = strQuery? Is it exactly the same SQL as your original post?

"Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes."

 

[HarleyQuinn]

And the query you pass to that function is exactly the same as you posted in your first post, correct?

This problem also only happens when you've updated data and then run it from VB? So for example, if you updated the data from Access and then just ran that function for your third query, do you get the right data?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[WBH1138]

Yes, the query in strQuery is as originally posted.

and yes the query works OK in Access, and OK in a a separate VB app with no other queries, but NOT when run after the first and second queries have run.

The queries are only for reporting so while the recordsets are updated the actual data on the database does not change.

 

[WBH1138]

so to clarify, Query one gets me a recordset which is the driving file for the DataReport.
Query 2 gets me a recordset of other similar records which are used to compare and add/amend to recordset 1
Query 3 (the failing one) should then use the client ID from each record in recordset 1 to get extra information to be amended on recordset 1 before producing the report.

Hope that helps )

 

[JoeAtWork]

ADO uses slightly different SQL syntax than Access.

Replace * with %

And don't use the Access "#" syntax for dates, rather pass it an unambigious date as a string, for example:

[Invoice date] < '23-Oct-2007 23:59:59'

 

[WBH1138]

B*gg*r!

I knew about the */% issue!!!!

Didn't know about the # thing though - we're new to ADO here so that'll be very helpful.

Will try that, see what happens and report back

Thanks very much!

 

[WBH1138]

OK!

Using % instead of * certainly helped - ADO didn't like using ' instead of # and I was still getting odd results where the last details weren't really the last but I went back to a simpler SQL query and that know works.

So thanks again. I'd have never spotted that one