Importing Dates from Excel

[DThreadgill]

I'm trying to import data from Excel into an Access table using VBA. I can't do the Import file because the dates on the spreadsheet are convaluted. Hence, my dilemma. When viewing the spreadsheet, some of the dates in the column are formatted as mm/dd/yy h:mm (10/1/2007 0:00) and when I actually click on the cell it shows mm/dd/yyyy hh:mm:ss AM/PM (10/1/2007 12:00:00 AM). I have tried changing the entire column using

Columns("G:G").Select
selection.numberformat = "MM/DD/YYYY"  

and

Columns("G:G").Select
selection.numberformat = "mm/dd/yyyy hh:mm"

and tried to format as the following upon rst.addnew

 If Not IsNull(RST2![AR Initial Contact Date]) Or RST2![AR Initial Contact Date] <> "" Then
rst![AR Initial Contact Date] = Format(RST2![AR Initial Contact Date], "mm/dd/yyyy")
Else
End If

It will change some but not all. It seems to pull in those that are originally formatted as mm/dd/yy h:mm but not mm/dd/yyyy hh:mm:ss AM/PM.

AR Initial Contact field datatype is date/time and format is short date.
Any ideas or suggestions?

Thanks

[MMcCarthy]

Try creating a variable to temporarily store the value and try formatting it as a long date as this is handled better by Access. You can still have the format of the field in rst set to short date.

 
Dim tmpDate as Date
 
    If nz(RST2![AR Initial Contact Date], "") <> "" Then
        tmpDate = Format(RST2![AR Initial Contact Date], "dd mmm yyyy")
    Else
        tmpDate = ""
    End If
    rst![AR Initial Contact Date] = tmpDate
 

[DThreadgill]

Try creating a variable to temporarily store the value and try formatting it as a long date as this is handled better by Access. You can still have the format of the field in rst set to short date.

 
Dim tmpDate as Date
 
    If nz(RST2![AR Initial Contact Date], "") <> "" Then
        tmpDate = Format(RST2![AR Initial Contact Date], "dd mmm yyyy")
    Else
        tmpDate = ""
    End If
    rst![AR Initial Contact Date] = tmpDate
 

Thanks Mary! I had to change format to text and dim tmpdate as string because there were null values in many of the records but it worked like a charm! I thank you very much!

[MMcCarthy]

Thanks Mary! I had to change format to text and dim tmpdate as string because there were null values in many of the records but it worked like a charm! I thank you very much!

You are welcome.