Help! DateValue Causing Type Mismatch, Error 13

[Michael Gulvin]

Can someone please restore my sanity and offer some ideas. I am converting
an application from Access 2.0 to Access 97 and one line of code keeps
failing where before it worked fine.

The code in question is in the OnLoad event of a query based form. I have
simplified the offending line and proved that it is the DATEVALUE causing
the problems.

If I just include the following in the Onload Event it fails with a Type
Mismatch, Why...?

Dim NumberOfDays As Single
NumberOfDays=DateValue(Now())

Now if I type the exact same thing in an immediate window it works fine, and
it also works in Access 2.0.

Any help would be appreciated,

Thanks

Mike.

[Rich Harrington]

Dim NumberOfDays as Double

==========================================
'If debugging is the process of eliminating coding errors,
then programming must be the process of creating them.'
- Some damn wiseguy
==========================================
Michael Gulvin <Michael...@btinternet.com> wrote in message
news:7252mb$5f7$1...@plutonium.btinternet.com...

[LMLinson]

In article <7252mb$5f7$1...@plutonium.btinternet.com>, "Michael Gulvin"
<Michael...@btinternet.com> writes:

>If I just include the following in the Onload Event it fails with a Type
>Mismatch, Why...?
>
> Dim NumberOfDays As Single
> NumberOfDays=DateValue(Now())

Here's what I got in the Debug Window:

? Now()
11/8/98 7:37:47 PM
? DateValue(Now())
11/8/98
? Csng(DateValue(Now()))
36107

There wasn't a Date/Time variable type in Access 2; there is in Access 97. I'd
guess that is what DateValue now returns and why you're getting a type
mismatch.

You could either convert, as above, to a Single if you want to Dim your
NumberOfDays as a Single, (but be aware that before they gave us a Date/Time
variable type, they told us that a date was kept in a Double -- not a Single).

Use

Dim NumberOfDays as Date

if you really want a date.

Access certainly didn't return something that obviously looked like a "single"
(nor a "double", for that matter) when I used DateValue(Now()).

[Allen Browne]

DateValue() returns a variant of type Date. In general, Access 97
is stricter with its typing than Access 2 was.

Even allowing for the fact that you have simplifed your example,
I cannot see any reason to assign this to a type Single. You're
probably better off staying with the Date type and using DateDiff()

If you really want to fudge things, you could try using a
Double with CDbl(Nz()) instead of DateValue().

Michael Gulvin wrote:
>
> Can someone please restore my sanity and offer some ideas. I am converting
> an application from Access 2.0 to Access 97 and one line of code keeps
> failing where before it worked fine.
>
> The code in question is in the OnLoad event of a query based form. I have
> simplified the offending line and proved that it is the DATEVALUE causing
> the problems.
>

> If I just include the following in the Onload Event it fails with a Type
> Mismatch, Why...?
>
> Dim NumberOfDays As Single
> NumberOfDays=DateValue(Now())
>

[Michael Gulvin]

Allen,

Can you explain this a little for me..?

>If you really want to fudge things, you could try using a
>Double with CDbl(Nz()) instead of DateValue().

Thanks

Mike

Allen Browne wrote in message <36464CC6...@odyssey.apana.org.au>...

[Michael Gulvin]

Okay,

Thanks for the quick response, I had already tried DIMensioning as something
different, I have tried all the suggestions posted and none of them work...!

The immediate window works fine, its only when it appears in an OnLoad event
in a form. I would be interested in someone confirming this as a problem, I
know I can get around it, but this has got me going now....<BG>

Any chance you could create a form with a DateValue(Now()) in the OnLoad
event, maybe MsgBox the result...? Or I can create a sample DB and compress
and attach it...?

I will check if it occurs in a module or any other events today (if its
quiet..!)

Thanks Again,

Mike

LMLinson wrote in message <19981108205620...@ngol08.aol.com>...

>
>In article <7252mb$5f7$1...@plutonium.btinternet.com>, "Michael Gulvin"
><Michael...@btinternet.com> writes:
>

> >If I just include the following in the Onload Event it fails with a Type
> >Mismatch, Why...?
> >
> > Dim NumberOfDays As Single
> > NumberOfDays=DateValue(Now())
>

[Michael S. Kaplan]

A Date is of type Date. Why exactly do you need it to use something other
then a date?

Either put it into a Variant or a Date. It is not a single and never should
have been put in one. You got lucky when VBA var typing was not as string.
Its time to do the right thing instead of worrying why the wrong thing does
not work any more.

Michael

Michael Gulvin <Michael...@btinternet.com> wrote in message

news:7269pd$8lf$1...@plutonium.btinternet.com...

[Michael S. Kaplan]

If you are really bored, use the TypeName function, this will tell you the
type of the return value. It will say Date and then you'll know for sure.

Michael

Michael S. Kaplan <mic...@nospam.trigeminal.com> wrote in message
news:#rzXw77C#GA.262@upnetnews05...

[Michael Gulvin]

Michael,

Please forget the 'Single' declarartion, very bad example and my appologies.
I have since done some more checking and it still does not make sense.

If I do the following in the OnLoad Event it fails with Type Mismatch:
Dim X as Variant
X = DateValue(Now())

If I put that in a module and call the module instead it works fine, In fact
this is my quick work-around.

Now get this, it only does it when the form is query based and before you
question the query I have simplified that, it runs fine on its own etc.

To check if this was an Access 97 thing I created a new DB simple table,
query and form and it all works fine.

I am starting to wonder whether I have a corruption somethere.

Please before you shoot me down in flames I have checked this, I would only
post to a newsgroup as a last resort..!

Thanks for your help

Mike

Michael S. Kaplan wrote in message ...

[Gary Labowitz]

Ran the following:
Private Sub Form_Load()
Dim x As Long
Dim y As Single
Dim z As Double
x = DateValue(Now())
y = DateValue(Now())
z = DateValue(Now())
Debug.Print "x= ", x
Debug.Print "y= ", y
Debug.Print "z= ", z
End Sub

It returned the following:
x= 36108
y= 36108
z= 36108

Is there a problem?

--
Gary (MCT, MCPS, MCSD)
http://www.enter.net/~garyl/ for references to good books
Contribute to ga...@enter.net
ICQ 6375624

[Allen Browne]

Internally, Access stores dates as 8-byte floating point numbers,
where the integer part represents the day, and the fraction part
the time of day (midday is .5, 9AM is .25 etc.)

The Double is also an 8-byte floating point, so if you want to
treat the date as anything, it makes sense to use a Double, not
a Single (4-byte, floating point). CDbl() specifically converts
a variable into a type Double.

Since a field can contain Null, and a VBA type Double or
Single cannot, if you are performing such a conversion, you
must specifically handle the case where the date field is
Null. In Access 95 or 97, the Nz() function allows you to
specify the assumption to use if the field is Null. For
example, if you want to assume today for Null, use:
= Nz([MyField], Date())

In general, you are far better served using internal functions
designed for date maths, e.g. DatePart(), DateAdd(), DateDiff().
These will give you much more predictable results than the
rounding problems that occur when comparing variables of type
Single or Double.