ms access and excel loop

[access101]

I have a crosstab query called ShiftHours in access with the following fields:

Staff, Shift, Mon, Tue, Wed, Thu, Fri, Sat, Sun

i need to export the fields to excel using a recordset

the problem i have is the the shift field

i need to loop through the records and if the shift = "AM" then update specified cells in excel
elseif shift = PM then update the other cells

below is the code i've been working on


Dim rs9 As Recordset
Set rst9 = CurrentDb.OpenRecordset("ShiftHours")
Do While Not rst9.EOF
For X = 10 To 16

'Am
If DLookup("Shift", "ShiftHours", "[Shift] = AM") Then
Set xlSheet = xlBook.Worksheets("Labor")
xlSheet.Range("a" & X) = (rst9.[Employee])
xlSheet.Range("c" & X) = (rst9.[Rate])
xlSheet.Range("d" & X) = (rst9.[wed])
xlSheet.Range("h" & X) = (rst9.[thu])
xlSheet.Range("l" & X) = (rst9.[fri])
xlSheet.Range("p" & X) = (rst9.[sat])
xlSheet.Range("t" & X) = (rst9.[sun])
xlSheet.Range("x" & X) = (rst9.[mon])
xlSheet.Range("ab" & X) = (rst9.[tue])
'PM
else
xlSheet.Range("a" & X) = (rst9.[Employee])
xlSheet.Range("c" & X) = (rst9.[Rate])
xlSheet.Range("f" & X) = (rst9.[wed])
xlSheet.Range("j" & X) = (rst9.[thu])
xlSheet.Range("o" & X) = (rst9.[fri])
xlSheet.Range("r" & X) = (rst9.[sat])
xlSheet.Range("t" & X) = (rst9.[sun])
xlSheet.Range("z" & X) = (rst9.[mon])
xlSheet.Range("ad" & X) = (rst9.[tue])
End If
rst9.MoveNext
Next X
Loop

xlBook.Save
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

 

[dhookom]

Is there a reason why you don't create separate columns in the crosstab for AM and PM? If you don't know how to do this, please reply back with your significant table/fields and the SQL of your crosstab.

Duane
Hook'D on Access
MS Access MVP

 

[access101]

ok i'll try that. but is the rest of my code ok to perform the function i require?

 

[dhookom]

The Dlookup() won't work and I'm not sure why you are using it when the AM and PM should be in the recordset.

   If rst9("Shift") = "AM" Then

Duane
Hook'D on Access
MS Access MVP

 

[access101]

If rst9("Shift") = "AM" Then

it almost works now its just exporting all the hours to am shift and not splitting them up by shift. so it seems my if statement needs fixed

 

[dhookom]

How about copying and pasting your actual code?

Duane
Hook'D on Access
MS Access MVP

 

[access101]

Function RunShift()
'*****************************************************************************
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Labor\LaborHours.xls")

xlApp.Visible = False
'*****************************************************************************
On Error Resume Next
Dim rs9 As Recordset
Set rst9 = CurrentDb.OpenRecordset("ShiftHours")
Do While Not rst9.EOF

'PIC Am
Set xlSheet = xlBook.Worksheets("Labor")
For X = 10 To 16
xlSheet.Range("a" & X) = (rst9.[Employee])
xlSheet.Range("c" & X) = (rst9.[Rate])
If rst9("Shift") = "AM" Then
xlSheet.Range("d" & X) = (rst9.[wed])
xlSheet.Range("h" & X) = (rst9.[thu])
xlSheet.Range("l" & X) = (rst9.[fri])
xlSheet.Range("p" & X) = (rst9.[sat])
xlSheet.Range("t" & X) = (rst9.[sun])
xlSheet.Range("x" & X) = (rst9.[mon])
xlSheet.Range("ab" & X) = (rst9.[tue])

If rst9("Shift") = "PM" Then
xlSheet.Range("f" & X) = (rst.[wed])
xlSheet.Range("j" & X) = (rst.[thu])
xlSheet.Range("n" & X) = (rst.[fri])
xlSheet.Range("r" & X) = (rst.[sat])
xlSheet.Range("v" & X) = (rst.[sun])
xlSheet.Range("z" & X) = (rst.[mon])
xlSheet.Range("ad" & X) = (rst.[tue])
End If
End If
rst9.MoveNext
Next
Loop


xlBook.Save
xlApp.DisplayAlerts = False
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
DoCmd.SetWarnings True


End Function

 

[SkipVought]

your loops -- should only have one, IMHO...

    Set xlSheet = xlBook.Worksheets("Labor")
    x = 10
    Do While Not rst9.EOF
        
        'PIC Am
        xlSheet.Range("a" & x) = (rst9.[Employee])
        xlSheet.Range("c" & x) = (rst9.[Rate])
        
        Select Case rst9("Shift")
            Case "AM"
                xlSheet.Range("d" & x) = (rst9.[wed])
                xlSheet.Range("h" & x) = (rst9.[thu])
                xlSheet.Range("l" & x) = (rst9.[fri])
                xlSheet.Range("p" & x) = (rst9.[sat])
                xlSheet.Range("t" & x) = (rst9.[sun])
                xlSheet.Range("x" & x) = (rst9.[mon])
                xlSheet.Range("ab" & x) = (rst9.[tue])
                
            Case "PM"
                xlSheet.Range("f" & x) = (rst.[wed])
                xlSheet.Range("j" & x) = (rst.[thu])
                xlSheet.Range("n" & x) = (rst.[fri])
                xlSheet.Range("r" & x) = (rst.[sat])
                xlSheet.Range("v" & x) = (rst.[sun])
                xlSheet.Range("z" & x) = (rst.[mon])
                xlSheet.Range("ad" & x) = (rst.[tue])
        End Select
        rst9.MoveNext
        x = x + 1
    Loop

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[access101]

im getting an runtime error 3265 item not found in collection?

 

[dhookom]

Which line is causing the error?
You have both rst and rst9 in your code. Have you attempted to compile prior to posting questions?

Duane
Hook'D on Access
MS Access MVP

 

[access101]

figured out why i get the runtime error, the crosstable only shows days that have hours so if mon is blank then it doesn't show up in my query. not sure how to fix so it just ignores the field if its missing. the on resume next seems to be simplest method.

 

[dhookom]

You can specify the columns in the Column Headings property so that all days of the week are generated. If you had replied earlier with the SQL of your crosstab, someone could provide the exact SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[access101]

here is the SQL


TRANSFORM Sum(([in]-[out])*24*-1) AS Expr1
SELECT Shift.Employee, Shift.Type, Shift.Position, First(Shift.Shift) AS FirstOfShift, (Sum(Shift.Rate))/(Count(Shift.Type)) AS Rate
FROM Shift
WHERE (((Shift.Type)='Employee'))
GROUP BY Shift.Employee, Shift.Type, Shift.Position, Shift.Shift
PIVOT Format([Date],'ddd');

 

[dhookom]

Try this to get all the days

TRANSFORM Sum(([in]-[out])*24*-1) AS Expr1
SELECT Shift.Employee, Shift.Type, Shift.Position, 
First(Shift.Shift) AS FirstOfShift, (Sum(Shift.Rate))/(Count(Shift.Type)) AS Rate
FROM Shift
WHERE (((Shift.Type)='Employee'))
GROUP BY Shift.Employee, Shift.Type, Shift.Position, Shift.Shift
PIVOT Format([Date],'ddd') IN ("SUN","MON","TUE","WED","THU","FRI","SAT");

Duane
Hook'D on Access
MS Access MVP