Expression needed to assign a "beginning of the week date"

[sickel2]

I have a table called "BOW" that has 2 columns: BOW and EOW (for beginning of the week and end of week).



I have a table called time that has records of hours employees worked including a field for "time in".

I want to create an expression that takes the "time in" date, searches between the BOW and EOW in the BOW table, and returns the BOW for the week that the "time-in" falls under.

Can anyone tell me how to do this or point me towards a resource that can?

Thanks.

[June7]

Not sure you need BOW table. The beginning (and end) of week can be calculated for any given date. What is your beginning of week - Mon, Sun?

This returns Mon:
DateAdd("d", Date(), -(Weekday(Date())) + 2)

This returns Sun:
DateAdd("d", Date(), -(Weekday(Date())) + 1)

[ssanfu]

I have a table called time

"Time" is a reserved word and shouldn't be used for object names. Also, it would be better if object names did not have spaces.

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

[sickel2]

Thanks June7 for that formula. That formula does obviate the need for a where clause. And thanks Ssanfu for the best practices. I am a beginner and do need to edit these names.