Date Help

[Raj]

I have a corsstab query that give the orders completed for today and
yesterday. What I need help is on Monday have it show the totals for Friday
with me having to change the parameters.
Thanks

 

[John Spencer (MVP)]

OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

This might give us a hint and someone can show you the needed modification.

 

[John Vinson]

I have a corsstab query that give the orders completed for today and
yesterday. What I need help is on Monday have it show the totals for Friday
with me having to change the parameters.
Thanks

Try a criterion of

=DateAdd("d", IIF(Weekday(Date() = 2, -3, -1), Date())

Note that if Monday or Friday is a holiday this won't give the desired
result - it will show the (nonexistant) Monday orders on Tuesday,
rather than the previous Friday's orders.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Raj]

John,
I treid the criterion and got this message.

"The Expression you entered has a function containing the wrong number of
arguments."

 

[Raj]

Here is the SQL statment:
TRANSFORM Count(MASTERDATA.circuitid) AS CountOfcircuitid
SELECT MASTERDATA.ratm_contact
FROM MASTERDATA
WHERE (((MASTERDATA.ratm_contact) Is Not Null) AND
((Format([research_completed],"Short Date"))=Date()-3 And
(Format([research_completed],"Short Date")) Is Not Null) AND
((Year([research_completed]))=2004)) OR (((Format([research_completed],"Short
Date"))=Date()))
GROUP BY MASTERDATA.ratm_contact
PIVOT Format([research_completed],"Short Date");

 

[John Vinson]

John,
I treid the criterion and got this message.

"The Expression you entered has a function containing the wrong number of
arguments."

\
Sorry... missed a parenthesis:
=
DateAdd("d", IIF(Weekday(Date()) = 2, -3, -1), Date())


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Raj]

John,
Thank you..

\
Sorry... missed a parenthesis:
=
DateAdd("d", IIF(Weekday(Date()) = 2, -3, -1), Date())


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps