Solved Current week Date formatting [Query] (1 Viewer)

TecknoFreak

Member
Local time
Today, 04:31
Joined
Dec 21, 2021
Messages
47
Hello World,
I need to include in my reports all records been done from Starting day [Saturday] Ending day [Friday] = (today for example)
I have it as this Between Date()-(6+Weekday(Date(),7)) And Date()-Weekday(Date(),7)

The problem I have is that for example today, I make the report and still give me the previous week report instead of giving me this week Report starting Saturday Dec 18th, ending today Friday Dec 24th.

The Criteria I use works perfectly fine but I have to wait until Saturday (tomorrow) in order for me to get this week report even-though I always make the reports on Fridays.

How can I archive this using a Criteria?
Start day = Saturday
End day = Friday (today)


Thanks,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
18,792
Hi. To return the previous Saturday, you could try the following expression.
Code:
DateAdd("d", 1-Weekday(DateField)-1, DateField)
Hope that helps...
 

TecknoFreak

Member
Local time
Today, 04:31
Joined
Dec 21, 2021
Messages
47
Hi. To return the previous Saturday, you could try the following expression.
Code:
DateAdd("d", 1-Weekday(DateField)-1, DateField)
Hope that helps...
lets say, if Im unable to create the report today or on the weekend, will this criteria still create the report based from last [starting] Saturday and [ending] Friday?

The criteria I have I can do that but with the only problem that I need to wait until tomorrow to create the report based on current week including today records.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
18,792
lets say, if Im unable to create the report today or on the weekend, will this criteria still create the report based from last [starting] Saturday and [ending] Friday?

The criteria I have I can do that but with the only problem that I need to wait until tomorrow to create the report based on current week including today records.
Hi. The expression I gave you is supposed to return the previous Saturday. If today was Saturday, it will return last Saturday. If today was Sunday, then it will return yesterday. If you're saying you still want it to return the previous Saturday when you run the report on a Sunday, then you need to make some adjustments to it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
18,792
Okay, you could try to use this expression to return the "previous" Saturday, no matter when you run the code, until the following Friday.
Code:
dateadd("d",1-weekday(dateadd("d",1-weekday(today,6)+1,today)),dateadd("d",1-weekday(today,6),today))
PS. "today" was the variable I used to test it out. Replace it with your date field's name or use Date().
 

TecknoFreak

Member
Local time
Today, 04:31
Joined
Dec 21, 2021
Messages
47
Okay, you could try to use this expression to return the "previous" Saturday, no matter when you run the code, until the following Friday.
Code:
dateadd("d",1-weekday(dateadd("d",1-weekday(today,6)+1,today)),dateadd("d",1-weekday(today,6),today))
PS. "today" was the variable I used to test it out. Replace it with your date field's name or use Date().
Maybe I'm stupid or some but it gave me a date type mismatch in criteria expression. I'm sorry I'm soo new to this. :(
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
18,792
Maybe I'm stupid or some but it gave me a date type mismatch in criteria expression. I'm sorry I'm soo new to this. :(
Please post the exact code or sql you used.
 

TecknoFreak

Member
Local time
Today, 04:31
Joined
Dec 21, 2021
Messages
47
Please post the exact code or sql you used.
Code:
SELECT Count(*) AS TotalParts, [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail
FROM [IAAIMS DATA ENTRY TABLE]
WHERE ((([IAAIMS DATA ENTRY TABLE].[Date Started])=DateAdd("d",1-Weekday(DateAdd("d",1-Weekday("today",6)+1,"today")),DateAdd("d",1-Weekday("today",6),"today"))))
GROUP BY [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail;
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:31
Joined
Sep 21, 2011
Messages
10,352
Read post 5 again, but carefully
 

TecknoFreak

Member
Local time
Today, 04:31
Joined
Dec 21, 2021
Messages
47
Read post 5 again, but carefully
I did that
Code:
PS. "today" was the variable I used to test it out. Replace it with your date field's name or use Date().
I replaced it with the Field name and even with Date() and still gave me date type mismatch in criteria expression error

I did these
Code:
dateadd("d",1-weekday(dateadd("d",1-weekday(Date(),6)+1,Date())),dateadd("d",1-weekday(Date(),6),Date()))
I replaced "today" with Date()

I feel so dumb right now. :cry:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
18,792
Code:
SELECT Count(*) AS TotalParts, [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail
FROM [IAAIMS DATA ENTRY TABLE]
WHERE ((([IAAIMS DATA ENTRY TABLE].[Date Started])=DateAdd("d",1-Weekday(DateAdd("d",1-Weekday("today",6)+1,"today")),DateAdd("d",1-Weekday("today",6),"today"))))
GROUP BY [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail;
Hi. Thanks for posting the SQL statement. But, didn't you say you wanted to get all the dates between last Saturday and this Friday? If so, what happened to your "Between" criteria? Was this one just to test the expression if it will return the last Saturday records?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
18,792
So, if you wanted all the records with [Date Started] between last Saturday and this Friday, you could try this query.
SQL:
SELECT Count(*) AS TotalParts, [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail
FROM [IAAIMS DATA ENTRY TABLE]
WHERE [IAAIMS DATA ENTRY TABLE].[Date Started] Between DateAdd("d",1-Weekday(DateAdd("d",1-Weekday(Date(),6)+1,Date())),DateAdd("d",1-Weekday(Date(),6),Date())) And DateAdd("d",1-Weekday(Date(),6),Date())
GROUP BY [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail;
Hope it helps...
PS. The above is untested. I could have trimmed too many parens out or put them in the wrong places. Please give it a shot and let us know how it goes.
 

TecknoFreak

Member
Local time
Today, 04:31
Joined
Dec 21, 2021
Messages
47
Hi. Thanks for posting the SQL statement. But, didn't you say you wanted to get all the dates between last Saturday and this Friday? If so, what happened to your "Between" criteria? Was this one just to test the expression if it will return the last Saturday records?
Correct, thats exactly what I need to do and every friday will reset to the current week as same, Start Saturday and End Friday of the current week.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:31
Joined
Oct 29, 2018
Messages
18,792
Correct, thats exactly what I need to do and every friday will reset to the current week as same, Start Saturday and End Friday of the current week.
Did you try the SQL statement I just posted?
 

TecknoFreak

Member
Local time
Today, 04:31
Joined
Dec 21, 2021
Messages
47
So, if you wanted all the records with [Date Started] between last Saturday and this Friday, you could try this query.
SQL:
SELECT Count(*) AS TotalParts, [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail
FROM [IAAIMS DATA ENTRY TABLE]
WHERE [IAAIMS DATA ENTRY TABLE].[Date Started] Between DateAdd("d",1-Weekday(DateAdd("d",1-Weekday(Date(),6)+1,Date())),DateAdd("d",1-Weekday(Date(),6),Date())) And DateAdd("d",1-Weekday(Date(),6),Date())
GROUP BY [IAAIMS DATA ENTRY TABLE].Part, [IAAIMS DATA ENTRY TABLE].PassFail;
Hope it helps...
PS. The above is untested. I could have trimmed too many parens out or put them in the wrong places. Please give it a shot and let us know how it goes.
OMG OMG!!!!!!!!!!! It worked, IT WORKED!!!!!!!!!!!!!!!! :eek::love::love::love::love::love::love:
 

Users who are viewing this thread

Top Bottom