Update Query using date in text format?

[rstiles]

Morning everyone,

Before I even attempt to due this, I would like to run by all of you...

I have a text box in one of my tables that is formatted for text
because it contains both dates and text (these were imported for
another application so I had no control over this part). My goal is to
create an update query that would change 6 months, 1 year, 5 years, etc
to a date that would be either 6 months, 1 year, etc from the date
that was entered on this record. Would this be possible in this
situation? I am thinking about using this expression =DateAdd("m",
[status], [datecreated])

I am also thinking that I would have to use an update query to change 6
months to 6, etc then run the above query.

Will this work? Do you have any suggestions?

Thanks,

Ron

 

[John Vinson]

Morning everyone,

Before I even attempt to due this, I would like to run by all of you...

I have a text box in one of my tables that is formatted for text
because it contains both dates and text (these were imported for
another application so I had no control over this part). My goal is to
create an update query that would change 6 months, 1 year, 5 years, etc
to a date that would be either 6 months, 1 year, etc from the date
that was entered on this record. Would this be possible in this
situation? I am thinking about using this expression =DateAdd("m",
[status], [datecreated])

I am also thinking that I would have to use an update query to change 6
months to 6, etc then run the above query.

Will this work? Do you have any suggestions?

Thanks,

Ron

It won't work well, since you'll get an error message when
[datecreated] contains a non-date value. You may be able to get around
this using the IsDate function:

IIf(IsDate([datecreated]), DateAdd("m", [status], [Datecreated]),
Null)

This assumes that [status] contains the appropriate integer number of
months.

Any chance you could create a new date/time field and put only the
real dates into it? Just because it was imported with non-date data,
are you obliged to keep it incorrectly designed?

John W. Vinson[MVP]

 

[rstiles]

In this application it would be difficult to change everything to a
date value only becuase there are 70,000 plus records and atleast 80%
of them are text at this point.

 

[rstiles]

One last thing,

I am obligated keeping it the same, I was like to see this done
properly...

 

[raskew via AccessMonster.com]

Hi –

It’s not going to fly! Your last two statements are mutually exclusive.

Whoever is making the uneducated decision that “I am obligated keeping it the
same†is, in effect, depriving your organization of taking advantage of
Access/VBA’s extensive date/time functions.

Perhaps you could provide some examples of the date/text field. If the
formatting is similar, we may be able to provide some guidance on properly
breaking the date and text into two separate fields.

Best wishes - Bob

 

[rstiles]

Sorry that was my fault, that was supposed to be "I am not obligatedf"
- What it basically comes down to, I can do what ever I would like to
with this application.

 

[rstiles]

Sorry about the seperate post's...

In this particular field there are several different ways through out
the years that people have put in the data...

excluded thru 1/1/9999
excluded 1 year
excluded 5 years
1/1/9999

What I would like to do is turn all of these into dates and then
include an unbound text box in a form that will change colors when the
control is more than todays date, or something simular.

As a last resort I could change them sepertly then change the format,
but that is something that I would not look forward to.

Ron

 

[John Vinson]

Sorry about the seperate post's...

In this particular field there are several different ways through out
the years that people have put in the data...

excluded thru 1/1/9999
excluded 1 year
excluded 5 years
1/1/9999

What I would like to do is turn all of these into dates and then
include an unbound text box in a form that will change colors when the
control is more than todays date, or something simular.

As a last resort I could change them sepertly then change the format,
but that is something that I would not look forward to.

Well, there's no reason I can see NOT to add a separate Date/Time
field to the Table. Run an Update query to update it to the datevalue
of this text field if it's a date, using the same expression suggested
above. That will at least get you started.

You may be able to get the "thru" dates, or some of them, using a
criterion

LIKE "*/*"

to filter those records containing an apparent date; you may be able
to use

Iif(IsDate(Mid([datefield], InstrRev([datefield], " ") + 1)),
CDate(Mid([datefield], InstrRev([datefield], " ") + 1)), Null)

to extract any date that falls at the end of the field.

To convert "excluded 5 years" into a date will of course require some
way to determine WHEN that exclusion started - do you have some other
date field that would give you that value?


John W. Vinson[MVP]

 

[Ron S]

To convert "excluded 5 years" into a date will of course require some

way to determine WHEN that exclusion started - do you have some other
date field that would give you that value?

Not with what I imported over, the only fields that include any dates
are the date that the record was created and the status field which has
all of this mix of stuff.

Ron

 

[John Vinson]

Not with what I imported over, the only fields that include any dates
are the date that the record was created and the status field which has
all of this mix of stuff.

Ron

In that case I cannot imagine any way to fill your date field.

My drivers' license expires four years after it was issued. Could you
send me a reminder a month before then so I don't forget? Thank you.

John W. Vinson[MVP]