Return on data where day of week matches dates in a table

[liammoohan]

I have a list of dates for the current month with volume of records on each day.

I need to query the data but only return all records wher the current day of the week is the same as the date.

i.e today 15/04/2016 = Friday so I only want the query to return data for the following dates:

01/04/2016
08/04/2016

 

[Eric W]

Something like this could work for you:

	A	B	C	D
1
2				Date
3	Date	Volume		4/15/2016
4	4/1/2016	1
5	4/2/2016	2		Sum of Fridays
6	4/3/2016	3		24
7	4/4/2016	4
8	4/5/2016	5
9	4/6/2016	6
10	4/7/2016	7
11	4/8/2016	8
12	4/9/2016	9
13	4/10/2016	10
14	4/11/2016	11
15	4/12/2016	12
16	4/13/2016	13
17	4/14/2016	14
18	4/15/2016	15
19	4/16/2016	16
20	4/17/2016	17
21	4/18/2016	18
22	4/19/2016	19
23	4/20/2016	20
24

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula D3 =TODAY() D5 ="Sum of "&TEXT(D3,"dddd")&"s" D6 =SUMPRODUCT((WEEKDAY(A4:A23)=WEEKDAY(D3))*B4:B23) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I just used TODAY() in D3, but you can put any date in there. The formula in D6 actually calculates the sum. In this example, it sums up 1, 8, and 15. There is no provision for excluding dates before or after any particular times, but those can be added as needed.

Let me know how this works for you.

 

[james_lankford]

except this is an Access question

 

[Joe4]

Eric, not sure if you realize, but this question was posted in the Access forum (if you are using the Zero Reply Posts listing, be sure to look over all the way to the right to see which forum the question is in).

Here is you Access solution:
- In your query, create a calculated field and set it equal to this:

Weekday([[I]DateField[/I]])

where DateField is the name of your Date field
- On the Criteria row of this calculated field, enter the following:

Weekday([Date])

- If you do not want to see this value return in your query output, simply uncheck the "Show" box (then it just uses the calculation without showing it to you)

 

[Eric W]

Apologies to both of you, normally I check.

 

[liammoohan]

Thanks - managed to get the results I was looking for ?