Text to Date

[rama]

I am importing some text data to access 2007 database which contains
few date fields. I tried to convert this date stored as text to date
using following expression in access but it brings unexpected
results.
ReqDate: DateSerial(Right([RequirementDate],4),Left(Right
([RequirementDate],10),2),Left(Mid([RequirementDate],5),2))

I am putting some sample results of the query, some are correct and
some are wrong. I would like to eliminate these errors.

RequirementDate ReqDate
21.05.2009 9/5/2010
06.05.2008 6/5/2008
06.05.2008 6/5/2008
22.01.2009 10/1/2010
10.06.2008 10/6/2008
Thanks
Rama

 

[Duane Hookom]

That's a fairly weird way to store dates. I would have expected the first two
characters to be the day of the month. You didn't say if the displayed
records are what you want or if some are wrong. If some are wrong, you should
have provided the desired date:

If your ReqDate is what you want then try:

DateSerial(Right([RequirementDate],4),Left([RequirementDate],2),Mid([RequirementDate],4,2))

otherwise try:
DateSerial(Right([RequirementDate],4),Mid([RequirementDate],4,2),
Left([RequirementDate],2))


If some of your displayed records are wrong then try

 

[Jerry Whittle]

ReqDate: CDate(Mid(RequirementDate, 4,2) &"/"& Left(RequirementDate, 2)&"/"&
Right(RequirementDate, 4))

The above will work IF (1) all the dates are valid (IE no February 30th);
(2) no null values; and (3) the field always has the same number of
characters.

 

[Marshall Barton]

I am importing some text data to access 2007 database which contains
few date fields. I tried to convert this date stored as text to date
using following expression in access but it brings unexpected
results.
ReqDate: DateSerial(Right([RequirementDate],4),Left(Right
([RequirementDate],10),2),Left(Mid([RequirementDate],5),2))

I am putting some sample results of the query, some are correct and
some are wrong. I would like to eliminate these errors.

RequirementDate ReqDate
21.05.2009 9/5/2010
06.05.2008 6/5/2008
06.05.2008 6/5/2008
22.01.2009 10/1/2010
10.06.2008 10/6/2008

Try using the CDate function.

 

[rama]

That's a fairly weird way to store dates. I would have expected the firsttwo
characters to be the day of the month. You didn't say if the displayed
records are what you want or if some are wrong. If some are wrong, you should
have provided the desired date:

If your ReqDate is what you want then try:

DateSerial(Right([RequirementDate],4),Left([RequirementDate],2),Mid([Requir­ementDate],4,2))

otherwise try:
  DateSerial(Right([RequirementDate],4),Mid([RequirementDate],4,2),
Left([RequirementDate],2))

If some of your displayed records are wrong then try
--
Duane Hookom
Microsoft Access MVP

I am importing some text data to access 2007 database which contains
few date fields. I tried to convert this date stored as text to date
using following expression in access but it brings unexpected
results.
ReqDate: DateSerial(Right([RequirementDate],4),Left(Right
([RequirementDate],10),2),Left(Mid([RequirementDate],5),2))

I am putting some sample results of the query, some are correct and
some are wrong.  I would like to eliminate these errors.

RequirementDate    ReqDate
21.05.2009 9/5/2010
06.05.2008 6/5/2008
06.05.2008 6/5/2008
22.01.2009 10/1/2010
10.06.2008 10/6/2008
Thanks
Rama
.- Hide quoted text -

- Show quoted text -

Thanks for the help. The second expression brought the correct
results.
DateSerial(Right([RequirementDate],4),Mid([RequirementDate],4,2),Left
([RequirementDate],2))
Rama