Display Day of Week with Date

[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?

 

[Beetle]

In the Format property of your form control put;

mm\/dd\/yyyy", "ddd

 

[JWeaver]

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?

 

[Beetle]

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?

 

[JWeaver]

No, it varies and sometimes a date isn't even given. There may be something
like "ASAP" or "See Contract" listed instead.

 

[Beetle]

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!