Copy data in sheet1 using sheet 2 and Access table data.

[aman]

The same error message appears when I copy the output from immediate window to New Query in Access database.

Maybe some of your fields type are numbers, by me all Text.

Yes you are right that data type may be different so please see below the data types of all the fields in the actual Access table:

ID Autonumber
ScanMonth Number
Type Number
Type1 Number
Type2 Number
Spare1 Number
Cases Number
Envelopes Number
Pages Number
Location Text
ScanBoxID Number
BatchedBy Number
ScannedBy Number
BatchNo Number
InputBy Text
ScanDate Number
ScanTime Sate/Time
ScanHour Number
ScanMin Number
PolicyNo Text

 

[aman]

Hi jHB, The data type of scandate was Text in your code but in actual its number so the following code doesn't give any error message but it shows -ve values in all the cells from column Q to column X.

strsql = "TRANSFORM Count(jabberwocky.BatchNo) AS CountOfBatchNo " _
  & "SELECT [Type] & Format([Type1],'00') & Format([Type2],'00') AS QueueNo, Count(jabberwocky.ScanDate) AS Total " _
  & "FROM jabberwocky " _
  & "WHERE ScanDate=" & J & " " _
  & "GROUP BY [Type] & Format([Type1],'00') & Format([Type2],'00') " _
  & "PIVOT Format([ScanHour],'00') In (08, 09,10,11,12,13,14,15);"

 

[aman]

JHB, Please see attached the output that appears in Sheet 1 of Excel workbook.

 

[JHB]

JHB, Please see attached the output that appears in Sheet 1 of Excel workbook.

And???? Try to compare the amount of columns you've between the Excel sheet you use now, and the Excel-sheet you gave me.

The same error message appears when I copy the output from immediate window to New Query in Access database.

Then try to find where on what the error occur!
To help you further, I need your database and a set of real data + you the Excel sheet you use now.

 

[aman]

Thanks JHB. I am not getting any error message now but it gives me negative values in the cells. Please see attached the Project and database workbook. As I can't send you the Access database so I have copied the data across Excelsheet named Database .

 

[JHB]

Insert the line marked with red in the code you've, then it should run.

  If Not rs.EOF Then
    Do
      QueueName = FindQueueNo(rs![QueueNo])
      If QueueName <> "" Then
        RowNo = FindQueueName(QueueName)
        If RowNo <> 0 Then
          [B][COLOR=Red]RememberCountOfBatch = 0[/COLOR][/B]
          For x = rs.Fields.Count To 3 Step -1
           ' MsgBox (rs.Fields(x - 1).Name)
            RememberCountOfBatch = RememberCountOfBatch + IIf(IsNull(rs.Fields(x - 1)), 0, rs.Fields(x - 1))
            ws.Range(Cells(RowNo, 24 - (10 - x)), Cells(RowNo, 24 - (10 - x))) = (rs![Total] - RememberCountOfBatch) / rs![Total]
          Next x
        End If
      End If
      rs.MoveNext
    Loop Until rs.EOF
  End If