Date - conversion to the name of the day in another field

[ondrejruz]

Hello. I am a beginner in Access. I have a form and it has 2 fields: Date and Name of the Day. I select a date in the Date field. How can I make sure that the appropriate name is displayed in the Names of the day field? I tried using: =If(Weekday([date])=1;[Sunday]) but it doesn't work for me... Can anyone help? Ondrej

[Minty]

In the control source of the NameDay control put =Format([YourDateField], "dddd")

A number of things.
Don't store this value, just use a query to calculate/display it. If you store it and the date changes you would have to recalculate it and store the new result, which is a recipe with potential for error or not updating.
[Date] is a very bad name for a field. It is a reserved word and a function in Access. Call it something more meaningful like SalesDate, or OpenDate etc.

[Bob Fitz]

First of all, "Date" is one of many reserved words and should not be used as the name of any object (field, control, form, report etc) A search for Access reserved words will help. InvDate, PurchDate or some thing like that would be much better as it also tells you something about the date.

Also, avoid spaces in any names that you give. Use PurchDate rather than Purch Date which would need to be enclosed in square brackets: [Purch Date]. This will save you much typing and will save you a headache or two when you forget the brackets.

Second. forms don't have fields. Tables have fields. Forms have controls (text boxes, combo boxes etc) which may or may not be bound to fields in the forms Record Source.
To get the name of the day I would use the following expression as a "Calculated Field" in the query used as the forms Record Source or as the Control Source of a textbox on the form.
You should not be saving the "Day" to a field in a table as this is a calculation. Calculations should rarely be saved, rather calculated whenever and wherever required.

Post back if you need further explanation or an example of the two different methods suggested.
WeekdayName(Weekday([NameOfDateFiel]),False,1)

[ondrejruz]

I used this function as element source but syntax error... =WeekdayName(Weekday([Datum]),False,1)

The name of the field in the db is not date but datum... The first texbox contains the date, the second texbox should contain the name of the day, i.e. the text...
Should I use the function =WeekdayName(Weekday([Date]),False,1) when leaving the first textbox, or as the source of the second textbox?
Ondrej

[Bob Fitz]

Set the Control Source property of the second text box to:
=WeekdayName(Weekday([Datum]),False,1)

EDIT:
I have attached a db using both methods for you.

[ondrejruz]

Hello. Thanks it works for me now... But.... Since I want to display any day of the week, I used the function: =WeekdayName(Weekday([Datum])). But it shows me the name of the day incorrectly, e.g. 13.10.2022 as Saturday. Unás week starts as Monday instead of Sunday, but it doesn't fit either way... You probably need some settings somewhere... Ondrej

[Welshgasman]

I used this function as element source but syntax error... =WeekdayName(Weekday([Datum]),False,1)

The name of the field in the db is not date but datum... The first texbox contains the date, the second texbox should contain the name of the day, i.e. the text...
Should I use the function =WeekdayName(Weekday([Date]),False,1) when leaving the first textbox, or as the source of the second textbox?
Ondrej

Be aware you can easily and quickly test code like this in the immediate window.

? WeekdayName(Weekday(Date),False,1)
Monday

[Bob Fitz]

Hello. Thanks it works for me now... But.... Since I want to display any day of the week, I used the function: =WeekdayName(Weekday([Datum])). But it shows me the name of the day incorrectly, e.g. 13.10.2022 as Saturday. Unás week starts as Monday instead of Sunday, but it doesn't fit either way... You probably need some settings somewhere... Ondrej

I don't understand your requirement now. What are you trying to return with: =WeekdayName(Weekday([Date]))

[ArviLaanemets]

Hello. Thanks it works for me now... But.... Since I want to display any day of the week, I used the function: =WeekdayName(Weekday([Datum])). But it shows me the name of the day incorrectly, e.g. 13.10.2022 as Saturday. Unás week starts as Monday instead of Sunday, but it doesn't fit either way... You probably need some settings somewhere... Ondrej

Optional parameter for Weekday()/Weekdayname() functions can have values from 1 or left out (week starts at Sunday), up to 7 (week starts at Saturday). To have week started at Monday, you need set the optional parameter to 2 for both of them: =WeekdayName(Weekday([Datum],2),2)

[Minty]

Just out of idle curiosity what was wrong with using the simpler format() expression I suggested, which always works regardless of the weekday setting?

[ondrejruz]

Thanks everyone for the good advice! The function =WeekdayName(Weekday([Datum];2);False;2) in the source control on TextBox2 works for me. Another question: In TextBox1 is the Date, in TextBox2 is the name of the day. Is there a way to make the day name appear on TextBox2 as soon as the date is selected in TextBox1? Without having to click on TextBox2? Ondrej

[ArviLaanemets]

Is there a way to make the day name appear on TextBox2 as soon as the date is selected in TextBox1? Without having to click on TextBox2?

I assume the source of your TextBox is a table field where date is stored, and in TextBox2 formula you use same date field. The problem is, that when you change the date in TextBox1, it is changed in textbox (a form control), not in table. The changes you made in any form control are made in table only when the record is saved - e.g. when you select another record in form, or when you run a script where a command to save record is given.

Your options to get date change reflected immediately in TextBox2 are (on fly):
a) to force saving of record in AfterUpdate event of TextBox1 (and probably refreshing TextBox 2 after that);
b) instead referring to date field in TextBox2 formula, referring to value of TextBox1 control (and probably running AfterUpdte event of TextBox1 to refresh TextBox2)

[Minty]

I'm not sure if my post (#2) is visible to anybody.
Or is there an echo in here?

[Welshgasman]

I'm not sure if my post (#2) is visible to anybody.
Or is there an echo in here?

I saw it.
I also know the feeling.

[moke123]

I'm not sure if my post (#2) is visible to anybody.
Or is there an echo in here?

Hello, hello, hello, hello, ...