Get a field to show day of week (1 Viewer)

[YNWA]

Hi, I have a form which has 2 fields called Date and Day of Week.

I used a calendar for the Date field and a drop down menu for the Day of Week field.

Instead of a drop down with days in it, I have been asked to create a field that will show the Day of Week, based on my Date field selection.

So if I selected todays date 20/7/09, the Day of Week field would show as Monday.

This is needed for a pay slip type form.

Any help?
Cheers
Will

 

[DCrake]

The easiest way is to use the Format(Date,"dddd") to get Monday or format("ddd",Date) to get Mon

David

 

[YNWA]

The easiest way is to use the Format(Date,"dddd") to get Monday or format("ddd",Date) to get Mon

David

Do I format this in the Day of Week field?

How do I relate the date selected to the day of the week field to show that day in question?

 

[DCrake]

If using on a form

Me.TxtDayOfWeek = Format(Me.TxtDateEntered,"dddd")

in a query

DayOfWeek:Format([YourDateField],"dddd")


David

 

[gemma-the-husky]

in a date field, you can just change the display format to show the date any way you want

any combination of these, plus am pm, and stuff like that
"dddd dd/mmm/yy hh:mm:ss" or
"dddd dd/mm/yy hh:mm:ss"

dddd - shows day in full
ddd - abbreviated day (3 chars)
dd - date as 2 digit number

mmmm - month in full
mmm - abbreviated month (3 chars)
mm - month as 2 digits

----------
note that to test which day it is, you also have weekday function

if weekday(anydate) = vbmonday etc

 

[YNWA]

I don't think you guys understand what I mean.

Let me put it another way...

I have 1 field called Date of Session and 1 field called Day of Week both on a form.

The user selects the date as normal, they then select the day of the week that date falls on using a combo box with Mon,Tue etc... in it.

I have been asked to scrap the combo box and to enable the Day of Week field to display the day according to what is selected in the Dat of Session field.

I think it will then be printed as a wage slip so saves someone making a mistake and some time by having the Day of Week fall automatically from the Date of Session.

I dont need to format the date to display a certain way, I just need the Day of Week field to show the day, based on what date is selected in the previous field.

 

[DCrake]

