Referencing a date/time field

[benlaird24]

Hi-
I want to have a field that automatically prints the day of the week based off a date inputted in another field.
This is my expression in the default value window.
=WeekdayName(Weekday(#10/10/2004#),False)
1) Instead of the date that is in there, i want it to use the date from my other field, 'Date of Event' . How do I do that?

2) When I just use the above statement, it works, but only with newly created records. I already have 100 records in my table. How do I have the date figured out for my preexisting records?

Thanks a lot. Any help is appreciated.

 

[Pat Hartman]

1/2) Add a calculated field to your query.

Select ...., Format(['Date of Event],"dddd") As EventDay
From YourTable;

 

[benlaird24]

Maybe I don't know enough about this, but can't I add the day of the week to my table without using any sort of query? I was trying to do it in the design view of my table, not with any type of query.

 

[Mike375]

Maybe I don't know enough about this, but can't I add the day of the week to my table without using any sort of query? I was trying to do it in the design view of my table, not with any type of query.

The default system only works for new records.

Tables are basically dead things. You could think of a table as warehouse where all the parts are stored. Queries, Code, Macros, Forms and Reports turns the parts into a car or whatever.

Mike

 

[scottn]

Mike is right -- the default approach will only work on a go-forward basis. however, you could write an update query to go through all your old records and make a one-time correction in the table data. after that the default will take care of any new record. that way you wouldn't have to use a query to "recalculate" the date every time you go after the data.

 

[Pat Hartman]

I was trying to do it in the design view of my table, not with any type of query

- you can't do this in the table design. Jet does not support triggers and therefore provides no way to store a calculated value based on another column. You could use - =Format(Date(),"ddd") as a default value but NOT - =Format([YourField],"ddd") because the first expression uses functions that are available to Jet but the second refers to a different column. This is not a bad thing since storing calculated values violates second normal form. BTW - Even if Jet were to allow you to set this type of default, it wouldn't update the day field if someone updated the date field. Do some research on database normalization.

scottn's solution will solve the problem of existing values. However, new rows would need to be handled with code in the form or forms you use for data entry/update. This leaves you open to update anomolies because if anyone ever updated the date or added a new row by accessing the table directly or running a query, the day field would not be calculated.

The calculation in a query solves all problems - both existing records and future records - and would be considered the "good practice" solution.

 

[benlaird24]

I understand now. I got this to work in a separate query.
One more question. I have a form where all the info in my main table is entered. I want the user to be able to type a date in, and i have another field in the table where the appropriate day of the week automatically changes. Can i put the query I made in the form? Will the dates in the query from my table automatically update? Thanks for the help.

 

[Pat Hartman]

I'm not sure that you do understand. The suggestion is to NOT store the day field. That means that you would remove the field from your table. The query containing the calculated day value should be used as the recordsource for your form. When the user changes the date value, the day will be recalculated automatically when he tabs out of the date field.

 

[benlaird24]

I'm not sure that you do understand. The suggestion is to NOT store the day field. That means that you would remove the field from your table. The query containing the calculated day value should be used as the recordsource for your form. When the user changes the date value, the day will be recalculated automatically when he tabs out of the date field.

To clarify: I have the one field date of event in my table, and I assume I need to keep it there. Based off of that date, which is entered by the user, I have the query I made convert the date to the day of the week. My day of week field is not in my table, which is what I think you suggested. My only question now is how to get that 'day of week', from my query, to be in my form. I am still confused how to do that. Thanks again for any help.

 

[Pat Hartman]

Use the querydef as the RecordSource for the form rather than using a table. Just type the query name in the RecordSource property or choose it from the list. Your calculated field can then be bound to a control.