Calculate day of week in form & store in table

[DGregg]

I need to calculate day of week based on a report date on a form and have the
data stored in the table. First off, I realize that this may be frowned upon
in that a query could be set up to calculate the day of week on an as needed
basis. This however, is not an option in that it doesn't meet the need for
how the database is to be used. I need to have both the form and table
display/store the day of week (i.e. Sunday).

I created a form in which the report date is entered. So also created a
combo box to capture the day of week in which there are 2 columns; column 1
is the translation (i.e. Sunday) and column 2 is the corresponding number for
day of week (i.e. 1) (Sunday = 1). If I enter weekday([ReportDate]) in the
control source for the combo box the value is returned on the form but is not
stored in the table. The combo box allows the english translation for the
numeric day of week value to be displayed on the form (displays as Sunday
rather than 1). I tried entering the formula (weekday([ReportDate]) in
AfterUpdate for the report date field but the day of week field is not being
updated. What am I doing wrong? Thank you in advance!

 

[John W. Vinson]

I need to calculate day of week based on a report date on a form and have the
data stored in the table. First off, I realize that this may be frowned upon
in that a query could be set up to calculate the day of week on an as needed
basis. This however, is not an option in that it doesn't meet the need for
how the database is to be used. I need to have both the form and table
display/store the day of week (i.e. Sunday).

Sorry, but no, you DON'T.

You can use a Query to calculate it. You can display this query on a Form; you
can print this query in a Report; you can export this query to an external
file; you can do *ANYTHING* with it which you can do with a table... except
one. The one thing you can't do is edit the day of the week to Sunday if the
date is October 30... but then you really wouldn't want the user to be able to
do that, would you???

If you have users viewing data in tables... well, don't. It's very limited for
the user, and this is just one of many things that you cannot do in a table.

John W. Vinson [MVP]

 

[Douglas J. Steele]

I don't see any reason why you need the week day stored in the table.

Create a query with a computed field that gives you the week day, and use
the query wherever you would otherwise have used the table.