Can't get CDate to work in my query...

[Guest]

I am trying to convert data in a text field to date format in a query. The
text data is in mm/dd/yyyy format, so should be recognized by the CDate
function, but I'm getting a type conversion error each time I run the query.
I am using simple syntax:
CDate([MyField])
Any ideas as to what I'm doing wrong?

 

[Guest]

If your text data actually looks like
10/27/2006 or 10-27-2006
There should not be a problem.
If it is actually
10272006
You will get an error 13 (type conversion)
Look at the actual table values to see what it really is.

 

[Guest]

Yes, the data ACTUALLY looks like 10/27/2006 !! This is why I specifically
included the actual format in my original question. And yes, it SHOULD work.
But unfortunately it's NOT working. Thus my question.

Any other ideas?

If your text data actually looks like
10/27/2006 or 10-27-2006
There should not be a problem.
If it is actually
10272006
You will get an error 13 (type conversion)
Look at the actual table values to see what it really is.

I am trying to convert data in a text field to date format in a query. The
text data is in mm/dd/yyyy format, so should be recognized by the CDate
function, but I'm getting a type conversion error each time I run the query.
I am using simple syntax:
CDate([MyField])
Any ideas as to what I'm doing wrong?

 

[Guest]

Yes, I have an idea.
My idea is I will add you to my list of "don't respond"
I ask perfectly reasonable questions to ensure we know what we are dealing
with and you get testy.

Yes, the data ACTUALLY looks like 10/27/2006 !! This is why I specifically
included the actual format in my original question. And yes, it SHOULD work.
But unfortunately it's NOT working. Thus my question.

Any other ideas?

If your text data actually looks like
10/27/2006 or 10-27-2006
There should not be a problem.
If it is actually
10272006
You will get an error 13 (type conversion)
Look at the actual table values to see what it really is.

I am trying to convert data in a text field to date format in a query. The
text data is in mm/dd/yyyy format, so should be recognized by the CDate
function, but I'm getting a type conversion error each time I run the query.
I am using simple syntax:
CDate([MyField])
Any ideas as to what I'm doing wrong?

 

[Guest]

I apolozige for being "testy" (really frustrated rather than testy), but you
must admit that your response was a bit insulting... "Look at the actual
table values to see what it really is," as if I must not have understood what
the simple date format in my original email meant.
I am just looking for an answer to my problem, and your response was not at
all helpful.

Yes, I have an idea.
My idea is I will add you to my list of "don't respond"
I ask perfectly reasonable questions to ensure we know what we are dealing
with and you get testy.

Yes, the data ACTUALLY looks like 10/27/2006 !! This is why I specifically
included the actual format in my original question. And yes, it SHOULD work.
But unfortunately it's NOT working. Thus my question.

Any other ideas?

If your text data actually looks like
10/27/2006 or 10-27-2006
There should not be a problem.
If it is actually
10272006
You will get an error 13 (type conversion)
Look at the actual table values to see what it really is.


:

I am trying to convert data in a text field to date format in a query. The
text data is in mm/dd/yyyy format, so should be recognized by the CDate
function, but I'm getting a type conversion error each time I run the query.
I am using simple syntax:
CDate([MyField])
Any ideas as to what I'm doing wrong?

 

[Guest]

Many people who post in these newsgroups use incorrect terminology, don't
correctly state the problem, or don't have a clear understanding of what they
are asking. Certainly the questions I asked were very basic, but I don't
know you or your capabilities, so if you are insulted by them, sorry, but
that is your problem. Don't be so insecure. I certainly did not intend to
demean or insult. I was trying to make sure the basics are covered.

I did some testing. If the date field is null, you should get an error 94 -
Invalid use of Null. If it is a zero length string ( "" ), you will get the
13 - Type Mismatch

I would check to see if there are any records that have a zero length
string. If there are not, This alternative may work for you:

cdate(dateserial(right([datefield],4),left([datefield],2),mid([datefield],4,2)))

Let me know how it works out.

As an alternative

I apolozige for being "testy" (really frustrated rather than testy), but you
must admit that your response was a bit insulting... "Look at the actual
table values to see what it really is," as if I must not have understood what
the simple date format in my original email meant.
I am just looking for an answer to my problem, and your response was not at
all helpful.

Yes, I have an idea.
My idea is I will add you to my list of "don't respond"
I ask perfectly reasonable questions to ensure we know what we are dealing
with and you get testy.

Yes, the data ACTUALLY looks like 10/27/2006 !! This is why I specifically
included the actual format in my original question. And yes, it SHOULD work.
But unfortunately it's NOT working. Thus my question.

Any other ideas?

:

If your text data actually looks like
10/27/2006 or 10-27-2006
There should not be a problem.
If it is actually
10272006
You will get an error 13 (type conversion)
Look at the actual table values to see what it really is.


:

I am trying to convert data in a text field to date format in a query. The
text data is in mm/dd/yyyy format, so should be recognized by the CDate
function, but I'm getting a type conversion error each time I run the query.
I am using simple syntax:
CDate([MyField])
Any ideas as to what I'm doing wrong?

 

[Guest]

One problem with CDate is that it will fail miserably if it runs into
something that it cannot convert into a date. I always, and I mean always,
use the IsDate function first to see if the text can possibly be evaluated as
a date before passing it to CDate. Something like below:

SELECT *
FROM tbl??
WHERE (((IsDate([MyField]))=False));
Or
SELECT IIf(IsDate([MyField])=True,Cdate([MyField]),#1/1/1950#) AS NotDate
FROM tbl???;

 

[Gary Walter]

Is your "From name" in any way related to:

KLAATU BARADA NIKTO

the phrase that was used to stop the robot Gort
from taking over the world in

"The Day the Earth Stood Still"

LangaList "Grin" section had link to
guy who reprogrammed a city sign
to above

http://www.zug.com/gab/index.cgi?func=view_thread&sort=active&head=1&thread_id=64475