If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date Help
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 |
#2
|
|||
|
|||
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. Raj wrote: 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 |
#3
|
|||
|
|||
On Tue, 12 Oct 2004 05:19:10 -0700, Raj
wrote: 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 |
#4
|
|||
|
|||
John,
I treid the criterion and got this message. "The Expression you entered has a function containing the wrong number of arguments." "John Vinson" wrote: On Tue, 12 Oct 2004 05:19:10 -0700, Raj wrote: 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 |
#5
|
|||
|
|||
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 Spencer (MVP)" wrote: 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. Raj wrote: 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 |
#6
|
|||
|
|||
On Wed, 13 Oct 2004 04:53:02 -0700, Raj
wrote: 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 |
#7
|
|||
|
|||
John,
Thank you.. "John Vinson" wrote: On Wed, 13 Oct 2004 04:53:02 -0700, Raj wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Filtering on custom date field | MED | Contacts | 3 | September 17th, 2004 03:05 PM |
more dates!!! | brigid | Running & Setting Up Queries | 6 | May 26th, 2004 10:59 AM |
Formatting dates in Excel | bernrunner15 | New Users | 4 | May 11th, 2004 10:32 PM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |