Working out the day from a date

[starlight]

Good Afternoon

I have a field in my table into which the user types in a date. What they also want is a field that works out and displays the day of the week from this date.

Any ideas on how best I can do this?

Thanks.

ps. They dont want to use a calendar!

 

[ColinEssex]

Bet you haven't done a search for this

 

[jon_sg]

The best method is to use a form to enter data into the tables where the date is entered through a text Box named for example txtDate. This is bound to the date field in the underlying table fldDate.

Create another text box on the form txtWeekday and bind this to fldDate or set the controlsource to txtDate. Then set txtWeekDay format to ddd to give Mon, Tues, etc or dddd to give Monday, Tuesday etc. Lock this textbox to prevent users entering any data into this control.

 

[raskew]

Try this from the debug (immediate) window.

? format(date(), "dddd")
Saturday

Adapt this as a calculated field in
a query, versus a field in your table.

HTH - Bob

 

[jon_sg]

Bob's method will work fine but a point to note with putting calculated fields directly into a query is that care is required to limit the records being returned by the query if the underlying table has many records.

I have found, through painful experience, that calculated fields in queries can seriously slow down the performance of the query apparently this is due to the way Jet applies indexes.

For this reason I always try and keep calculated controls on forms or reports, if you have no other option look to restricting the rows returned by another query prior to performing any calculations within a query.

Regards

Jon

 

[Rich]

You don't need a calculated control, as has already been pointed out, you only need to set the display format to dddd on the property sheet

 

[jon_sg]

Good point Rich - I forgot about the format option in the Query property sheet

 

[neileg]

Don't worry, chaps. I wandered over to Starlight's desk and showed her how to set the format in the form!