Generate for "Friday last week until today"

  • Thread starter Thread starter igorin
  • Start date Start date
I

igorin

Hello,

I'm currently using this script...

WHERE ((([From Table1].[Date received]) Between Date()-7 And Date()-1));

....to generate the data for the past 7 days,

BUT:

I have been asked now for a report that generates the data since the
previous Friday, regardles of whether today is a Saturday, Monday or
Wednesday.

This means that if the report is generated on a Saturday then the
information would be only for Friday; and it is generated on a Tuesday then
the information would be for Friday + Saturday + Sunday + Monday.

Is there a script that can do this?

Thank you very much!!!
 
This actually means that it's not "from Friday Last week until Today" but:
""from Friday Last week until Yesterday"
 
igorin said:
I'm currently using this script...

WHERE ((([From Table1].[Date received]) Between Date()-7 And Date()-1));

...to generate the data for the past 7 days,

BUT:

I have been asked now for a report that generates the data since the
previous Friday, regardles of whether today is a Saturday, Monday or
Wednesday.

This means that if the report is generated on a Saturday then the
information would be only for Friday; and it is generated on a Tuesday then
the information would be for Friday + Saturday + Sunday + Monday.
Click to expand...


Try this kind of expression:

DateAdd("d", 1-Weekday([From Table1].[Date received], 6),
[From Table1].[Date received])
 
Marshall,

I'm getting an error message:

Data Type Mismatch in Criteria Expression

This is what I put:

WHERE DateAdd("d",1-Weekday([From ACP: Main Table].[Date received],6), [From
ACP: Main Table].[Date received]);

What could be the problem?

--
igor


Marshall Barton said:
igorin said:
I'm currently using this script...

WHERE ((([From Table1].[Date received]) Between Date()-7 And Date()-1));

...to generate the data for the past 7 days,

BUT:

I have been asked now for a report that generates the data since the
previous Friday, regardles of whether today is a Saturday, Monday or
Wednesday.

This means that if the report is generated on a Saturday then the
information would be only for Friday; and it is generated on a Tuesday then
the information would be for Friday + Saturday + Sunday + Monday.
Click to expand...


Try this kind of expression:

DateAdd("d", 1-Weekday([From Table1].[Date received], 6),
[From Table1].[Date received])
Click to expand...
 
igorin said:
I'm getting an error message:

Data Type Mismatch in Criteria Expression

This is what I put:

WHERE DateAdd("d",1-Weekday([From ACP: Main Table].[Date received],6), [From
ACP: Main Table].[Date received]);
Click to expand...


That's a pretty odd table name. I'm not sure, but the colon
might be a problem.

I don't see where the error message is coming from even if
your date received field were a Text field. OTOH, that is
an incomplete where clause. You need to compare some field
to a value, maybe something like:

WHERE [From ACP: Main Table].[Date received] Between
DateAdd("d",1-Weekday(Date(),6), Date()) And DateAdd("d",
-1, Date())
 
Hello, Marshall,

I just tried this and it's not working. I also changed the name of the table.

Do you have an idea of what could be the problem?

--
igor


Marshall Barton said:
igorin said:
I'm getting an error message:

Data Type Mismatch in Criteria Expression

This is what I put:

WHERE DateAdd("d",1-Weekday([From ACP: Main Table].[Date received],6), [From
ACP: Main Table].[Date received]);
Click to expand...


That's a pretty odd table name. I'm not sure, but the colon
might be a problem.

I don't see where the error message is coming from even if
your date received field were a Text field. OTOH, that is
an incomplete where clause. You need to compare some field
to a value, maybe something like:

WHERE [From ACP: Main Table].[Date received] Between
DateAdd("d",1-Weekday(Date(),6), Date()) And DateAdd("d",
-1, Date())
Click to expand...
 
igorin said:
I just tried this and it's not working. I also changed the name of the table.

Do you have an idea of what could be the problem?
Click to expand...


An infinitude of possibilities. To narrow it down, why
don't you describe what happened. It would probably help if
you also posted a copy/paste of the query's SQL view and any
other relevant information about the table(s?) and the
fields of interest.
 
Back
Top