[AccessD] Import Excel Dates into Access

Stuart McLachlan stuart at lexacorp.com.pg
Tue Oct 14 18:46:22 CDT 2003


On 14 Oct 2003 at 18:34, Mitsules, Mark wrote:

> Thank you so much Stuart.  You are absolutely right.  Even a right click
> 'filter by selection' will not result in matches for the same date because
> of the time differences.  I've never used Int() with dates before and could
> not find an example in MSDN.  Can you provide an example syntax?
> 

Since date/times are actually stored as a double with the whole 
number represents the date and the fractional part represents the 
time, using Int() will truncate to just the date part.

For existing data:
Docmd.RunSQL "Update myTable Set MyDate = Int(MyDate)"

Importing a CSV into a table:
Dim rs as DAO.Recordset
DIm strA as string, strB as String,strC as String, strD as String
Set rs = CurrentDB.Openrecordset("MyTable")
Open "MyFile.CSV" for Input as #1
While Not EOF(1)
	Line Input #1, strA,StrB,StrC,strD
     rs.Addnew
     rs!MyNumber = Val(StrA) 
     rs!MyString = StrB
     rs!MyDateTime = Val(StrC)
     rs!MyDate = Int(Val(StrD))
     rs.Update
Wend
Close #1





 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.





More information about the AccessD mailing list