By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Importing Dates from Excel

P: 57
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
Expand|Select|Wrap|Line Numbers
  1. Columns("G:G").Select
  2. selection.numberformat = "MM/DD/YYYY"  
and
Expand|Select|Wrap|Line Numbers
  1. Columns("G:G").Select
  2. selection.numberformat = "mm/dd/yyyy hh:mm"
and tried to format as the following upon rst.addnew
Expand|Select|Wrap|Line Numbers
  1.  If Not IsNull(RST2![AR Initial Contact Date]) Or RST2![AR Initial Contact Date] <> "" Then
  2. rst![AR Initial Contact Date] = Format(RST2![AR Initial Contact Date], "mm/dd/yyyy")
  3. Else
  4. 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
Nov 13 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim tmpDate as Date
  3.  
  4.     If nz(RST2![AR Initial Contact Date], "") <> "" Then
  5.         tmpDate = Format(RST2![AR Initial Contact Date], "dd mmm yyyy")
  6.     Else
  7.         tmpDate = ""
  8.     End If
  9.     rst![AR Initial Contact Date] = tmpDate
  10.  
Nov 14 '07 #2

P: 57
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim tmpDate as Date
  3.  
  4.     If nz(RST2![AR Initial Contact Date], "") <> "" Then
  5.         tmpDate = Format(RST2![AR Initial Contact Date], "dd mmm yyyy")
  6.     Else
  7.         tmpDate = ""
  8.     End If
  9.     rst![AR Initial Contact Date] = tmpDate
  10.  
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!
Nov 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 14 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.