Converted to Access 2010 Now Timevalue() and datevalue() give Error 13 Type Mismatch

That happens in your field value is Null.  Is that the case for some records?

mx

Where is this error occurring?  Query? Form? Code?

mx

The field is not null.  It has a variant value of  rv.firststart= 40498.4131944444
This code worked prior to upgrade to Access 2010

Error 13 Type Missmatch occurs:
rs("Date") = DateValue(ev.firstStart)
but no error if i do this:
rs("Date") = Format(CDate(ev.firstStart), "mm/dd/yyyy") '10/15/10 de changed as this gave type error DateValue(ev.firstStart)

Error occurs for this:
ev.startTime = TimeValue(ev.firstStart)

from VBA window, tools > references

see if you have any references prefix with MISSING:

uncheck this, then do a Debug >Compile

correct any other errors raised

sorry, cross posting...

try this

rs("Date") = datevalue(CDate(ev.firstStart))

no references missing and compiled with no errors.

Sorry, but if I do this in the A2003  Immediate window:

?datevalue(40498.4131944444)

I get Error 13 Type Mismatch  ... so ... ?

mx

dleads,

see my post at http:#a33918074


you have to convert firts to date to extract the date value, hence


rs("Date") = datevalue(CDate(ev.firstStart))

Consequently it did not work in previous versions ...?

mx

likewise, you can get the timevalue with

timevalue(CDate(ev.firstStart))

<This code worked prior to upgrade to Access 2010>

How could that be ?

It definately works in their Access 2003 version.  I'm looking for a reason why it will not work in Access 2010.  I realize there are other ways to get around it, but my preference is to find out the cause and not rewrite.

"It definately works in their Access 2003 version."
It does not work in my Access 2003 version, as I posted above @ http:#a33918090 .  It does with the CDate wrap.

mx

I may have been too quick to say "it worked in Access 2003".  The CODE did (and does) work, but perhaps the value passed was different.

