Generate for "Friday last week until today"

[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!!!

 

[igorin]

This actually means that it's not "from Friday Last week until Today" but:
""from Friday Last week until Yesterday"

 

[Marshall Barton]

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.

Try this kind of expression:

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

 

[igorin]

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

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.

Try this kind of expression:

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

 

[Marshall Barton]

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]);

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())

 

[igorin]

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

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]);

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())

 

[Marshall Barton]

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?

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.