Adding the day of the week to a date field

[cschieff]

Hi all,

I have a cemetery database that has a date field called date of interment (date of burial) in the form of MM/DD/YYYY. What I would like to be able to do is create a report that will select what day of the week (Monday, Tuesday, etc) a burial took place based on this date field. Currently, I have created a report that allows me to enter a start and stop date, and this report gives me all records between those two dates. For example, if I enter a start date of 01/01/1970 and a stop date of 01/01/1971, I will get back a list of all people buried between these dates. Is there a way I can add another column that will also give me the day of the week that this burial occured? Or how can I create a report so that I can type in a day of the week (for example Saturday) and get back all records of burials that took place on a Saturday?

Thanks,

Chase

[rpeare]

if you're doing it in a query you'd have to use a nested if statement like:

DayText: iif(isnull([date of burial]), null, iif(weekday([date of burial]) = 1, "Sunday", iif(weekday([date of burial]) = 2, "Monday",iif(weekday([date of burial]) = 3, "Tuesday",iif(weekday([date of burial]) = 4, "Wednesday",iif(weekday([date of burial]) = 5, "Thursday",iif(weekday([date of burial]) = 6, "Friday",iif(weekday([date of burial]) = 7, "Saturday", "Unknown"))))))))

didn't test the code so I may be off on the parentheses but you get the idea.

[June7]

Or use Format function:

[date of burial] = 1/1/1970

Format([date of burial],"ddd") = Thu
Format([date of burial],"dddd") = Thursday