error 3075 Please Help!

[Guest]

I have the following in my module but when I run it, it says error 3075
syntax error missing operator in query expression "HdyDate Between And And
" And Weekday(HdyDate, 1) <> 1 And Weekday(HdyDate, 1) <> 7

What is the problem?

Public Function Holidays() As Integer
Holidays = DCount("*", "Holiday", "HdyDate Between " & _
Format$(BeginDate, "\#mm\/dd\/yyyy\#") & _
" And " & Format$(EndDate, "\#mm\/dd\/yyyy\#") & _
" And Weekday(HdyDate, 1) <> 1 " & " And Weekday(HdyDate, 1) <> 7 ")

End Function

 

[Ron Hinds]

Where are BeginDate and EndDate defined? They are apparently NULL which is
why you are getting the "missing operator" error. I think you probably want
to rewrite your function passing those tow as parameters to the function
call like so:

Public Function Holidays(BeginDate As Date, EndDate As Date) As Integer
Holidays = DCount("*", "Holiday", "HdyDate Between " & _
Format$(BeginDate, "\#mm\/dd\/yyyy\#") & _
" And " & Format$(EndDate, "\#mm\/dd\/yyyy\#") & _
" And Weekday(HdyDate, 1) <> 1 " & " And Weekday(HdyDate, 1) <> 7 ")

End Function

 

[Guest]

Thanks for the reply, Begindate and Enddate are of datatype date/time.
BeginDate is from tblBorrower while endDate is from tblTaxDoc

 

[Ron Hinds]

Ah - and presumably this module is the Form module in which those fields are
being used, correct? If that is the case, then you need to do something like
this (note BeginDate and EndDate - I don't think you need the Format
function):

Public Function Holidays() As Integer
Holidays = DCount("*", "Holiday", "HdyDate Between " & _
Me![BeginDate] & _
" And " & Me![EndDate] & _
" And Weekday(HdyDate, 1) <> 1 " & " And Weekday(HdyDate, 1) <> 7 ")

End Function

 

[Guest]

Actually no, this module is is going to be used by different querries that i
have so that I can not count the holidays...

The thing is that if I use the syntax statment I gave you, in my query it
crashes because its too long so I thoght that if I put that funtion in a
module then subtract it in the query then it would not crash.....

I actually don't know how I need to reference the funtion in my query, any
ideas?

Ah - and presumably this module is the Form module in which those fields are
being used, correct? If that is the case, then you need to do something like
this (note BeginDate and EndDate - I don't think you need the Format
function):

Public Function Holidays() As Integer
Holidays = DCount("*", "Holiday", "HdyDate Between " & _
Me![BeginDate] & _
" And " & Me![EndDate] & _
" And Weekday(HdyDate, 1) <> 1 " & " And Weekday(HdyDate, 1) <> 7 ")

End Function

Thanks for the reply, Begindate and Enddate are of datatype date/time.
BeginDate is from tblBorrower while endDate is from tblTaxDoc