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!

On Tue, 30 Oct 2007 14:08:01 -0700, DGregg <(E-Mail Removed)>
wrote:

>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]

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"DGregg" <(E-Mail Removed)> wrote in message
news:B18C3C56-C9DE-4B23-AECF-(E-Mail Removed)...
>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!