Copying row in ListBox, and Date VarType doesn't copy

[donwiss]

I have a multi-column ListBox. It is populated from Access using .GetRows and .List. One of the columns from Access is Date/Time. If the data is extracted from the ListBox, the data type for the timestamp column is Variant/Date.

I'd like to copy a row. Just copying the timestamp cell from the old row to the newly added row turns the VarType to Variant/String. So I figured I use CDate() when copying. Nope. Still Variant/String.

I need all in the column to be Date type, so my sort can correctly work.

 

[Norie]

The values in a textbox don't really have a data type, they are all essentially strings.

How are you 'copying' the values?

 

[donwiss]

But after the initial population of the ListBox, extracting the data shows Variant/Date. So Access is able to pass on its Date/Time format.

ListBox1.AddItem NewName
ListBox1.List(ListBox1.ListCount - 1, 1) = CDate(ListBox1.List(i, 1))

Later when sorting, I can't use DateValue() as it returns only the date, and I also want the time.

 

[Norie]

How are you extracting the data?

P.S. Is this Access or Excel or both?

 

[donwiss]

This is Excel VBA, using DAO to pull data from Access to populate the ListBox. There are no WorkSheets in the application I'm working on. It's all Access and UserForms. Driven by a CommandBar.

 

[Norie]

How/where is this causing problems?

 

[donwiss]

I need all in the column to be Date type, so my sort can correctly work.

Instead of copying that cell from the old row to the new row, I figured I could simply retrieve that cell from Access (the file is already open, as I also copied the row in the table) and put that into the cell. Nope. Becomes a string. Apparently filling the entire .List is different from filling a single cell.

What I can do, instead of Adding an Item, is to get the whole list from Access again.

 

[Norie]

Like I said, columns in a listbox do not have a data type.