Extract day of the week from a date to link to data.

[ECEK]

I want to use the expression Date() in a query to get today's date and then format this to "ddd" to give me the day of the week.

I then want to link this "ddd" to data in my table that contains the three letter days of the week.

How do I make the day become "static" in order to be able to link.

 

[pr2-eugin]

Could you give another shot at explaining the problem please?

 

[ECEK]

I have a query called "today" that has just Date() in it.

This produces todays date. I want it to produce the day of the week Mon Tue etc so that I can then link this with data (that has day of the week in a field)

At the moment I cant link them because one is a date 08/12/2014 and one is a text field "Mon"

How do I get todays date into a linkable text format?


Is that better?...sorry !!!

 

[pr2-eugin]

So what about Dates like 01/12/2014 and 24/11/2014? They are Monday's too. So the real Monday (Today) is not unique in your table is it?

To answer your question you can use WeekdayName function or Format..

? Format(Date(), "ddd")
Mon

 

[ECEK]

I have a table containing a list of people who come in on certain days.

[day_of_the_week],[staff]

MON, Peter
MON, Jason
MON, Jane
TUE, Jason
TUE, Henry
etc etc

What I want do is automatically create a query that tells me who is in on which day.
I figured I could create a query that shows todays day (everytime I open the database) and it will tell me who is in. I cant link the table to the automated Date() in the query.

 

[ECEK]

Got it !!!!

Format(Date(), "ddd")

Thank you

I shall try to word my questions better in the future.

 

[gemma-the-husky]

another way (possibly better) is to store the working days as numbers, rather than text.

the day of week (a number 1-7) is simply weekday(date), and there are constants for each day.

if weekday(date)=vbmonday etc

if weekday(somedate) = dateintable etc