Get a weekdayname from a date (1 Viewer)

Ports

Registered User.
Local time
Today, 07:47
Joined
Jun 30, 2019
Messages
64
Hi,


I've got a form element (txtStartDate) that is linked to a Date field in a table. I've created another element in the form (txtStartDay), a text box, that displays a week day of the corresponding date.


This however does not work as intended. The displayed weekday does not correspond with the date for some reason. Also, the updates do not register if I change the date via a date picker. I usually have to click away and come back to the date picker or something somilar to see any change in the displayed txtStartDay.



Code:
Private Sub txtStartDate_Change()
    Dim DName As String
    Dim DIndex As Integer
    DIndex = Weekday(Me.txtStartDate)
    DName = WeekdayName(DIndex)
    Me.txtStartDay.Value = DName
End Sub
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
The weekdayname function depends on the value of the firstdayofweek setting.
By default, Sunday is day 1
However, if for example weekday starts on Monday, weekday =3 will be Wednesday.
 

Ports

Registered User.
Local time
Today, 07:47
Joined
Jun 30, 2019
Messages
64
The weekdayname function depends on the value of the firstdayofweek setting.
By default, Sunday is day 1
However, if for example weekday starts on Monday, weekday =3 will be Wednesday.


I get it. Isn't it the case for both Weekday and WeekdayName? Am I wrong in assuming that since I omitted the start day parameter in both Weekday and WeekdayName, it takes the default value of Sunday (being the first day) and therefore should be synced?... but for some reason they aren't.
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
Correct. It uses the default unless you specify an alternative.
Easy way to test your settings is to enter the following in the Immediate window
Code:
?WeekdayName(1)
I get Monday - you may get e.g. Sunday

In case you aren't aware, you can change the first day of week in Windows settings. Any changes made are immediately reflected in Access

EDIT
One other point. If you are using dates in VBA, you need to convert to US format mm/dd/yyyy or yyyy-mm-dd otherwise some dates will be interpreted incorrectly.
E.g. A UK date of 3 Aug is 03/08/2019 but vba treats that as 8 Mar. Today is 21 Aug and will be correctly interpreted even if written as 21/08/2019 as its unambiguous!
 
Last edited:

Ports

Registered User.
Local time
Today, 07:47
Joined
Jun 30, 2019
Messages
64
Thanks. I'm trying to run the following:

Code:
Option Compare Database

Option Explicit


Sub testme()
Print WeekdayName(1)
 End Sub




but I get the error:
Compile Error: Method not valid without a suitable object.


Sorry, am new to Access:)
 

Ports

Registered User.
Local time
Today, 07:47
Joined
Jun 30, 2019
Messages
64
Regarding the date conversion - how do I do it? Also, is it possible to do it in the background so administrators using the form see the UK format and it gets converted to the US format to do any calculations.
 

June7

AWF VIP
Local time
Today, 06:47
Joined
Mar 9, 2014
Messages
5,423
Your code should be:

Debug.Print WeekdayName(1)

Format() function can be used to return the day name of given date.

Format(Date(), "ddd")

or

Format(Date(), "dddd")

Also has FirstDayOfWeek and FirstWeekOfYear settings to consider.

Review http://allenbrowne.com/ser-36.html
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
Yes you can. Enter dates in your standard UK format.

When creating SQL strings in your code, enter
Code:
Format(YourDateFieldOrControlName,"mm/dd/yyyy")
 
Last edited:

Ports

Registered User.
Local time
Today, 07:47
Joined
Jun 30, 2019
Messages
64
Thanks for the help and the link (very useful). So I've changed the code to:


Code:
Private Sub txtStartDate_Change()
    Dim USADate As String
    USADate = Format(Me.txtStartDate, "mm/dd/yyyy")
    Me.txtStartDay.Value = Format(USADate, "dddd")
End Sub
Still the txtStartDay results are inconsisent. Frankly, I don't know what's happening. the Format(...) does not rely on my local system settings so it should be ok. Yet, it doesn't seem to be correct.


Also, regardless of the date format, the data picker in the form does not seem to change the displayed value for txtStartDay. One needs to click away and do it again. Is there any way to amend this behaviour?


I've attached the database file. Thanks.


edit: the attached database file has this line:
Code:
Me.txtStartDay.Value = Format(USADate, "mmm")
to check if the month is even correct, the results are inconsistent as well.
 

Attachments

  • Generator - test_v1.zip
    60.9 KB · Views: 89

