Convert 112010 to date field

[MN]

Hi -- How do I convert 112010 to 1/1/2010
and 12012010 to 12/1/2010
Thank you inadvange!

 

[Ron2006]

First, I wish you good luck.

The easiest part is use the last for characters as the year.

From that point on is where the good luck comes.

How will you distinguish 111 as being January 11 from November 1

Your example does not give use sifficient information to devise any
type of rules. You will have to look at the data to see if a rule can
be derived.

Ron

 

[vanderghast]

Is 1112010 means 1/11/2010 or 11/1/2010 ?

The encoding you have is thus potentially ***ambiguous***, unless the middle
number is ALWAYS two digits, but that does not fit the example you supplied.
Should have then been 1012010 to represent 1/01/2010 and in that case

DateSerial( yourNumber MOD 10000, yourNumber \ CDec(1000000),
(yourNumber\10000) MOD 100 )

should do the job, assuming the middle number of your encoding is the day,
else, try

DateSerial( yourNumber MOD 10000, (yourNumber\10000) MOD 100,
yourNumber \ CDec(1000000) )



Vanderghast, Access MVP.

 

[Marshall Barton]

Hi -- How do I convert 112010 to 1/1/2010
and 12012010 to 12/1/2010

If your input is that hapharzard, then you probably can not
do it in all cases. Assuming the year part will always be 4
digits, this kind of logic can deal with 6 and 8 digit
inputs:

Select Case Len(dfld)
Case 6

mydate=DateSerial(Right(dfld,4),Left(dfld,1),Mid(dfld,2,1))
Case 8

mydate=DateSerial(Right(dfld,4),Left(dfld,2),Mid(dfld,3,2))
Case 7
' Need a way to determine the month in a date
' such as 1232010. Is it 12/3/2010 or 1/23/2010?
Case Else
MsgBox "Invalid number of digits"
End Select

 

[MN]

Sorry for I am provide wrong case:
All of the case always have format mddyyyy, as all of you can point it out
my err :-(
Sorry all. I think I will using the case of 7 and 8 like Marsh wrote. But
how can I wrote it in the query ?
And how can we validate the date? like month can't >12.
Again - Thanks you....

 

[Marshall Barton]

1 or 2 digit month, 2 digit day of month and 4 digit year in
a query:

IIf(Len(dfld)=7,DateSerial(Right(dfld,4),Left(dfld,1),Mid(dfld,2,2)),IIf(Len(dfld)=7,DateSerial(Right(dfld,4),Left(dfld,2),Mid(dfld,3,2)),Null))

Validation of these funky dates should be done either in the
table or field's Validation rules or somewhere before
running the query.