InStr Question

[GitarJake]

Hi All,

In the text string below, I want to delete the time stamp and then convert
it to a date type in the English US format:

Jan 22 03:45:00 2004

to

01/22/04

Could someone please point me in the right direction?

TIA,

Jake

 

[Rick Brandt]

Hi All,

In the text string below, I want to delete the time stamp and then
convert it to a date type in the English US format:

Jan 22 03:45:00 2004

to

01/22/04

Could someone please point me in the right direction?

=Format(CDate(Left(YourString, 7) & Right(YourString, 4)), "mm/dd/yy")

(2 digit year? for shame!)

 

[GitarJake]

Rick,

(BTW: Access 2003)

The name of my string is the table field name WebDate. So, on a control in
a form, should the control source be as below?

=Format(CDate(Left([WebDate],7) & Right([WebDate],4)),"mm/dd/yy")

This returns #Name?. The brackets are placeds automatically by Access.
What are the benefits of a 4 digit year over 2? (Newbie alert!)

I was just tryin' to be economically correct!

If I wanted 4, would the code be like this?

=Format(CDate(Left([WebDate], 7) & Right([WebDate], 4)), "mm/dd/yyyy")

Thanks,

Jake

 

[John Spencer]

Assumptions
-- Always a three character month abbreviation followed by a space.
-- Day of month is one or two characters
-- Always a 4 character year.
-- No leading or trailing spaces (if there are your Trim function around YourString)

DateValue(Left(YourString,6) & " " & Right(YourString,4))

 

[Tom Lake]

=Format(CDate(Left([WebDate],7) & Right([WebDate],4)),"mm/dd/yy")

This returns #Name?. The brackets are placeds automatically by Access.
What are the benefits of a 4 digit year over 2? (Newbie alert!)

To get rid of #Name, make sure the name of the control you're putting this
code into
isn't named WebDate. The benefit of a 4-digit year over a two digit year is
that
you can easily tell what century the date refers to. If I just give you the
digits 59
is that 1959 or 2059? You have no way to know.

Tom Lake

 

[GitarJake]

Hi Tom,

The name of the control is TextDate, and I'm still gettin #Name?.

Will this work?

=Format(CDate(Left([WebDate],7) & Right([WebDate],4)),"mmm/dd/yyyy")

Thanks,

Jake

=Format(CDate(Left([WebDate],7) & Right([WebDate],4)),"mm/dd/yy")

This returns #Name?. The brackets are placeds automatically by Access.
What are the benefits of a 4 digit year over 2? (Newbie alert!)

To get rid of #Name, make sure the name of the control you're putting this
code into
isn't named WebDate. The benefit of a 4-digit year over a two digit year
is that
you can easily tell what century the date refers to. If I just give you
the digits 59
is that 1959 or 2059? You have no way to know.

Tom Lake

 

[Randy Harris]

Hi Tom,

The name of the control is TextDate, and I'm still gettin #Name?.

Will this work?

=Format(CDate(Left([WebDate],7) & Right([WebDate],4)),"mmm/dd/yyyy")

Thanks,

Jake

Jake,

Are you absolutely certain that WebDate is a text field?

Does

=Format([WebDate], "mmm/dd/yyyy")

produce an error?

 

[Rick Brandt]

Hi Tom,

The name of the control is TextDate, and I'm still gettin #Name?.

Will this work?

=Format(CDate(Left([WebDate],7) & Right([WebDate],4)),"mmm/dd/yyyy")

When a compound expression doesn't work as expected it's a good idea to break it
down into its component pieces.

Does Left([WebDate],7) return what you expect it to?

How about Right([WebDate],4)?

Then try Left([WebDate],7) & Right([WebDate],4)).

If that looks okay then add the CDate().

If that returns the correct date then add the Format() function.

 

[Steve Schapel]

... If I just give you the
digits 59
is that 1959 or 2059? You have no way to know.

Well, to be fair, in 99% of cases it is obvious from the context. So
"you have no way to know" is seldom correct.

 

[Rick Brandt]

Well, to be fair, in 99% of cases it is obvious from the context. So
"you have no way to know" is seldom correct.

I just find two digit years to be an ill-advised and completely unnecessary way
to avoid TWO keystrokes.

A Date field is DATA. It should be entered and stored completely and
non-ambiguously, and a year happens to include four digits. Why not enter 4
digits? Speed? Storage? Two digit years is merely a convention and a silly
one. We don't allow context to determine the value of any other type of data.
Why single out dates?

 

[Randy Harris]

I just find two digit years to be an ill-advised and completely unnecessary way
to avoid TWO keystrokes.

