rs3 wont go to cell 18 on Excel from Access

[DMT Dave]

Hi again guy's, can i add a Count within a recordset ?

So I can add total quantity on the next excel to the right of record set ?

So the Sheet comes out something like : 1 Joe Bloggs Wolverhampton WV6 3GH 6 Items

Somewhere in the code like And Vehicle ='" & VehReg & "' And (Count(DelTo Where DelNo = " & I,dbOpenSnapshot) ?????

Set rs2 = db.OpenRecordset("SELECT DISTINCT tblAssign.DelNo, tblAssign.DelTo, tblAssign.Town, tblAssign.PostCode FROM tblAssign " _
                                & "WHERE Driver = '" & mDriver & "' And DeliveryDate = #" & mDelDate & "# And Vehicle = '" & VehReg & "' And DelNo = " & i, dbOpenSnapshot)

[accesstos]

Dave, try this:

Create a query in your database with this definition:

SELECT DelTo, Town, PostCode,  Vehicle, Driver, DeliveryDate, Count(DelNo) AS DelCount 
FROM tblAssign 
GROUP BY DelTo, Town, PostCode, Vehicle, Driver, DeliveryDate;

Check the results and save it.
Then use its name (say qryAssignGroup) in your code as below:

Set rs = db.OpenRecordset("SELECT * FROM qryAssignGroup " _
                           & "WHERE Driver = '" & mDriver _
                           & "' And DeliveryDate = #" & mDelDate _
                           & "# And Vehicle = '" & VehReg _
                           & "' And DelCount <= " & mDelCount, dbOpenSnapshot)

Export the data to Excel with "one step" method.

[DMT Dave]

Hi accesstos, I am shocked, everything you have sent works, this one I am getting syntax error FROM clause run time error 3131 ?

Results are correct when I run the qryAssignGroup, the debug highlights the rs2 (only change from your code) as below

With xLWB
For i = 1 To mDelCount
        'Open recordset with the current RemQty.
        Set rs = db.OpenRecordset("SELECT DISTINCT tblAssign.DelNo, tblAssign.DelTo, tblAssign.Town, tblAssign.PostCode FROM tblAssign " _
                                & "WHERE Driver = '" & mDriver & "' And DeliveryDate = #" & mDelDate & "# And Vehicle = '" & VehReg & "' And DelNo = " & i, dbOpenSnapshot)
        Set rs2 = db.OpenRecordset("SELECT * FROM qryAssignGroup" _
                           & "WHERE Driver = '" & mDriver _
                           & "' And DeliveryDate = #" & mDelDate _
                           & "# And Vehicle = '" & VehReg _
                           & "' And DelCount <= " & mDelCount, dbOpenSnapshot)
        'Export data to worksheet.
        xLWB.Worksheets(1).Cells(2, 3).Offset((i - 1) * 2, 0).CopyFromRecordset rs
        xLWB.Worksheets(1).Cells(2, 7).Offset((i - 1) * 2, 0).CopyFromRecordset rs2
        rs.Close
    Next i
.Worksheets(1).Cells(2, 2) = Me.cboVehicleIndex5
.Worksheets(1).Cells(3, 2) = Me.cboDriverIndex5
.Worksheets(1).Cells(5, 2) = Format(Me.cboDelDateIndex5, "ddd-dd-mmm-yyyy")
.Worksheets(1).Cells.EntireColumn.AutoFit
.Worksheets(1).Cells.EntireColumn.HorizontalAlignment = xlLeft
End With

[DMT Dave]

I also have EntryCode and ExitCode text fields in tblDealers, is it easy to set up another recordset (rs3) to look these up based on the postcode from rs ?

Many Thanks

[accesstos]

I'm sorry Dave! It was my fault. You must enter a whitespace afterwards
qryAssignGroup in SQL def (see the corrected code in my post).

But, you have to export the rs2 once! Out of the For...Next loop.

xLWB.Worksheets(1).Cells(2, 7).CopyFromRecordset rs2

If you need to have blank rows between entries in the worksheet, you should use a new loop for rs2 only.
Could you attach the template workbook?

[accesstos]

I also have EntryCode and ExitCode text fields in tblDealers, is it easy to set up another recordset (rs3) to look these up based on the postcode from rs ?

Many Thanks

postcode is presumably text so should be

"WHERE PostCode = '" & rs.Fields("PostCode") & i & "'"

Otherwise what is the error description you get?

So try this:

Set rs3 = db.OpenRecordset("SELECT DISTINCT tblDealers.PostCode, tblDealers.EntryCode, tblDealers.ExitCode FROM tblDealers " _ 
                                                                                         & "WHERE PostCode = '" & rs.Fields("PostCode") & "'", dbOpenSnapshot)