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!
--
_________
Sean Bailey
"JWeaver" wrote:
> No, it varies and sometimes a date isn't even given. There may be
something
> like "ASAP" or "See Contract" listed instead.
> --
> JWeaver
>
>
> "Beetle" wrote:
>
> > In the fields that have additional text in them, is the Date ****tion
always
> > at the right end of the string, like the two examples in your post?
> > --
> > _________
> >
> > Sean Bailey
> >
> >
> > "JWeaver" wrote:
> >
> > > 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 ****tion. 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?
> > > --
> > > JWeaver
> > >
> > >
> > > "Beetle" wrote:
> > >
> > > > In the Format property of your form control put;
> > > >
> > > > mm\/dd\/yyyy", "ddd
> > > > --
> > > > _________
> > > >
> > > > Sean Bailey
> > > >
> > > >
> > > > "JWeaver" wrote:
> > > >
> > > > > 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 re****ts.
> > > > >
> > > > > 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?
> > > > > --
> > > > > JWeaver
|
