Help to enter value based on Day of Week

[MonarTech]

I have table that is used to collect roster information the date field is formatted dddd/dd/mm/yyyy a second field requires the end user to insert W to designate the entry as a weekend date. The end user's often forget to insert the W (not functional) I need a way to code the field to look @ date field and update itself with a W if [DateField] is a weekend date.

A query calculates the roster hours and * the hours field by 1.5 if the W is true. The query works fine, I just want to automate the "W"

Weekday Fn perhaps? Help anybody???

 

[TessB]

There's probably a better way to do this than what I propose, but I would create a form to use for user input. And then on that form, write code to check the date in the Date Field's After Update or Lose Focus Event

IF Format(Me.[Date Field], "ddd") like "Sat" or Format(Me.[Date Field], "ddd") like "Sun"

Then

Me.[Weekend Date].[Text] = "W"

Simple enough with a form. Or you can run an update query occasionally to make sure your data is accurate using the same logic. OR you can add the logic to your query, which wouldn't guarantee that the W is always entered in the table, but would correctly calculate your data regardless.

Your calculation field would look like this (kinda) in SQL
IIf(Format([MyTable]![Date Field],"ddd") Like "Sat" or Format([MyTable]![Date Field],"ddd") Like "Sun" ,[Calculated Field]*1,[Calculated Field]*1.5) AS Calculation

Have I completely confused you now?

 

[MonarTech]

Thanks TessB...The Form update sounds like a good idea, this is where the end user "is supposed to enter the W" so if I can auto it on the form...problem will be solved...I'll have a go and update you accordingly.....Thanks again