Convert this: 10102005 to date?

[Guest]

The format is MDY.....is there a simple way to do this?
TIA

 

[Guest]

If the string will always have 8 digits, leading zeros then try this

CVDate(format(FieldName,"mm/dd/yyyy"))

 

[Guest]

Thanks Ofer - it did not work, got mostly errors ....there are varied entries
ie 442005 or 4102005 or 12252005

 

[Guest]

Hi.

The format is MDY.....is there a simple way to do this?

No. But you can get a Date data type by formatting the string first
(whether or not it's a full eight digits), then converting to a Date data
type. Try:

X = IIF(Len(SomeValue) = 6, (CDate(Format(SomeValue, "#\/#\/####"))),
CDate(Format(SomeValue, "##\/##\/####")))

.... where SomeValue is a text field with digits.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

It is not possible then
How can the computer tell that
1222005
is 1/22/2005 and not 12/2/2005

 

[Guest]

Iam doing this in access db query.....

SELECT IIf(Len([Old Steam Readings]![ReadingDate])=6,(CDate(Format([Old
Steam Readings]![ReadingDate],"#\/#\/#"))),CDate(Format([Old Steam
Readings]![ReadingDate],"#\/#\/#"))) AS Expr1
FROM [Old Steam Readings];

Am I entering the query correctly?
Thanks
Gary

 

[Rick Brandt]

If the string will always have 8 digits, leading zeros then try this

CVDate(format(FieldName,"mm/dd/yyyy"))

Actually that should be...

CVDate(format(FieldName,"00/00/0000"))

....since it's not a date until AFTER the conversion.

 

[Guest]

Hi.

Try:

SELECT IIF(Len(ReadingDate) = 6, (CDate(Format(ReadingDate, "#\/#\/####"))),
CDate(Format(ReadingDate, "##\/##\/####"))) AS NewReadingDate
FROM [Old Steam Readings];

However, Ofer brings up a valid point. A text string such as 1222005 is
ambiguous as to the actual date intended. Unless less there is a context
involved (or one remembers typing this value), then there is no way to know
whether it's supposed to be 1/22/2005 or 12/2/2005. This SQL statement will
assign 1/22/2005, so the ambiguity will appear to be fixed, but it's not.
It's up to a human to determine whether that date is correct. It may very
well be, since 12/2/2005 is a future date. However, if the data is being
examined sometime next year, then this won't be so obvious.

I suggest having someone manually check all 7 digit numbers in this column
that begin with a 1 in order to ensure that the data is correct.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Iam doing this in access db query.....

SELECT IIf(Len([Old Steam Readings]![ReadingDate])=6,(CDate(Format([Old
Steam Readings]![ReadingDate],"#\/#\/#"))),CDate(Format([Old Steam
Readings]![ReadingDate],"#\/#\/#"))) AS Expr1
FROM [Old Steam Readings];

Am I entering the query correctly?
Thanks
Gary

Hi.


No. But you can get a Date data type by formatting the string first
(whether or not it's a full eight digits), then converting to a Date data
type. Try:

X = IIF(Len(SomeValue) = 6, (CDate(Format(SomeValue, "#\/#\/####"))),
CDate(Format(SomeValue, "##\/##\/####")))

... where SomeValue is a text field with digits.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

You right, thanks

 

[Guest]

Thanks

Hi.

Try:

SELECT IIF(Len(ReadingDate) = 6, (CDate(Format(ReadingDate, "#\/#\/####"))),
CDate(Format(ReadingDate, "##\/##\/####"))) AS NewReadingDate
FROM [Old Steam Readings];

However, Ofer brings up a valid point. A text string such as 1222005 is
ambiguous as to the actual date intended. Unless less there is a context
involved (or one remembers typing this value), then there is no way to know
whether it's supposed to be 1/22/2005 or 12/2/2005. This SQL statement will
assign 1/22/2005, so the ambiguity will appear to be fixed, but it's not.
It's up to a human to determine whether that date is correct. It may very
well be, since 12/2/2005 is a future date. However, if the data is being
examined sometime next year, then this won't be so obvious.

I suggest having someone manually check all 7 digit numbers in this column
that begin with a 1 in order to ensure that the data is correct.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Iam doing this in access db query.....

SELECT IIf(Len([Old Steam Readings]![ReadingDate])=6,(CDate(Format([Old
Steam Readings]![ReadingDate],"#\/#\/#"))),CDate(Format([Old Steam
Readings]![ReadingDate],"#\/#\/#"))) AS Expr1
FROM [Old Steam Readings];

Am I entering the query correctly?
Thanks
Gary

Hi.

The format is MDY.....is there a simple way to do this?

No. But you can get a Date data type by formatting the string first
(whether or not it's a full eight digits), then converting to a Date data
type. Try:

X = IIF(Len(SomeValue) = 6, (CDate(Format(SomeValue, "#\/#\/####"))),
CDate(Format(SomeValue, "##\/##\/####")))

... where SomeValue is a text field with digits.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

The format is MDY.....is there a simple way to do this?
TIA

 

[Guest]

One more question...why does this work:

SELECT
IIf(Len(ReadingDate)=6,(CDate(Format(ReadingDate,"#\/#\/#"))),CDate(Format(ReadingDate,"#\/#\/#"))) AS NewReadingDate
FROM [Old boiler steamflow readings by date];

But this returns #Error in the NewReadingDate field:

SELECT
IIf(Len(ReadingDate)=6,(CDate(Format(ReadingDate,"#\/#\/#"))),CDate(Format(ReadingDate,"#\/#\/#")))
AS NewReadingDate, [Old boiler steamflow readings by date].Steamflow
FROM [Old boiler steamflow readings by date];

Thanks

Hi.

Try:

SELECT IIF(Len(ReadingDate) = 6, (CDate(Format(ReadingDate, "#\/#\/####"))),
CDate(Format(ReadingDate, "##\/##\/####"))) AS NewReadingDate
FROM [Old Steam Readings];

However, Ofer brings up a valid point. A text string such as 1222005 is
ambiguous as to the actual date intended. Unless less there is a context
involved (or one remembers typing this value), then there is no way to know
whether it's supposed to be 1/22/2005 or 12/2/2005. This SQL statement will
assign 1/22/2005, so the ambiguity will appear to be fixed, but it's not.
It's up to a human to determine whether that date is correct. It may very
well be, since 12/2/2005 is a future date. However, if the data is being
examined sometime next year, then this won't be so obvious.

I suggest having someone manually check all 7 digit numbers in this column
that begin with a 1 in order to ensure that the data is correct.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Iam doing this in access db query.....

SELECT IIf(Len([Old Steam Readings]![ReadingDate])=6,(CDate(Format([Old
Steam Readings]![ReadingDate],"#\/#\/#"))),CDate(Format([Old Steam
Readings]![ReadingDate],"#\/#\/#"))) AS Expr1
FROM [Old Steam Readings];

Am I entering the query correctly?
Thanks
Gary

:

Hi.

The format is MDY.....is there a simple way to do this?

No. But you can get a Date data type by formatting the string first
(whether or not it's a full eight digits), then converting to a Date data
type. Try:

X = IIF(Len(SomeValue) = 6, (CDate(Format(SomeValue, "#\/#\/####"))),
CDate(Format(SomeValue, "##\/##\/####")))

... where SomeValue is a text field with digits.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

The format is MDY.....is there a simple way to do this?
TIA

 

['69 Camaro]

Hi.

But this returns #Error in the NewReadingDate field:

It depends upon whether the CDate( ) function can make a date out of the
text string passed to it.

For a 6-digit date string, this format works: "#\/#\/####"
For a 7- or 8-digit date string, this format works: "##\/##\/####"

If the second format is applied to a 6-digit date string, then the first
slash will be placed in the wrong position, and CDate( ) won't be able to
return a date value. Likewise, if the first format is applied to a 7- or
8-digit date string, the first slash will be misplaced so that only one
digit is allowed in the day of the month, and CDate( ) won't be able to
return a date value, either.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

One more question...why does this work:

SELECT
IIf(Len(ReadingDate)=6,(CDate(Format(ReadingDate,"#\/#\/#"))),CDate(Format(ReadingDate,"#\/#\/#")))
AS NewReadingDate
FROM [Old boiler steamflow readings by date];

But this returns #Error in the NewReadingDate field:

SELECT
IIf(Len(ReadingDate)=6,(CDate(Format(ReadingDate,"#\/#\/#"))),CDate(Format(ReadingDate,"#\/#\/#")))
AS NewReadingDate, [Old boiler steamflow readings by date].Steamflow
FROM [Old boiler steamflow readings by date];

Thanks

Hi.

Try:

SELECT IIF(Len(ReadingDate) = 6, (CDate(Format(ReadingDate,
"#\/#\/####"))),
CDate(Format(ReadingDate, "##\/##\/####"))) AS NewReadingDate
FROM [Old Steam Readings];

However, Ofer brings up a valid point. A text string such as 1222005
is
ambiguous as to the actual date intended. Unless less there is a
context
involved (or one remembers typing this value), then there is no way to
know
whether it's supposed to be 1/22/2005 or 12/2/2005. This SQL statement
will
assign 1/22/2005, so the ambiguity will appear to be fixed, but it's
not.
It's up to a human to determine whether that date is correct. It may
very
well be, since 12/2/2005 is a future date. However, if the data is
being
examined sometime next year, then this won't be so obvious.

I suggest having someone manually check all 7 digit numbers in this
column
that begin with a 1 in order to ensure that the data is correct.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message
will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Iam doing this in access db query.....

SELECT IIf(Len([Old Steam
Readings]![ReadingDate])=6,(CDate(Format([Old
Steam Readings]![ReadingDate],"#\/#\/#"))),CDate(Format([Old Steam
Readings]![ReadingDate],"#\/#\/#"))) AS Expr1
FROM [Old Steam Readings];

Am I entering the query correctly?
Thanks
Gary

:

Hi.

The format is MDY.....is there a simple way to do this?

No. But you can get a Date data type by formatting the string
first
(whether or not it's a full eight digits), then converting to a
Date data
type. Try:

X = IIF(Len(SomeValue) = 6, (CDate(Format(SomeValue,
"#\/#\/####"))),
CDate(Format(SomeValue, "##\/##\/####")))

... where SomeValue is a text field with digits.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a
message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

The format is MDY.....is there a simple way to do this?
TIA

 

[Bill Mosca, MS Access MVP]

Gregory

It depends on where you want this to display.

I had a bit of trouble using Ofer's solution. I found this one works (note:
those are backward and forward slashes, not a V):
CVDate(Format([MyField],"00\/00\/0000"))

Do you want to just see it in forms or reports? If so, put it in the text
box's ControlSource preceeded by an equal sign as in:
=CVDate(Format([MyField],"00\/00\/0000"))


If you want to update the fields in a table so they are stored as real dates
then you can run an update query on the field. Once the field is updated,
change it's data type to Date/Time.
UPDATE MyTable
SET MyField=Format([MyField],"00\/00\/0000")

 

[Bill Mosca, MS Access MVP]

You're welcome, Greg. Let us know if you run into any problems with it.

--
Bill Mosca, MS Access MVP

thank you i will try this.
Greg

Gregory

It depends on where you want this to display.

I had a bit of trouble using Ofer's solution. I found this one works
(note:
those are backward and forward slashes, not a V):
CVDate(Format([MyField],"00\/00\/0000"))

Do you want to just see it in forms or reports? If so, put it in the text
box's ControlSource preceeded by an equal sign as in:
=CVDate(Format([MyField],"00\/00\/0000"))


If you want to update the fields in a table so they are stored as real
dates
then you can run an update query on the field. Once the field is updated,
change it's data type to Date/Time.
UPDATE MyTable
SET MyField=Format([MyField],"00\/00\/0000")

--
Bill Mosca, MS Access MVP


message

i know this is a old question but i am having the same problem. i am new
to
access and i am wondering where i type
CVDate(format(FieldName,"mm/dd/yyyy"))
the sting in my field is always 8 digits.

:

If the string will always have 8 digits, leading zeros then try this

CVDate(format(FieldName,"mm/dd/yyyy"))


--
I hope that helped
Good luck


:

The format is MDY.....is there a simple way to do this?
TIA