I need to find value in table based on what day it is

[RickK]

I have a table that has seven fields . each field is the name of the day of the week starting with Sunday. Each field has a different value of time covered. Example , Sunday has 8 hours , Monday - Thursday has 24 and the rest of the days have 10 hours.

I need to get a result in a query of the number of covered hours depending on what day the query runs. Example, if the query was ran on Sunday the result would be 8, if it is ran on Monday the result would be 24 or if it was ran on Friday the result would be 10.

I have a query that shows me the day of the week which I got by this expression

Expr1: Weekday(Now())

Then I have each field (Day) from my table list in the same query. but now I am stuck. I am not sure where to go from here.

My table name is SysCoveredHours and a few of my field names are Monday, Tuesday, Wednesday......

How do I get the number of hours covered from any field of my table for the day that the query is ran?

Struggling here.

Thanks

 

[pbaldy]

You've made a design mistake in my view. The days of the week should be records, not fields. Then you can use a join in a query or a DLookup() to get the value for a given day. In other words:

DayOfWeek...Hours
1....8
2....24

 

[RickK]

You've made a design mistake in my view. The days of the week should be records, not fields. Then you can use a join in a query or a DLookup() to get the value for a given day. In other words:

DayOfWeek...Hours
1....8
2....24

Paul,

I am open to suggestions , so I am willing to make changes as needed.

So I made a new table named Cover , then I made two fields DayOfWeek and Hours. I entered Sunday-Saturday under DayOfWeek and the appropriate hours for each day under hours field. So how would I get the results back in a query for that day that the query is ran. If I ran it today I need the result to be Tuesdays hours.
I am in no way a access expert , hence why I am on here asking for help. I do know enough to enter what I am told, I just need guidance on what to enter.

So what would my expression need to look like in the query?

As always Thanks for your help

 

[pbaldy]

As in my example, I'd use a numeric field for the day, since Weekday(Now()) returns a number, not the text. If you want today's value:

DLookup("Hours", "Cover", "DayOfWeek = " & Weekday(Now()))

In a query you could join to that table.

 

[RickK]

As in my example, I'd use a numeric field for the day, since Weekday(Now()) returns a number, not the text. If you want today's value:

DLookup("Hours", "Cover", "DayOfWeek = " & Weekday(Now()))

In a query you could join to that table.

That worked great. Thanks

 

[pbaldy]

Happy to help!