Change Data from Now() to Date() Format

[Jasmine]

I've been entering data ( for some time ) in one of my field in a database
with the Now() format - eg: 11/26/2007 9:52:0a AM

I've now discovered that I should have used the standard date() format - eg:
11/26/2007

This would facilitate my record retrieval in the future.

Is there a way to do an update query that will change to format from Now()
to Date() without affecting the
the contents of the data .

Thanks for all your help.

 

[Albert D. Kallal]

Take is easy on the cross posting here. It is considered VERY RUDE to just
throw out your question to a GAZILLION newsgroups with the thought that this
helps get your question answered better, or faster.

Most intelligent people that value their time here, and help in these
newsgroups will as a GENERAL RULE just simply ignore this inconsiderate
"shot gun" approach to your posting in a zillion newsgroups.

Ok, now, a solution:

It turns out that a date field has a integer portion, and a fraction
portion. So, if you convert the date field value to a long integer, then it
will strip out the fraction portion (the time portion).

Make a backup of your database, and then simply run a update query on eh
date field as follows:


update tableName set [DateField] = clng([DateField]) where DateField is not
null

 

[Shiller]

I've been entering data ( for some time ) in one of my field in a database
with the Now() format - eg: 11/26/2007 9:52:0a AM

I've now discovered that I should have used the standard date() format - eg:
11/26/2007

This would facilitate my record retrieval in the future.

Is there a way to do an update query that will change to format from Now()
to Date() without affecting the
the contents of the data .

Thanks for all your help.

First create a Back up copy of your database then open your table in
Design View, click on the date field and change the format to "Short
Date".

 

[Jasmine]

Shiller,
That was the most simplistic update in history.

Tried the 1st example on a testdatabase but that created a increment of 1 on
each date changed - horrible.

Thanks for all your help ( without the "chatter" )

 

[Dale Fye]

Jasmine,

There are advantages to doing it both ways. Unless the field is absolutely
just going to contain a date (like a reservation date or something like
that), I will normally use Now() over Date () because that gives me more
flexibility. I can always format that date field as just the date (or just
the time) portion in a query using the DateValue( ) or TimeValue( ) functions.

The down side of using Now() is that if you want to query for records that
include today, you either have to use the datevalue function to strip the
time or modify your query so that it will include all the values for today,
something like:

Where [SomeDateField] < date() + 1

HTH
Dale

 

[Dale Fye]

Albert,

Although I agree that OP cross-post list is a little long, I've never had a
problem with cross-posting, since the responses show up in all of the groups
to which the original was posted. Since I can see the responses posted by
someone in one of the other groups, I don't know how this wastes anyones time.

On the other hand, multi-posting is really annoying.

Jasmine, see http://en.wikipedia.org/wiki/Crossposting for an explaination
of cross-posting vs multi-posting

Dale

 

[Dirk Goldgar]

First create a Back up copy of your database then open your table in
Design View, click on the date field and change the format to "Short
Date".

Note that this will change the way the dates are displayed, but will not
actually change the fact that there are times stored in the date fields. So
if your records actually contain date/time values that include the time as
well as the date, that time portion is still in there and can complicate
querying by date.

It's not clear to me from Jasmine's original post whether she wants to get
rid of the stored time portion, or whether she just wants to change the
display format.

 

[Douglas J. Steele]

While it may have been the most simplistic update in history, it didn't do
anything.

Changing the format of a field does not change its values: it strictly
changes how the values are displayed.

You MUST use an Update query if you want the values to be changed. (And if
you don't want the values to be changed, then you're not going to achieve
any improvement in your record retrieval).

Albert's answer was correct (although I would have used the DateValue
function rather than the CLng function). If that doesn't work for you, post
back the actual SQL you're trying to use.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Shiller,
That was the most simplistic update in history.

Tried the 1st example on a testdatabase but that created a increment of 1
on each date changed - horrible.

Thanks for all your help ( without the "chatter" )

 

[Jasmine]

Thanks for all the response guys - I trully appreciated it - Really.

Everything worked exactly how I wanted it to.

All I needed was to strip the time at the end of the field - simple enough.

Changing the Format to "Shortdate" was the answer -

I'm now happy as a lark.

It's incredible how many people gets involved in such a short time trying to
solve
other people's problem - I luv it.

 

[Douglas J. Steele]

In other words, your comment about wanting to "facilitate my record
retrieval in the future" wasn't correct?

As Dirk & I have pointed out, you have NOT stripped the time from the field.
You're merely hidden it: it's still there.

 

[Dale Fye]

Jasmine,

Changing the "Format" only changes the way the data is displayed. As
indicated in other posts, it does not change what data is there. So if you
want to query this particular field, and have data in that field that was
entered using the Now( ) function, then your data will still contain the time
element and will have to be queried to take that into account.

Dale

 

[Shiller]

Thanks for all the response guys - I trully appreciated it - Really.

Everything worked exactly how I wanted it to.

All I needed was to strip the time at the end of the field - simple enough.

Changing the Format to "Shortdate" was the answer -

I'm now happy as a lark.

It's incredible how many people gets involved in such a short time trying to
solve
other people's problem - I luv it.

Jasmine,

I'm glad to find out that changing the field format to "Shortdate" was
part of your solution... I really like the comment you made about
"the most simplistic update in history", best of luck with the
project.

Shiller

 

[Jasmine]

Nuff said.

In other words, your comment about wanting to "facilitate my record
retrieval in the future" wasn't correct?

As Dirk & I have pointed out, you have NOT stripped the time from the
field. You're merely hidden it: it's still there.

 

[Albert D. Kallal]

Albert's answer was correct (although I would have used the DateValue
function rather than the CLng function). If that doesn't work for you,
post back the actual SQL you're trying to use.

Actually, DateValue is the way to go, or use int() which can return a long
value.

clng() actually rounds up...and is the WRONG answer, as it will round up
some values (and the original poster mentioned this)


So, for the sake of "history" and Google's elephantine memory:

My suggestion is WRONG!

use cint(), or better use the recommend dateValue()

 

[BruceM]

In other words, "I don't care"?

Nuff said.

 

[Tony Toews [MVP]]

As Dirk & I have pointed out, you have NOT stripped the time from the field.
You're merely hidden it: it's still there.

So what this means is that when the original poster wants a report by
to be selected by date they will need to include all dates from
2007-01-01 to 2007-01-07 23:59:59.

Now I know you know this Doug and Albert. This is for Jasmine's
information.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/