If you type  DateFirst(  and stop after bracket, you will see that DateValue expects NOT a date, no, really, not a date - but a String.
Casting to CDate is almost correct, since date is further cast to string (correctly via default format), but it really is expecting a string, not a number.

VBA does a lot of implicit casting.
DateValue: expect string, get date, will cast to string
DateAdd: expect date, get number (the 40000+), will cast to date
But to expect a double jump from:
DateValue: give number (which equals some date), expect cast to date, further cast to string = no go
what happens is

DateValue: get number, cast number to string - bomb

"If you type  DateFirst(  and stop after bracket, you will see that DateValue expects NOT a date, no, really, not a date - but a String."

Not quite:  From help:

DateValue(date)

The required date argument is normally a string expression representing a date from January 1, 100 through December 31, 9999. However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^            

mx: see http:#a33918191

So, you are saying the Help file is wrong ?
Actually, you get the same error in a query:

SELECT Table1.FIELD2, Table1.FIELD4, DateValue(40498.41319) AS Expr1
FROM Table1;

mx

mx, I'm only going to say this once

(1)
The required date argument is normally a string expression representing a date from January 1, 100 through December 31, 9999.
"DateValue: expect string"

(2)
However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.
"VBA does a lot of implicit casting.
DateValue: expect string, get date, will cast to string"
If you pass a Date or Variant of vartype Date, it will cast to a string and process, per point (1)

I don't see a conflict.  I never said the help file was wrong.

That wasn't my question, which is ... is the Help file wrong?

"However, date can also be any expression that can represent a date, a time, or both a date and time, in that range.  

I see a conflict ... since it also fails in a query. So, the Help file may be wrong ....

mx

I lied. I will clarify again

Type 40498.41319 in the immediate window or assign it to a Double.
Check it in the Watch window. Check vartype(var). It is not a DATE is it?
It is what the internal value of some Date var will be but it is NOT a Date.

VBA will happily convert a non-string to a string for use with DateValue, you gave it a NUMBER of value 40498.41319.  It converts to the STRING parameter "40498.41319".
If you use CDate, you are convert it to a Date type.  Check vartype of CDate.

Still not my question.  Is the Help file wrong?  And if not, then where does that one statement apply?

mx

Missed this comment

> I may have been too quick to say "it worked in Access 2003".  The CODE did (and does) work, but perhaps the value passed was different.

It would have to be.  I tried in 2003/2007/2010 and they are all consistent.

dleads,


please respond to this did you try what i posted at http:#a33918106

what was the result?

dleads,

Since you say

[but no error if i do this:
rs("Date") = Format(CDate(ev.firstStart), "mm/dd/yyyy")]

You already know the solution.

[ I'm looking for a reason why it will not work in Access 2010.  I realize there are other ways to get around it, but my preference is to find out the cause and not rewrite.]

I suppose there is no other way other than to rewrite using explicity Cast.  Maybe ev.firstStart used to be a Date member of ev?

this will have a type mismatch error

rs("Date") = Format(CDate(ev.firstStart), "mm/dd/yyyy")]

comparing date vs string

you can run an after query to convert the field firstStart to date/time format


update tableName
set [firstStart]=cdate([firstStart])
where [firstStart] <>null

> The field .. has a variant value of  rv.firststart= 40498.4131944444
> This code worked prior to upgrade to Access 2010

That was pure luck. Neither does it work in A2007.

> Error 13 Type Missmatch occurs:
> rs("Date") = DateValue(ev.firstStart)

That's because DateValue takes a String and you pass a Double. Thus, 40498.4131944444 is casted to the string "40498.4131944444" which cannot be read as a date/time string.

> but no error if i do this:
> rs("Date") = Format(CDate(ev.firstStart), "mm/dd/yyyy")

This is because you format to a string expression of the date that can be casted to a valid date/time. But that method is not failsafe and not recommended.

> Error occurs for this:
> ev.startTime = TimeValue(ev.firstStart)

Again, TimeValue takes a String which you don't provide.

So ...

What you must do is to convert your Double to Date/time which is what you have to pass to your fields of the recordset.
Here is how to do this correctly:

rs("Date") = DateValue(Format(ev.firstStart, "yyyy-mm-dd"))
ev.startTime = TimeValue(Format(ev.firstStart, "hh:nn:ss"))

This may work as well as CDate returns a Date/time value that will be correctly casted to a string expression which DateValue and TimeValue can read:

rs("Date") = DateValue(CDate(ev.firstStart))
ev.startTime = TimeValue(CDate(ev.firstStart))

Of course, if you had a combined date/time field you wouldn't need all this:

rs("DateTime") = CDate(ev.firstStart)

and this should in fact work as well:

rs("DateTime") = ev.firstStart


/gustav

this was already posted here

http:#a33918106 
and

http:#a33918126

dleads,

when you said "Converted to Access 2010"  did you convert a .mdb to .accdb?

Yes, I took an existing mdb and converted to .accdb

so, after the conversion, the date field data was converted to the
this value >  40498.4131944444  which is actually displayed as 11/16/2010 9:55:00 AM in the .mdb version, is this correct?


actually, dates are stored as number in the table.
the whole number part represents the number of whole days since 12/31/1899,
the decimal part represents part of the day which is the time

so if you see
 40498.4131944444  instead of  11/16/2010 9:55:00 AM

looks like a your access 2010 have a problem displaying the value as date format..

tried, converted three old .mdb to .accdb and i could not reproduce the same one that you have..

what is the Version number of your A2010?

> this was already posted here

No. You didn't post the correct solution using Format, neither any explanation of the issue.

> .. you have to convert firts to date to extract the date value, ...

That is not so. First, it is not what you do; second, you don't have to "extract" a date value first - if you did that, DateValue would serve no purpose. CDate converts to a date/time value including a time part.
What you need, if you wish to use DateValue, is, as I explained, to obtain a string expression of the date/time value because DateValue takes a string parameter. Format is the right tool for this.

The scenario for using TimeValue is similar.

---

By second thought, to cut the confusion and because I don't like to use strings for handling date/time and numerics, I will recommend to use DateSerial and TimeSerial as the optimum solution:

datDate = CDate(ev.FirstStart)
rs("Date") = DateSerial(Year(datDate), Month(datDate), Day(datDate))
ev.startTime = TimeSerial(Hour(datDate), Minute(datDate), Second(datDate))

/gustav

why will i need the format, when i can use

rs("Date") = datevalue(CDate(ev.firstStart))

and

timevalue(CDate(ev.firstStart))

you have to convert firts to date to extract the date value, ...

<That is not so. First, it is not what you do; second, you don't have to "extract" a date value first - if you did that,  DateValue would serve no purpose. CDate converts to a date/time value including a time part.>

why don't you try it first?

cap,

Do you read that statement the same way as mx?  Does "40498.4131944444" represent a date in your books?  If I were the Microsoft MVP and not the other way around, I would have the answer already from the source code :)

