troubleshooting Question

VBA Access Timesheet - Vertical to Horizontal view

Avatar of andrew gunesberk
andrew gunesberk asked on
Microsoft AccessVBA
6 Comments1 Solution109 ViewsLast Modified:
I am basically trying to convert vertical data to horizontal (like an excel table) an am almost there. I have to use a loop but the result is not what I want and not sure how to go about it! Here's the code so far which is working but not how I want:

strSQL = "SELECT * FROM tblTimeSheetData WHERE [WorkDate] BETWEEN #" & 
StartDate & "# AND #" & EndDate & "#" _ 
  & "ORDER BY [EmpID], [WorkDate] ;"    

Set rstTime = CurrentDb.OpenRecordset(strSQL)   

Do While Not rstTime.EOF    
    rstTemp.AddNew
    lngEmpID = rstTime![EmpID]
    rstTemp![EmpID] = lngEmpID

    intWeekday = Weekday(rstTime![WorkDate])

    Select Case intWeekday

        Case 2
           rstTemp![MondayWorkDate] = rstTime![WorkDate]
           rstTemp![MondayWorkHours] = rstTime![WorkHours]

        Case 3
           rstTemp![TuesdayWorkDate] = rstTime![WorkDate]
           rstTemp![TuesdayWorkHours] = rstTime![WorkHours]

        Case 4
           rstTemp![WednesdayWorkDate] = rstTime![WorkDate]
           rstTemp![WednesdayWorkHours] = rstTime![WorkHours]

        Case 5
           rstTemp![ThursdayWorkDate] = rstTime![WorkDate]
           rstTemp![ThursdayWorkHours] = rstTime![WorkHours]

        Case 6
           rstTemp![FridayWorkDate] = rstTime![WorkDate]
           rstTemp![FridayWorkHours] = rstTime![WorkHours]

     End Select

     rstTemp.Update
     rstTime.MoveNext
  Loop

My select query returns exactly what I want:
TNgef1.jpg
My code returns this:
Wfxe8.jpg
I want it to return this:
vlmt2.jpg
Thank you so much for your help!
ASKER CERTIFIED SOLUTION
andrew gunesberk

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros