Error 3085

[PatAccess]

Hello,
This worked fine until today and I can seems to find the problem :banghead:

I have a button with the following code:
Private Sub Cmd_OpenRptPEByName_Click()
DoCmd.OpenReport "Rpt_CARenew", acViewPreview
End Sub

when I click the button I get
Error code 3085
Undefined function 'Date' in expression

The report I am trying to open has the following in the filter (data proterty)section:
[Forms]![Frm_CARenewByName]![cboFName] = Qry_CARenew.[EmpID] And (Year(Qry_CARenew.[Expires]) = Year(Date())) AND (Month(Qry_CARenew.[Expires]) = Month(Date()) OR Month(Qry_CARenew.[Expires]) = (Month(Date()) + 1) OR Month(Qry_CARenew.[Expires]) = (Month(Date()) - 1))

Can you please help me?

Thank you,

 

[sneuberg]

One thing I've found that will cause weird errors like this is a bad reference. I suggest checking them (VB Editor, Tools, References) to make sure they are ok.

 

[ashleedawg]

Is the report's Record Source set to Qry_CARenew or is there more to it than that?

What is the SQL behind Qry_CARenew ?

 

[ashleedawg]

Something else I noticed; I'm not sure what exactly you need to display but I suspect there's a mistake in the filter criteria (possibly unrelated to the error you're getting).

I think a fairly common mistake when comparing date-parts like you are is to "not compare all date-parts equally" (if that makes any sense!)

When I have an issue involving a lengthy line of criteria with lots of operators and brackets, I break it up like this so I can better see what's going on:

[Forms]![Frm_CARenewByName]![cboFName] = Qry_CARenew.[EmpID]    [COLOR=Red]<--backwards but should work OK I guess[/COLOR]

AND

(
Year(Qry_CARenew.[Expires]) = Year(Date())
) 

AND 

(
Month(Qry_CARenew.[Expires]) = Month(Date()) 
OR 
Month(Qry_CARenew.[Expires]) = (Month(Date()) + 1) 
OR 
Month(Qry_CARenew.[Expires]) = (Month(Date()) - 1)
)

[COLOR=Red][U][B]so:[/B][/U][/COLOR]

EXPIRE's month = yesterday's month OR today's month OR tomorrow's month 
[COLOR=red][B]but[/B][/COLOR]
EXPIRE's year = today's year

So that's fine as long as this is what you intended, but I'd imagine the red dates are not intended, if not others as well:

Excel is handy for analyzing query criteria like this. (Might look time consuming but really I just filled column A with a year of date, and plugged your criteria in the next 2 cells over, fill down, and presto! 3 minute analysis.) Like I said, I have no idea what your goal is, so maybe that's exactly the outcome you intended!

 

[sneuberg]

Since it was complaining about the date function to help you figure out what this is try running the following query and see if that works.

SELECT Date() AS Thisdate;

If that gives you an error and your references are ok another thing to try would be a compact and repair.

 

[Orthodox Dave]

I'm with sneuberg initially - check the references - particularly Microsoft DAO 3.6 Object Library. And (as I discovered recently) the sequence of ticked references gives the priority order.

Secondly Ashleedawg is right - if month(x) = 1 (January), month(x)-1 = 0 (no month at all). Wouldn't this work better:-

[Forms]![Frm_CARenewByName]![cboFName] = Qry_CARenew.[EmpID] 
AND Qry_CARenew.[Expires]<(Date()+31) AND Qry_CARenew.[Expires]>(Date()-31 )

 

[PatAccess]

Thanks all.. It was fixed. It was a combination of things
For one I had an issue with trusted location and It was a problem with my brackets and then parentheses.
ashleedawg break down was a big help

Thanks again,