The crux is what constitutes "represents a date".  VBA is not a programming language that supports function name overloading (multiple signature per function name).  The only signature available, which is 100% evident when you type DateValue(  and stop at open bracket, is String.

Just after the thoughts of an MVP.

> .. Access/VBA does a LOT of implicit casting.

True. And you get lazy and forget that and sometimes are trapped. I realized that when I started programming in C# some years ago - it makes you look at VBA code more carefully.

/gustav

I've given up and will simply change the code.  Thank you for your time.

<VBA is not a programming language that supports function name overloading (multiple signature per function name).  The only signature available, which is 100% evident when you type DateValue(  and stop at open bracket, is String.>

not really true..
when you type

datevalue(  ' it ask you for date as string -- correct  actually
                   datevalue(date as String)
you continue with

datevalue(cdate(  'now it ask you for expression   cdate(expression) as date

so you continue with


datevalue(cdate(40498.4131944444))
and you will get
11/16/2010

not an error but a valid date

 rs("Date") = Format(CDate(ev.firstStart), "mm/dd/yyyy")

You may be surprised; that works.

have you tried

?date()=format(now(),"mm/dd/yyyy")

and

?date()=cdate(format(now(),"mm/dd/yyyy"))

< If I were the Microsoft MVP and not the other way around, I would have the answer already from the source code :)>

that is why i am an MVP, i just don't stop looking for ways where others do.

"Do you read that statement the same way as mx?  Does "40498.4131944444" represent a date in your books?"
Are you saying that 40498.4131944444 does not represent a date internally ?

OP:
"It has a variant value of  rv.firststart= 40498.4131944444 "
The way I *read* that was ... seeing a number that does represent a date ... internally.  

My confusion was what the Help file states..."However, date can also be any expression that can represent a date, a time, or both a date and time, in that range" ... which *implies* to me that the number as an argument should work, since internally that is how a date is represented. But, the Help File is misleading.  And as we see, the argument MUST be a String.  

In fact, I was the first to point out @ http:#a33918090 that it does not work in previous version either.

Keep in mind that cdate(40498.4131944444) returns 11-16-2010 09:55:00  ... so in fact, it does represent a date.  

mx

cap, http:#a33924207

[
datevalue(  ' it ask you for date as string -- correct  actually
                   datevalue(date as String)
you continue with
datevalue(cdate(  'now it ask you for expression   cdate(expression) as date
datevalue(cdate(40498.4131944444))
and you will get
11/16/2010
]

That doesn't conflict with anything I have said.  datavalue is looking for a string. - check
CDate is looking for anything (will cast to date) - no contention, I said nothing about this
datevalue(cdate(40498.4131944444)) works - check.  Access implicitly converts DATE to the string representing the date, which is a valid STRING for datevalue

datevalue(40498.4131944444)  - doesn't work
40498.4131944444 cast as a string directly (implicitly) is '40498.4131944444'
datevalue('40498.4131944444')  - no go

Do we actually have a disagreement?
You can imagine that DateValue puts a CSTR around the argument, if it is not already a STRING or VARIANT of type STRING.  Like I said, if I were the MVP, the first thing I would have done is download the source code so I can provide detailed info and confirm this.

http:#a33924265

You're missing the point.

[ rs("Date") = Format(CDate(ev.firstStart), "mm/dd/yyyy") ]
The above works 100%.   rs("Date") is looking for a date.  Access (VBA) obliges by implicitly throwing CDate against the right side, so it is
rs("Date") = CDate(Format(CDate(ev.firstStart), "mm/dd/yyyy"))

Just try it, it does work.  Let me know if it doesn't.

[ ?date()=format(now(),"mm/dd/yyyy") ]

No contention here.  One is a string, one is a date.  They are not equal.  But if you tried to assign either to a recordset field of type date, the end result is the same.
Try this

Sub t3()
Dim d As Date, d2 As String
d = Date
d2 = Format(Now(), "mm/dd/yyyy")
MsgBox Date = Format(Now(), "mm/dd/yyyy")   ' your version = false
MsgBox d = d2   ' new version = true !
End Sub

Did you expect that?

[That doesn't conflict with anything I have said. ]

i believe you posted this

VBA is not a programming language that supports function name overloading (multiple signature per function name).

cap,

There is still no conflict.
It doesn't support overloading, here's a definition for it:
     http://en.wikipedia.org/wiki/Function_overloading

Instead, it performs implicit casting to the one signature it accepts.
Overloading and implicit casting are very different things.
Implicit casting is frowned upon in most programming languages, it can easily cause errors and confusion like this question.

"it can easily cause errors and confusion like this question."
The only confusion here really is what the Help file implies :-)

mx