Converting Dates to Actual Day Names

**scarames** · 04-29-2010, 02:14 PM

I have a rather large database that others fill in daily. I need to know for my reports the name of the weekday that these events occur on. I need to have the weekday name or Sat, Sun filled in automatically based on the date. Otherwise, I have to go through at the end of the month and check and edit every transaction to make sure it was done correctly. I don't know VBA very well, so bear with me if it's necessary. Thank you so much. Access 2007

**royUK** · 04-29-2010, 02:15 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Your title explains nothing

**darbid** · 04-30-2010, 06:20 AM

Originally Posted by scarames

I need to have the weekday name or Sat, Sun filled in automatically based on the date.

Have a look at the "Format" function for VBA.

You also then need to search for "User-Defined Date/Time Formats (Format Function)"

Here you will find the information you need.

eg today is 30 May 2010

Format(Date,"ddd") = "Fri"
Format(Date,"dddd") = "Friday"

by the way I assume that all people are using english but if some have windows regional setting as another language then this is the language that the day will appear in.

**split_atom18** · 04-30-2010, 06:10 PM

I would create a Select Query and put the following in a column.

For Month:

Please Login or Register  to view this content.

For Day

Please Login or Register  to view this content.

You could then set the field to = Day of the Week from your query.

Hope this helps,

Dan

**scarames** · 05-04-2010, 01:44 PM

Just got back to work. Thank you Split Atom. Going to try it today.

**scarames** · 05-04-2010, 01:46 PM

Thanks for the quick reply. As I said, I don't get into VBA often, but I'm going to try and see what happens. Thanks for replying.

**split_atom18** · 05-04-2010, 05:11 PM

This isn't really VBA. It is just using expression in a query.
Here are some links for you.

Please Login or Register  to view this content.

You create a Query Called qryMainExtended add all the fields from your table via the Astrix. Then add the code I had above in another column. What it is doing is: Performing the Weekday function on your field called "Date" which returns a number between 1 and 7, then the WeekdayName Function turns that number into the Day, the False doesn't allow it to abbreviate the day name. These functions are listed in the link below.

Some Access Function By Catagory

Below is an attached example. Now if it were my database I would delete the field called Day. There is no reason in my opinion to store this data.

Hope this helps,

Dan