June7

AWF VIP
Local time
Today, 06:47
Joined
Mar 9, 2014
Messages
5,423
The results are consistent for me because I am in U.S.

I've never utilized date picker.

Use combobox and textbox AfterUpdate event instead of Change. The calculations will trigger when control loses focus - enter or tab to another control. However, if user aborts edits (ESC), the calculations will not be triggered for either event and wrong day name is showing.

I would just have calculation in textbox ControlSource instead of VBA.

=Format([txtStartDate],"dddd")
 
Last edited:

Ports

Registered User.
Local time
Today, 07:47
Joined
Jun 30, 2019
Messages
64
The results are consistent for me because I am in U.S.
But wasn't Format(...) supposed to be working independently of the system locale? The fact that it works fine for you and not me means that it doees seem to draw on the system settings of a particular computer, doesn't it?
 

Minty

AWF VIP
Local time
Today, 14:47
Joined
Jul 26, 2013
Messages
10,354
If you are not in the US and for consistency use a function as Allen Browne suggests to format your dates in VBA

As suggested you use the after update event, NOT the on change event.

See the attached.
 

Attachments

  • Generator - test_v1.accdb
    768 KB · Views: 81

Ports

Registered User.
Local time
Today, 07:47
Joined
Jun 30, 2019
Messages
64
The results are consistent for me because I am in U.S.

I've never utilized date picker.

I would not bother saving day name to table. I would calculate when needed. I would just put expression in textbox ControlSource or in query.

Use combobox and textbox AfterUpdate event instead of Change. The calculations will trigger when control loses focus - enter or tab to another control. However, if user aborts edits (ESC), the calculations will not be triggered for either event and wrong day name is saved.


The txtStartDate in the form's control source is StartDate field in the table (which is formatted to Date) - hence the date picker on the form.



I'm not saving the txtStartDay anywhere. I just want it displayed on the form. That and the txtEndDay. The reason for that is to assist the person using the form. If they picked a StartDay that falls eg. on a Monday and the EndDay that falls on eg. a Tuesday, they can see straight away that there's a mistake somewhere. A course that starts on a Monday will always end on a Monday.
 

June7

AWF VIP
Local time
Today, 06:47
Joined
Mar 9, 2014
Messages
5,423
Yes, I finally realized not saving to table and edited my prior post but you already read it. Unfortunately I cannot test calculation for international date.

I still never have utilized date picker for input of dates. Users type the full date. I use InputMask.
 
Last edited:

Ports

Registered User.
Local time
Today, 07:47
Joined
Jun 30, 2019
Messages
64
Thanks guys. Minty's db works fine and is just simpler. I didn't know you can just type an expression (like an Excel's formula) in a text box.
 

sonic8

AWF VIP
Local time
Today, 15:47
Joined
Oct 27, 2015
Messages
998
One other point. If you are using dates in VBA, you need to convert to US format mm/dd/yyyy or yyyy-mm-dd otherwise some dates will be interpreted incorrectly.
E.g. A UK date of 3 Aug is 03/08/2019 but vba treats that as 8 Mar. Today is 21 Aug and will be correctly interpreted even if written as 21/08/2019 as its unambiguous!
I'm afraid this is incorrect and if applied as @Ports did, it makes matters worse.

VBA will use the system date format setting for parsing strings into dates. Don't touch it and you will be fine! (As long as you enter dates in the format matching you system settings!)


isladogs said:
The weekdayname function depends on the value of the firstdayofweek setting.
By default, Sunday is day 1
However, if for example weekday starts on Monday, weekday =3 will be Wednesday.
I get it. Isn't it the case for both Weekday and WeekdayName?
No! It is not! - This is causing your the problem!

With the Weekday-Function the default for FirstDayOfWeek is vbSunday
With the WeekdayName-Function the default for FirstDayOfWeek is vbUseSystemDayOfWeek

Explicitly use vbUseSystemDayOfWeek and your original code will work as expected.
 

isladogs

MVP / VIP
Local time
Today, 14:47
Joined
Jan 14, 2017
Messages
18,186
More precisely I should have said when constructing sql strings in your vba code. For example
Code:
       strDateFilter = "(IncidentDate Between #" & Format(Me.txtFrom, "mm/dd/yyyy") & _
            "# And #" & Format(Me.txtTo, "mm/dd/yyyy") & "#) "

I will edit my original answer accordingly.
 

Users who are viewing this thread

Top Bottom