Syntax problem!

[Guest]

i have the following in my module and I am receiving an error when I run my
query saying runtime error 13 type mismatch

Public Function Weekends (BeginDate As Date, EndDate As Date) As Integer

Weekends = DateDiff("d", BeginDate, EndDate) & _
-DateDiff("ww", BeginDate, EndDate, 7) & _
-DateDiff("ww", BeginDate, EndDate, 1)

End Function


NB: the entries in the tables for begindate and end date have also time in
them as 12/06/2005 12:20 PM

 

[Dirk Goldgar]

i have the following in my module and I am receiving an error when I
run my query saying runtime error 13 type mismatch

Public Function Weekends (BeginDate As Date, EndDate As Date) As
Integer

Weekends = DateDiff("d", BeginDate, EndDate) & _
-DateDiff("ww", BeginDate, EndDate, 7) & _
-DateDiff("ww", BeginDate, EndDate, 1)

End Function


NB: the entries in the tables for begindate and end date have also
time in them as 12/06/2005 12:20 PM

Are you really intending to concatenate the various DateDiff results in
that function? Should you maybe be writing it as

Weekends = _
DateDiff("d", BeginDate, EndDate) _
- DateDiff("ww", BeginDate, EndDate, 7) _
- DateDiff("ww", BeginDate, EndDate, 1)

? I don't know whether that gives you the answer you want or not, but
it won't give you the type mismatch.

 

[Douglas J Steele]

Do any of the date fields you're passing contain Nulls? The Date datatype
cannot accept Nulls.

You either need to use the Nz function to convert the Null dates to some
default value, or else change the function declaration to declare BeginDate
and EndDate as Variants, and then include some logic in the function to
handle Null values.

 

[Douglas J Steele]

And Dirk's right: the & are incorrect.

 

[Guest]

Thanks, that worked very well...

Are you really intending to concatenate the various DateDiff results in
that function? Should you maybe be writing it as

Weekends = _
DateDiff("d", BeginDate, EndDate) _
- DateDiff("ww", BeginDate, EndDate, 7) _
- DateDiff("ww", BeginDate, EndDate, 1)

? I don't know whether that gives you the answer you want or not, but
it won't give you the type mismatch.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)