Display Day of Week with Date

  • Thread starter Thread starter JWeaver
  • Start date Start date
J

JWeaver

I have a Form where a date is entered for service to begin. I need to have
the day of the week displayed also. For example, if I entered "12/02/08", I
want it to display as "12/02/08, Tue". Right now, this information is
entered manually by the user but different users are entering this data in
different ways and I want to make it consistent so that it will sort properly
in queries and on reports.

I have tried adding =Format(Date(), "dddd, mmm d yyyy") to the Format of the
field on the Form but this doesn't work.

What is the best way to do this?
 
Thanks for the reply.

I copied my database and am making these changes in the "Dummy" version
before applying them to the database that we are using to ensure that
information doesn't get lost. I had forgotten to mention that originally the
fields were set as Text fields and were converted to Date/Time fields. I
figured out how to do what I wanted after I posted my question. I set the
Format property in the Table and the Form as "mm/dd/yy ddd" and it works fine.

Since the fields were originally set as Text fields, sometimes information
was entered in Start Date field as "either 12/3/08" and End Date field as "or
12/4/08". When I converted these 2 fields to Date/Time fields I lost
everything in them, even the date portion. I decided to add a Date Note
field to capture this type of information when it is not specific to a
particular date. Is there a way that I can copy information that isn't
strictly a date to this field first so that when I convert my working
database, I don't lose this information?
 
In the fields that have additional text in them, is the Date portion always
at the right end of the string, like the two examples in your post?
 
No, it varies and sometimes a date isn't even given. There may be something
like "ASAP" or "See Contract" listed instead.
 
That's going to be a little tricky to solve. Here's something you might try.
Make a backup before you try any of the following.

Add a new field to your table called NewDate (or whatever). Make it
a text field to start with so the update doesn't fail because of data type.

Run an update query using the following function (replace [OldDate] with
the actual name of your current text field).

IIf(InStr([OldDate],"/")>0,Mid([OldDate],InStr([OldDate],"/")-2,8),Null)

Basically what this does is look for the first occurrence of / in the field,
then backs up two spaces and extracts the next 8 characters. It should work
if the dates are all in the format of mm/dd/yy (if some of them have 4
digit years, you can replace the 8 with a 10, but then you may end up with
extra characters that aren't part of the date in cases where the year is
2 digits), and as long as there are no other occurrences of text with
a / in it (like "this/that/the other").

So, the SQL might look like;

UPDATE tblYourTable SET NewDate = IIf(InStr([OldDate],"/")>0,
Mid([OldDate],InStr([OldDate],"/")-2,8),Null);

If it works, you can then convert the values in the NewDate field to Dates
using the CDate function, and then change the field to a Date/Time data type.

Good Luck!
 
Back
Top