Create a date from data import

[Lee]

I linked to a SQL file and did a query as the file is too large to import
using Access 2007. The query pulled in a date field with the date formated
as 20090515. I use Excel and can create a date using
date(left(),mid(),right()) but haven't a clue in Access. Any help is greatly
appreciated.
Thanks,
Lee

 

[Ken Snell MVP]

DateValueResult: DateSerial(CLng(Left(NameOfYYYYMMDDField, 4)),
CLng(Mid(NameOfYYYYMMDDField, 5, 2)), CLng(Right(NameOfYYYYMMDDField, 2)))

 

[John W. Vinson]

I linked to a SQL file and did a query as the file is too large to import
using Access 2007. The query pulled in a date field with the date formated
as 20090515. I use Excel and can create a date using
date(left(),mid(),right()) but haven't a clue in Access. Any help is greatly
appreciated.
Thanks,
Lee

Ken's DateSerial, or more compactly

CDate(Format([datefield], "@@@@\-@@\-@@"))

 

[John Spencer]

I always test the value first to handle errors that are caused by nulls
or invalid dates.

IIF(IsDate(Format([datefield],"@@@@\-@@\-@@")),CDate(Format([datefield],"@@@@\-@@\-@@")),null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I linked to a SQL file and did a query as the file is too large to import
using Access 2007. The query pulled in a date field with the date formated
as 20090515. I use Excel and can create a date using
date(left(),mid(),right()) but haven't a clue in Access. Any help is greatly
appreciated.
Thanks,
Lee

Ken's DateSerial, or more compactly

CDate(Format([datefield], "@@@@\-@@\-@@"))

 

[Lee]

Thanks for the help, this is much different than excel.
Lee

I always test the value first to handle errors that are caused by nulls or
invalid dates.

IIF(IsDate(Format([datefield],"@@@@\-@@\-@@")),CDate(Format([datefield],"@@@@\-@@\-@@")),null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I linked to a SQL file and did a query as the file is too large to
import using Access 2007. The query pulled in a date field with the date
formated as 20090515. I use Excel and can create a date using
date(left(),mid(),right()) but haven't a clue in Access. Any help is
greatly appreciated.
Thanks,
Lee

Ken's DateSerial, or more compactly

CDate(Format([datefield], "@@@@\-@@\-@@"))