0

I am trying to assign a Date to a table column. I get 1004 error on each Date column.

Public Type JobStatusDetailLine
    ...

    PromisedShipDate As Date
    LastOpDate As Date
    ShipDate As Date
    
End Type

Public JobStatusDetailArr() As JobStatusDetailLine

    For i = 1 To UBound(JobStatusDetailArr)
        Dim newLabRow As ListRow
        Set newLabRow = tbl.ListRows.Add
        With newLabRow
            
            .Range(1) = JobStatusDetailArr(i).SalesGroup
            .Range(2) = JobStatusDetailArr(i).BuildLocation
            .Range(3) = JobStatusDetailArr(i).ProjectManager
            .Range(4) = JobStatusDetailArr(i).Customer
            .Range(5) = JobStatusDetailArr(i).JobNum
            .Range(6) = JobStatusDetailArr(i).AssemblySeq
            .Range(7) = JobStatusDetailArr(i).PartNum
            .Range(8) = JobStatusDetailArr(i).PartDesc
            .Range(9) = JobStatusDetailArr(i).ProdQuantity
            .Range(10) = JobStatusDetailArr(i).JobReleased
            .Range(11) = JobStatusDetailArr(i).PromisedShipDate
            .Range(12) = JobStatusDetailArr(i).LastOpDate
            .Range(13) = JobStatusDetailArr(i).ShipDate
                       
          
        End With
        
    Next i

Error occurs on Columns 11, 12 and 13. Do I need to do something specific to assign a date? I am sure I have done this in the past with no problem.

mchernecki
  • 29
  • 7
  • how/where redim JobStatusDetailArr() ? what is value of (i) ? More complete code is needed for someone to help you – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ Mar 13 '23 at 23:01
  • See if error occurs with `.Range(11) = Date` – CDP1802 Mar 14 '23 at 11:42
  • I have added the complete For Loop in the example. – mchernecki Mar 14 '23 at 16:34
  • Runs fine with `.Range(11) = Date `. The date comes from SqlServer query `JobStatusDetailArr(i).PromisedShipDate = adoDbRs("PromisedShipDate")` – mchernecki Mar 14 '23 at 16:43
  • Do you know what the value of JobStatusDetailArr(i).PromisedShipDate is when the error occurs ? – CDP1802 Mar 14 '23 at 19:51
  • I just noticed it fails on 01/01/1753. If I keep jumping over that line until I get a different date, then it works. I know it is the value of `System.Data.SqlTypes.SqlDateTime.MinValue` which I am using if I encounter a NULL date field. Is this a bad date in Excel or something? – mchernecki Mar 14 '23 at 20:26
  • Do you want to see the date as 01/01/1753 or as blank. ? – CDP1802 Mar 14 '23 at 21:38
  • Try formatting the date `.Range(11) = Format(JobStatusDetailArr(i).PromisedShipDate,"dd-mm-yyyy")`, or test for the year `If Year(JobStatusDetailArr(i).PromisedShipDate) < 1900 then` – CDP1802 Mar 15 '23 at 09:23
  • Thanks. I eventually removed setting NULL dates to SqlDateTime.MinValue and just displayed them as blanks. – mchernecki Mar 15 '23 at 14:44

1 Answers1

0

Excel stores dates as numbers, 1.0 representing the first second of January 1, 1900. Although a date variable can hold a date before then, there is a problem displaying it unless you specify the format. Since you have multiple dates to check then use a function to check if the date is 01/01/1753 your replacement for NULL.

Function IsDateNull(dt As Date)
    If Year(dt) > 1753 Then IsDateNull = Format(dt, "dd/mm/yyyy")
End Function

also you could assign the array element to an intermediate variable.

Sub Demo()

    Dim i As Long, j As JobStatusDetailLine
    Dim tbl As ListObject, newLabRow As ListRow
    
    Set tbl = Sheets("Sheet1").ListObjects(1)
    For i = 1 To UBound(JobStatusDetailArr)
        j = JobStatusDetailArr(i)
        Set newLabRow = tbl.ListRows.Add
        With newLabRow
            .Range(1) = j.SalesGroup
            .Range(2) = j.BuildLocation
            .Range(3) = j.ProjectManager
            .Range(4) = j.Customer
            .Range(5) = j.JobNum
            .Range(6) = j.AssemblySeq
            .Range(7) = j.PartNum
            .Range(8) = j.PartDesc
            .Range(9) = j.ProdQuantity
            .Range(10) = j.JobReleased
            .Range(11) = IsDateNull(j.PromisedShipDate)
            .Range(12) = IsDateNull(j.LastOpDate)
            .Range(13) = IsDateNull(j.ShipDate)
        End With
    Next

End Sub
CDP1802
  • 13,871
  • 2
  • 7
  • 17