InStr Question

G

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
 
R

Rick Brandt

GitarJake said:
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?
Click to expand...


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

(2 digit year? for shame!)
 
G

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
 
J

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))
 
T

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!)
Click to expand...

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
 
G

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

Tom Lake said:
=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!)
Click to expand...

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
Click to expand...
 
R

Randy Harris

GitarJake said:
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
Click to expand...


Jake,

Are you absolutely certain that WebDate is a text field?

Does

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

produce an error?
 
R

Rick Brandt

GitarJake said:
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")
Click to expand...

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.
 
S

Steve Schapel

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

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

Rick Brandt

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

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

Randy Harris

Rick Brandt said:
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?
Click to expand...


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.
 
S

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.
 
J

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.
Click to expand...

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]
 
T

Tom Lake

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

Not with the dates I deal with!

Tom L
 
S

Steve Schapel

Randy said:
Old habits die hard (even bad ones).
Click to expand...

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.
Click to expand...

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 ;-).
 
Top