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
|
|||
|
|||
Generate for "Friday last week until today"
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!!! -- igor |
#2
|
|||
|
|||
Generate for "Friday last week until today"
This actually means that it's not "from Friday Last week until Today" but:
""from Friday Last week until Yesterday" -- igor "igorin" wrote: 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!!! -- igor |
#3
|
|||
|
|||
Generate for "Friday last week until today"
igorin wrote:
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]) -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Generate for "Friday last week until today"
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" wrote: igorin wrote: 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]) -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
Generate for "Friday last week until today"
igorin wrote:
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()) -- Marsh MVP [MS Access] |
#6
|
|||
|
|||
Generate for "Friday last week until today"
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" wrote: igorin wrote: 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()) -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
Generate for "Friday last week until today"
igorin wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|