M
MN
Hi -- How do I convert 112010 to 1/1/2010
and 12012010 to 12/1/2010
Thank you inadvange!
and 12012010 to 12/1/2010
Thank you inadvange!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
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
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
V
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.
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.
M
Marshall Barton
MN said: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
M
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....
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....
M
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.
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.