A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date Help



 
 
Thread Tools Display Modes
  #1  
Old October 12th, 2004, 01:19 PM
Raj
external usenet poster
 
Posts: n/a
Default 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  
Old October 12th, 2004, 10:28 PM
John Spencer (MVP)
external usenet poster
 
Posts: n/a
Default

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  
Old October 13th, 2004, 01:13 AM
John Vinson
external usenet poster
 
Posts: n/a
Default

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  
Old October 13th, 2004, 12:53 PM
Raj
external usenet poster
 
Posts: n/a
Default

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  
Old October 13th, 2004, 12:53 PM
Raj
external usenet poster
 
Posts: n/a
Default

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  
Old October 13th, 2004, 04:09 PM
John Vinson
external usenet poster
 
Posts: n/a
Default

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  
Old October 13th, 2004, 06:01 PM
Raj
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 05:08 PM


All times are GMT +1. The time now is 02:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.