Different Results From Query and VBA

[evilbungle]

Hi, I have another problem with an Access 07 database I am building to write reports in Excel.

I have several queries that I paste into Excel and for some reason one of them is giving me different results when it pastes than when I run the query from the Access Window.

Through Access the Query "qry_QuotingDownload_agb" returns 11 Records but when I run the following code as part of my sub:

Private Sub cmdrun_Click()
Dim qdefOne As QueryDef
Dim rsOne As Recordset
Dim iCols As Integer
Dim param As Variant
Dim param2 As Variant

'Quoting
Set qdefOne = CurrentDb.QueryDefs("qry_QuotingDownload_agb")
For Each param In qdefOne.Parameters
    param.Value = Eval(param.Name)
Next param
Set rsOne = qdefOne.OpenRecordset
xlSheet.Range("X1").Select
For iCols = 0 To rsOne.Fields.Count - 1
xlSheet.Cells(1, iCols + 24).Value = rsOne.Fields(iCols).Name
Next
xlSheet.Range("X2").CopyFromRecordset rsOne
rsOne.Close
Set rsOne = Nothing
Set qdefOne = Nothing

It only returns 9 records (It is the top 9 records the query resturns in the database window)

Any help would be appreciated.

Thanks

Adam

 

[r.harrison]

Hi Evilbungle,

Have you tried opening the table and filtering the results to see how many records the query should return?

Rich

 

[evilbungle]

When I open the Query in the database it shows 11 which is the correct amount, It is just when I try and paste it into Excel through VBA that it pastes a different amount. (9) All I want is the query to paste in exactly as it runs when just ran from the database.

 

[r.harrison]

Have you tried importing the query directly to Excel instead of through code?

Data - External Data - Access

?

 

[spikepl]

How about some old-fashioned debugging? You know what records you get in the two cases, then you know which records are missing, and that would indicate which criteria are messed up. In your code, debug.print the values of the query's parameters.