Date To Weekday 1

[tj007]

Hi

I have two fields surv_date & day. Once I type the date (01/05/03) in the surv_date field, I would like to populate the day field automatically with the weekday based on the surv_date.

I am using this function =Format([Surv_Date],"dddd") as the control source to show the actual day of the surv_date field. The day field is being populated with the correct weekday. However, the data from the day field can only be seen in the form field and the datasheet view of the form. The contents of the day field does not populate the day field in the database. Is there a procedure to populate the day field in the database with the data from the form?

 

[PHV]

The form field must be bound to the table field.
Move the =Format([Surv_Date],"dddd") stuff to the AfterUpdate event procedure of Surv_Date.
Anyway it'considered bad practice to store in database derived or calculated value (3rd normal rule, if I remember correctly).

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tj007]

PHV

I am trying to add some automation. The user will type the day in the day field anyway. So storing the derived value in the database is a must. So can I create a txtbox and bound it to the table then use this function.

 

[tj007]

Moving the =Format([Surv_Date],"dddd") stuff to the AfterUpdate event procedure of Surv_Date did not provide the correct results. Why would I move this to the Surv_date field when I am trying to update the day field?

 

[KenReay]

Hi

Becuase surv_DayOfWeek will only change when surv_date changes, the after updat event of Surv_Date is the event which will detect that change, hence you need:

[surv_DayOfWeek] = Format([Surv_Date],"dddd")

in the after update event of the control, Surv_Date

But..

as PVH has already pointed out to you, it is not a good idea to store a derived value in this way, the fact that you are having these problems in keeping it synchronised demonstrate perfectly why it is not a good idea. The value can be derived quite simply in a query for presentation on a form, report of use in calculation etc etc, so why store it?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tj007]

Thanks for the explanation Ken

Ah!!!You are correct. I will not store the value. I will show the day as a calculated field with Crystal.