Query Access table via VBA and retain cell formats

[mikeymay]

I am running a query via VBA to populate a number of cells with the results of the query.

I have the cells formatted to how I need them but upon the cells being populated, their formats are being changed to date formats.

The fields in the Access table are set up with the correct formats and I am using the following code to populate Excel with the values but they are still displaying as dates when finished

With rsQuery
   .Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic

   Range("CD_CaseNumber") = CStr(strCase) 'ACCESS FIELD AND EXCEL CELL FORMATTED AT TEXT
   Range("CD_StartDate") = CDate(.Fields("CaseStartDate")) 'ACCESS FIELD AND EXCEL CELL FORMATTED AT 'DD MMM YY' FORMAT
   Range("CD_EndDate") = Format(CDate(.Fields("CaseEndDate")), "dd mmm yy") 'ACCESS FIELD AND EXCEL CELL FORMATTED AT 'DD MMM YY' FORMAT
   Range("CD_ClosingBalance") = CDec(.Fields("CaseClosingBalance")) 'ACCESS FIELD AND EXCEL CELL FORMATTED AT 2 DP WITH 000'S SEPERATOR FORMAT
   Range("FD_IntDate") = .Fields("IntAppliedOnDate") 
   
   
   .Close
End With

I'm assuming there will be an answer of 'just re-format the cell after population' but surely this shouldn't need to happen and the formats from the source and the location should be used/retained?

Really struggling with this one as I thought it would have been quite simple.


Thanks

 

[Norie]

What happens if you don't use the conversion functions like CStr, CDate etc?

With rsQuery
   .Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic

   Range("CD_CaseNumber").Value =strCase 'ACCESS FIELD AND EXCEL CELL FORMATTED AT TEXT
   Range("CD_StartDate").Value =.Fields("CaseStartDate") 'ACCESS FIELD AND EXCEL CELL FORMATTED AT 'DD MMM YY' FORMAT
   Range("CD_EndDate").Value = .Fields("CaseEndDate") 'ACCESS FIELD AND EXCEL CELL FORMATTED AT 'DD MMM YY' FORMAT
   Range("CD_ClosingBalance").Value =.Fields("CaseClosingBalance") 'ACCESS FIELD AND EXCEL CELL FORMATTED AT 2 DP WITH 000'S SEPERATOR FORMAT
   Range("FD_IntDate").Value = .Fields("IntAppliedOnDate") 
   
   
   .Close
End With

 

[mikeymay]

They come through as a dd/mm/yyyy format

 

[Norie]

What data are you returning from the recordset and how are the cells formatted?

 

[mikeymay]

There are 2 date and a decimal number. All 3 are held in an access field defined for the required formats and the cells are formatted as required as well so I can;t work out why it is ignoring all the defined formats and putting in as dates.