A Date field is DATA. It should be entered and stored completely and
non-ambiguously, and a year happens to include four digits. Why not enter 4
digits? Speed? Storage? Two digit years is merely a convention and a silly
one. We don't allow context to determine the value of any other type of data.
Why single out dates?

Old habits die hard (even bad ones).

We all became painfully aware of the problem a few years ago. With the way
that life expectancy is rising this could end up being a substantial problem
for "Date Of Birth" well before we get to the next century mark.

 

[Steve Schapel]

I know what you are saying, Rick, and where there is a chance of
ambiguity I agree that great care should be taken to ensure accuracy.
As you know, dates are "stored completely" no matter what, so I guess I
am talking about the data entry and the display aspects. I agree that
two digit years is "merely a convention", and it is a convention I
generally choose to follow. There are many mere conventions like this,
for example using abbreviations like lbs to mean pounds. I find the
data easier to read if it isn't clogged up with unnecessary junk like
which century is being referred to. Yes, there is also a data entry
factor, for example my users would often find it simpler to enter
today's date as 1/1/6 and it then displays (in my applications, given my
domicile) as 01-Jan-06. I am not advocating that this is the "correct"
way to do it, or that others should do the same, but it works for me,
and feedback from my users supports this. I am certainly not against
you or Tom always using 4 digit years, but I was prompted to question
the "no way to know" statement.

 

[Rick Brandt]

I know what you are saying, Rick, and where there is a chance of
ambiguity I agree that great care should be taken to ensure accuracy.
As you know, dates are "stored completely" no matter what, so I guess
I am talking about the data entry and the display aspects. I agree
that two digit years is "merely a convention", and it is a convention
I generally choose to follow. There are many mere conventions like
this, for example using abbreviations like lbs to mean pounds. I
find the data easier to read if it isn't clogged up with unnecessary
junk like which century is being referred to. Yes, there is also a
data entry factor, for example my users would often find it simpler
to enter today's date as 1/1/6 and it then displays (in my
applications, given my domicile) as 01-Jan-06. I am not advocating
that this is the "correct" way to do it, or that others should do the
same, but it works for me, and feedback from my users supports this. I am
certainly not against you or Tom always using 4 digit years, but
I was prompted to question the "no way to know" statement.

I know that this is often pushed by users (God love em), but I just find it
humorous. I'll design a form where the user has to enter numerous 15 character
part numbers, 20 character serial numbers, a (how freakin long) shipping tracker
number and a few sentences of notes and then they'll ask why I make them enter
all 4 digits of the year.

 

[Steve Schapel]

Rick,

When I was referring to my users, I was thinking more of the
display/readability aspect rather than the data entry aspect. As
regards the data entry aspect, I usually teach my users that they don't
need to enter *anything* at all for the year, if it is the current year,
which it very frequently is - depending on the type of application of
course. So you would very often see my users simply typing 5/2 <enter>
and they end up with Access storing 38753 and their forms and reports
displaying 05-Feb-06.

 

[John Vinson]

We all became painfully aware of the problem a few years ago. With the way
that life expectancy is rising this could end up being a substantial problem
for "Date Of Birth" well before we get to the next century mark.

It already is a problem. My church database needed (until her passing
last Spring) to deal with one lady born in '97, and with one of her
great-great-granddaughters also born in '97.

John W. Vinson[MVP]

 

[John Vinson]

Rick,

When I was referring to my users, I was thinking more of the
display/readability aspect rather than the data entry aspect. As
regards the data entry aspect, I usually teach my users that they don't
need to enter *anything* at all for the year, if it is the current year,
which it very frequently is - depending on the type of application of
course. So you would very often see my users simply typing 5/2 <enter>
and they end up with Access storing 38753 and their forms and reports
displaying 05-Feb-06.

.... of 05-Feb-2006 at no cost other than an eighth of an inch of
screen space... <g>

John W. Vinson[MVP]

 

[Tom Lake]

Well, to be fair, in 99% of cases it is obvious from the context. So "you
have no way to know" is seldom correct.

Not with the dates I deal with!

Tom L

 

[Steve Schapel]

Old habits die hard (even bad ones).

Funny. A few months ago, I did some modifications to an Access
application that I had originally built in '96 (apologies for any

We all became painfully aware of the problem a few years ago. With the way
that life expectancy is rising this could end up being a substantial problem
for "Date Of Birth" well before we get to the next century mark.

I can certainly see that if I had a database where the data in a date
field spanned more than 100 years, I would take a different approach in
that specific case. Just by an accident of the databases I have worked
on so far, I don't think this has ever arisen. The players in the
soccer league include young children, starting at 5 years old, but
luckily the association has a rule no players allowed over 90, so we're
safe there ;-).

 

[Steve Schapel]

Not with the dates I deal with!

Fair enough, Tom. I guess design decisions should be based on the
specifics of the project.