I think you are bogged down with the word Format. What we are saying is you can display different elements of a date, be it the day of the week, the month in words then number of days since 1st Jan, etc by using the format function. If the Session Date is say today 20/09/2009 and we want to know what day of the week it was we would use Format("dddd",#20/09/2009#) and the answer would be ..... Monday...

David

 

[Scooterbug]

By telling a field to format the Date of Session, you are in essence asking it to determine the day of the week. Hence:

If using on a form

Me.TxtDayOfWeek = Format(Me.TxtDateEntered,"dddd")

in a query

DayOfWeek:Format([YourDateField],"dddd")


David

Will give you the day that the Session Date falls on. If you are looking to store the day...there is no need.

 

[YNWA]

Ah, I get you now sorry about that.

So do I put this in the "validation rule" part of the Day of Week field properties or somewhere else?

 

[Scooterbug]

I would put it on the after update event of the Date of Session field.

 

[gemma-the-husky]

out of interest, when you say

The user selects the date as normal,

how do they do it - do they type it in, or use a date picker?

date picker is easiest.

---------------
Access (VBA) can do lots more for you actually

1. you can do what you are doing without any code at all (ie no afterupdate code needed).

in the DAY field, just make the controlsource

=[sessiondate]

and make the format "dddd"

now, whenever you change the session date, this field will refresh automagically

----------
or JUST WITH ONE FIELD

in fact you dont even need to do this!

just format the sessiondate field as

"dddd dd/mm/yyyy"

and you can enter 17/7/09

but it will display "Friday 17/07/2009"

----------
or LIMIT WHAT THE USER CAN DO

and if you want to restrict the date to say a monday, then in the beforeupdate event of the sessiondate you can say

sub sessiondate_beforeupdate(cancel as integer)

if weekday(sessiondate)<>vbmonday then
 msgbox("You must enter a Monday")
 cancel = true
 exit sub
end if

end sub

this wil stop the user entering any date UNLESS its a Monday - the cancel line refuses the edit, and wont leave the field UNTIL you enter a Monday

 

[YNWA]

I would put it on the after update event of the Date of Session field.

Not working for some reason.

I put the code in and when I returned to form view, the day of week did display the day of week, however when I change the date it will not change the day.

Also if I go to new record when I select the date, nothing displays in the day field?

Any ideas?

 

[YNWA]

out of interest, when you say



how do they do it - do they type it in, or use a date picker?

date picker is easiest.

---------------
Access (VBA) can do lots more for you actually

1. you can do what you are doing without any code at all (ie no afterupdate code needed).

in the DAY field, just make the controlsource

=[sessiondate]

and make the format "dddd"

now, whenever you change the session date, this field will refresh automagically

----------
or JUST WITH ONE FIELD

in fact you dont even need to do this!

just format the sessiondate field as

"dddd dd/mm/yyyy"

and you can enter 17/7/09

but it will display "Friday 17/07/2009"

----------
or LIMIT WHAT THE USER CAN DO

and if you want to restrict the date to say a monday, then in the beforeupdate event of the sessiondate you can say

sub sessiondate_beforeupdate(cancel as integer)
 
if weekday(sessiondate)<>vbmonday then
 msgbox("You must enter a Monday")
 cancel = true
 exit sub
end if
 
end sub

this wil stop the user entering any date UNLESS its a Monday - the cancel line refuses the edit, and wont leave the field UNTIL you enter a Monday

Hi, I am using a calendar to pick the date.

Done this

=[sessiondate]

and make the format "dddd"

But nothing happens. Not sure if I am putting the format "dddd" in the correct place.

Dont need this one

LIMIT WHAT THE USER CAN DO

and dont think they want it like this

JUST WITH ONE FIELD

Any ideas on why the first one wont work?

 

[gemma-the-husky]

my fault - the format should be

dddd (no punctuation)


NOT

"dddd" (just gives you dddd)

 

[YNWA]

my fault - the format should be

dddd (no punctuation)


NOT

"dddd" (just gives you dddd)

Works a treat, thank you.

One problem, how do I store this field if I am using the control source?

 

[gemma-the-husky]

you dont need to store the day at all (is that what you mean) - you have the session date (i presume you store that - you sohuld, if its important), and you just get the day from that anytime you need it.

you shouldn't store anything that you can calculate from something else (there may be specialised intentional exceptions to this - where the effort to perform the calcluation is unreasonable large, but this isnt an example where you would store both)

 

[YNWA]

you dont need to store the day at all (is that what you mean) - you have the session date (i presume you store that - you sohuld, if its important), and you just get the day from that anytime you need it.

you shouldn't store anything that you can calculate from something else (there may be specialised intentional exceptions to this - where the effort to perform the calcluation is unreasonable large, but this isnt an example where you would store both)

Yea I was thinking that myself. I will ask the manager if they need the day storing. As its for staff payments, not sure if they day is important.

I have changed the format so it shows the date and day in the Date of Session field, therefore it stores both sets of info in the one field. Looks a bit better and saved on time and space also.

Would you agree that the day and date way in 1 field only is the best way to go?

 

[gemma-the-husky]

well a date is a given - you can always derive the day of week from the day, so I dont think anyone would ever store the day as a separate field.

The only reason to store the day itself i can think of is if you want to join a table to another table, based on the day of week. If you store the day you can do it directly (and have it indexed for efficiency), If you dont you need an extra query to evaluate the weekday from the date, and then you use that in your join. (and you lose the efficiency)

 

[moyi]

my fault - the format should be

dddd (no punctuation)


NOT

"dddd" (just gives you dddd)

Thank you, I was looking for this too!