display the day of a date

[antonyx]

i have a field on my report that is called job date.. it is a date time field.. and displays the date as 01/01/2007

i was told that access has the ability to extract the day of that date automatically.. so if i create another textbox.. how do i format that textbox to display the day of the txtjobdate control..?

 

[KeithG]

WeekdayName(Weekday([DateValue]))

 

[boblarson]

And an alternate version:
Format([YourDateValue],"dddd")

 

[antonyx]

i put this..

Format([JobDate],"dddd")

in the control source of my unbound textbox and got this error

Syntax error (comma) in query expression

and without the comma i get another error..

and using the other method it says extra ) in expression.. ?

 

[antonyx]

i fixed it.. thanks

 

[Rich]

You don't need any code, just set the display format in a textbox to "dddd"

 

[antonyx]

yeah i figured that out.. i tend to tip toe around the pond rather then belly flop into it..

 

[Allan]

Follow Up to Displaying the day of a given date.

Hi,

antonyx described my problem perfectly and KeithG's code works except the returned value is one day out (the day after the actual date).
Can anybody provide a solution please?

I have a need to record events on numerous dates in the past (gleaned from documents) and there is also a need to identify the "day" the event occured.

I am using XP Acc2003. After trying numerous combinations from a search on this forum (including BobLarson's above), this is the only code I have been able to get a result except it returns the day following the date?
Dim TempDay As String

TempDay = WeekdayName(Weekday([ChronoDate]))
ChronoDay = TempDay

TIA
Allan

 

[RoyVidar]

Date issues is a constant PITA for some

See, over there, the week starts on Sunday (which is default for some functions), while some of the rest of us is comfortable with starting the week on a Monday.

In this case, pass either the constant representing the day your week starts on

TempDay = WeekdayName(Weekday([ChronoDate], vbMonday))

or allow it to use system settings

TempDay = WeekdayName(Weekday([ChronoDate], vbUseSystemDayOfWeek))

But, applying the format to the controls format property, as noted by Rich, is probably among the most effective.

 

[boblarson]

Just curious, I may be missing something, but couldn't you just use:

Format([ChronoDate],"dddd")

to get the day?

 

[Allan]

Thank you Roy,

I used the system suggestion and works fine now.

I will also experiment with the "better" method suggested by Rich. I had no success the first time around.

Thanks again.

Allan

 

[boblarson]

Also, if you use just set the format as Rich suggested, you put

dddd

without quotes.

 

[Allan]

Just curious, I may be missing something, but couldn't you just use:

Format([ChronoDate],"dddd")

to get the day?

Tried that Bob but kept getting the same error as antonyx. I will try again with a clearer head when I experiment with Rich's suggestion.

 

[boblarson]

Is ChronoDate truly a date field or is it a text field? That's the only thing I can think of why it wouldn't show correctly. You shouldn't have to use 2 levels of functions "WeekdayName" and "Weekday" to get the day to display. I use

dddd

in the format all of the time and I use Format([MyDateField],"dddd") all the time and it works too. Something else may be afoot.

 

[RoyVidar]

Some of us use comma as decimalseparator, which means that it cannot be used as arguement separators. We have to use semicolon in stead.

That means all the functions and expressions in the interface, as well as functions in Excel, needs to be separated with semicolon in stead of comma.

Format([MyDateField];"dddd")

 

[boblarson]

However that still wouldn't explain why

dddd

didn't work in the format field. I suspect that the quotes were included when they should not have been, and that would have been the simplest method.

 

[Allan]

ChronoDate is a date/time field. I initially had Chronoday as a date/time field but after the errors I thought I would try a text field and convert etc.

With more time permitting over the weekend (yes Roy the week 'ends' tomorrow Saturday) I will try the suggested combinations and post the results.

Thanks for your help.

Allan

 

[Allan]

Experiment with the code result.

As promised, this is for the information of Bob and Roy if you are interested, I do not expect a response. I experimented with the combinations and please excuse any errors in my description of the problem. I am a part time access user tinkering with programs developed for work and home.

I have a subform with text boxes for ChronoDate (Date/Time) and ChronoDay (Text). To get the relevant day in the Chronoday, I used the code above in the On Exit of ChronoDate.

To try the suggested code I changed CDay to a Date/Time field in the Table and the code Format([ChronoDate], “dddd”) in the OnExit event for CDate.
I also put dddd in the Format if the text box for CDay.

I then saved it and went into the main form with the above subform and entered a date in the Cdate field. On exit I received the following VB error:

“Run-time error ‘-2147352567 (80020009)’:
The value you entered isn’t valid for this field.

I then tried the code output to an unbound textbox with dddd in Format and it worked except it recorded the same day for all of the records despite the previously entered dates.

It may be obvious to some but I am lost for an explanation so I have gone back to the “two level functions” which I know works however not as efficiently.
Thanks again.

Allan

 

[Rich]

You shouldn't be storing the Day at all, it can be calculated at anytime on a form in a query or on a report from the actual date. Just add an unbound textbox, set its control source to = [ChronoDate] Set the display format on the property sheet to "dddd". You are at the minute trying to save a textual value in a Date/Time field. No other code is needed to do this in Access

 

[Allan]

Thanks Rich. Using Control Source makes the difference. Obviously the problem has been operator error!