Solved Need Correct Where Condition

[Eljefegeneo]

I have the following code that opens the report correctly:

Daily Followup:  DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="[FollowUpDate] = Date()"
Weekly Followup: DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="DatePart('ww',[FollowUpDate]) =  DatePart('ww',Date()) "
Mothly Followup: DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="DatePart('m',[FollowUpDate]) =  DatePart('m',Date()) "

But when I try to set the filter for plus or minus two weeks:

DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="[FollowUpDate] = Between DatePart('ww',-2,Date())And DatePart('ww',2,Date())"

Or

DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="[FollowUpDate] = Between [FollowUpDate]('ww',-2,Date())And [FollowUpDate]('ww',2,Date())"

I keep getting a syntax error missing operator.
I don't know which code (or neither one) to use that is missing the "operator".

 

[isladogs]

Try removing the = before Between

 

[Eljefegeneo]

Did that and the error is Runtime error 307 Between Operator Without And

 

[bob fitz]

Perhaps you should be using DateAdd() rather than DatePart(). Something like:
WhereCondition:="[FollowUpDate] = Between DateAdd("ww",-2,[FollowUpDate] ) AND DateAdd("ww",2,[FollowUpDate] )

 

[Eljefegeneo]

Same error message. Syntax error missing operator.

I did a work around by putting two hidden text boxes on the form from which the report is run.

Minus2Weeks with it =DateAdd('ww',-2,Date())
Plus2Weeks with it =DateAdd('ww',2,Date())
and used the code

DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="[FollowUpDate] Between Forms!frmMainMenu!Minus2Weeks And Forms!frmMainMenu!Plus2Weeks"

and the report runs as it should.

so I tried:

DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="[FollowUpDate] Between DateAdd('ww',-2,[FollowUpDate]) AND DateAdd('ww',2,[FollowUpDate])"

And Volia!
Thanks for the suggestions. Much appreciated.

 

[isladogs]

Having read this more carefully, I agree.
If Bob's answer doesn't work, try this

DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="FollowUpDate >= DateAdd("ww",-2,Date) And FollowUpDate <= DateAdd("ww",2,Date)"

 

[CJ_London]

think you have a typo

so I tried:
Code:Copy to clipboard
DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="[FollowUpDate] Between DateAdd('ww',-2,[FollowUpDate]) AND DateAdd('ww',2,[FollowUpDate])"
And Volia!

followupdate will always be between plus or minus two weeks of itself