Query since last (Day of week)

[BDevil15]

Hi all, I am using access 2007 and I need to run a query on a date field of all records since the last Thursday. So if its Monday I want all records from Monday, Sunday, Saturday, Friday, and Thursday. If it is Thursday I only want the records from today.

I would also like to know if its possible to do the previous week only as well, so to include only records from the previous thursday through the previous wednesday.

Thanks for any help you can offer.

[ItsMe]

Perhaps the Weekday() function combined with the WeekdayName() function will help.

[BDevil15]

Anyone have an idea on this?

[recyan]

1) Will all the consecutive dates be present in the table or Will there be some missing Dates, in the Table Date Field Values ?
2) Does using a form with 2 input unbound Date Text Boxes ( From & To ) linked to a query using BETWEEN, not serve the purpose ? If no, Why ?

Thanks

[BDevil15]

1) 99% of the time yes there will be data for consecutive days.
2) Yes... but I don't want it to work that way

[June7]

How do you want it to work? You want the query to calculate the date range parameters based on the current date? Always? You describe what should happen on Monday and Thursday, what about the other 5 days?

Also, not really clear what you mean by 'from the previous Thursday to the previous Wednesday' - only 2 days of data?

[BDevil15]

No matter what day you open it I want to see records since and including Thursday...

Additionally I would like to see records from the previous week Between Thursday and Wednesday... so the entire previous week beginning on Thursday and ending on Wednesday.

[June7]

Today is Sunday, August 24. You want to see records between Thursday, August 21 and today?

[BDevil15]

Today is sunday August 24th and I want to see records between Thursday, August 21 and today...

Additionally I would like a query for... Ok now lts still Sunday August 24th... Between Thursday August 14th and Wednesday August 20th.. thus "the previous week".

[June7]

Review http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

Use current date to calculate Thursday date. One way is to consider Thursday the first day of the week:

Date() - Weekday(Date(), vbThursday) + 1

SELECT * FROM tablename WHERE [Datefield] BETWEEN Date() - Weekday(Date(), vbThursday) + 1 AND Date();

Subtract 7 for the previous week.

SELECT * FROM tablename WHERE [Datefield] BETWEEN (Date() - Weekday(Date(), vbThursday) + 1) - 7 AND Date() - Weekday(Date(), vbThursday);

[BDevil15]

Thanks very much works great and I understand most of it but if you could just indulge me one more time. It works but I'd like to try to understand it completely.

Where does the +1 come from?

Date() - Weekday(Date(), vbThursday) + 1

Is it today minus How many days ago was Thursday Plus One day Because to me that gives me Friday

I just can not wrap my head around the +1 so obviously Im just dense. Thanks a ton for the help thus far but if you could explain that I'd really appreciate it.

[June7]

Weekday() function returns the day of the week. I set Thursday as first day of the week so Sunday is weekday 4 - 4th day of week, the 3rd day after Thursday.

8/24/2014 - 4 = 8/20/2014 (Wednesday)

Because subtraction from date does not include the given date - count back 4 starting with previous day (Saturday) and you end up on Wednesday.

Add 1 to get Thursday date of 8/21/2014.

Similar for adding days - start counting with the next day.

Test these calcs in the VBA immediate window and maybe that will help you follow. Review link at bottom of my post for debugging guidelines.