Solved Need Correct Where Condition (1 Viewer)

Eljefegeneo

Still trying to learn
Local time
Today, 08:23
Joined
Jan 10, 2011
Messages
904
I have the following code that opens the report correctly:
Code:
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:
Code:
DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="[FollowUpDate] = Between DatePart('ww',-2,Date())And DatePart('ww',2,Date())"
Or
Code:
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".
 

Eljefegeneo

Still trying to learn
Local time
Today, 08:23
Joined
Jan 10, 2011
Messages
904
Did that and the error is Runtime error 307 Between Operator Without And
 

bob fitz

AWF VIP
Local time
Today, 15:23
Joined
May 23, 2011
Messages
4,717
Perhaps you should be using DateAdd() rather than DatePart(). Something like:
WhereCondition:="[FollowUpDate] = Between DateAdd("ww",-2,[FollowUpDate] ) AND DateAdd("ww",2,[FollowUpDate] )
 

Eljefegeneo

Still trying to learn
Local time
Today, 08:23
Joined
Jan 10, 2011
Messages
904
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
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:
Code:
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

MVP / VIP
Local time
Today, 15:23
Joined
Jan 14, 2017
Messages
18,186
Having read this more carefully, I agree.
If Bob's answer doesn't work, try this
Code:
DoCmd.OpenReport "rptSalesContactsOpen", acViewReport, , WhereCondition:="FollowUpDate >= DateAdd("ww",-2,Date) And FollowUpDate <= DateAdd("ww",2,Date)"
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:23
Joined
Feb 19, 2013
Messages
16,555
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;)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2002
Messages
42,986
Once you have more than one year of data, your criteria will NOT work. You'll get January 2020 and January 2021. Same problem for week number. You always have to include year in your criteria so I use Format(somefield, "yyyymm") for month and Format(somefield, "yyyyww") for week.

Format the string dates with year first otherwise they won't sort correctly if you want to sort them and they will not compare correctly if you need to use the string as criteria.

Where Format(mydate, "yyyymm") >= Format(otherdate, "yyyymm")

Because 012020 is NOT > 022019 But 202001 Is > 201902
 
Last edited:

Users who are viewing this thread

Top Bottom