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.

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



-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

hi Jasmine,

what a crosspost...

Jasmine wrote:
> 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 has nothing to do with the format of a date time value.

> 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 .
You may drop the time part in your columns using an update query like

   UPDATE [yourTable]
   SET [yourDateTime] = CDate(CInt([yourDateTime]))

Warning: This will REMOVE the time information. This is not just a 
format option.


You may otherwise use the trick form above for a select query like

   SELECT *
   FROM [yourTable]
   WHERE CDate(CInt([yourDateTime])) = #11/26/2007#

without loosing information.


mfG
--> stefan <--

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



"Shiller" <shillerc@gmail.com> wrote in message 
news:574ab3a4-1407-4da8-9a8b-a293bd18c79e@i29g2000prf.googlegroups.com...
> On Nov 26, 9:58 am, "Jasmine" <lalexander...@verizon.net> wrote:
>> 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".
> --
> Shiller Calixte
> http://AccessWiz.blogspot.com/ 

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
-- 
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"Jasmine" wrote:

> 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,

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
-- 
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"Albert D. Kallal" wrote:

> 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
> 
> 
> 
> -- 
> Albert D. Kallal    (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> 
> 
> 

"Shiller" <shillerc@gmail.com> wrote in message 
news:574ab3a4-1407-4da8-9a8b-a293bd18c79e@i29g2000prf.googlegroups.com...
>
> 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.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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
http://I.Am/DougSteele
(no e-mails, please!)


"Jasmine" <lalexanderusa@verizon.net> wrote in message 
news:eJTbmPEMIHA.1164@TK2MSFTNGP02.phx.gbl...
> 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" )
>
>
>
> "Shiller" <shillerc@gmail.com> wrote in message 
> news:574ab3a4-1407-4da8-9a8b-a293bd18c79e@i29g2000prf.googlegroups.com...
>> On Nov 26, 9:58 am, "Jasmine" <lalexander...@verizon.net> wrote:
>>> 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".
>> --
>> Shiller Calixte
>> http://AccessWiz.blogspot.com/
>
> 

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.





"Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
news:e4HLAiEMIHA.5160@TK2MSFTNGP05.phx.gbl...
> "Shiller" <shillerc@gmail.com> wrote in message 
> news:574ab3a4-1407-4da8-9a8b-a293bd18c79e@i29g2000prf.googlegroups.com...
>>
>> 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.
>
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
> 

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.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Jasmine" <lalexanderusa@verizon.net> wrote in message 
news:%23jLPxpEMIHA.3916@TK2MSFTNGP02.phx.gbl...
> 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.
>
>
>
>
>
> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
> news:e4HLAiEMIHA.5160@TK2MSFTNGP05.phx.gbl...
>> "Shiller" <shillerc@gmail.com> wrote in message 
>> news:574ab3a4-1407-4da8-9a8b-a293bd18c79e@i29g2000prf.googlegroups.com...
>>>
>>> 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.
>>
>> -- 
>> Dirk Goldgar, MS Access MVP
>> www.datagnostics.com
>>
>> (please reply to the newsgroup)
>>
>
> 

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
-- 
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


"Jasmine" wrote:

> 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.
> 
> 
> 
> 
> 
> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
> news:e4HLAiEMIHA.5160@TK2MSFTNGP05.phx.gbl...
> > "Shiller" <shillerc@gmail.com> wrote in message 
> > news:574ab3a4-1407-4da8-9a8b-a293bd18c79e@i29g2000prf.googlegroups.com...
> >>
> >> 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.
> >
> > -- 
> > Dirk Goldgar, MS Access MVP
> > www.datagnostics.com
> >
> > (please reply to the newsgroup)
> > 
> 
> 
> 

Nuff said.



"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
news:u9Gv%23yEMIHA.3976@TK2MSFTNGP03.phx.gbl...
> 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.
>
> -- 
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Jasmine" <lalexanderusa@verizon.net> wrote in message 
> news:%23jLPxpEMIHA.3916@TK2MSFTNGP02.phx.gbl...
>> 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.
>>
>>
>>
>>
>>
>> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
>> news:e4HLAiEMIHA.5160@TK2MSFTNGP05.phx.gbl...
>>> "Shiller" <shillerc@gmail.com> wrote in message 
>>> news:574ab3a4-1407-4da8-9a8b-a293bd18c79e@i29g2000prf.googlegroups.com...
>>>>
>>>> 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.
>>>
>>> -- 
>>> Dirk Goldgar, MS Access MVP
>>> www.datagnostics.com
>>>
>>> (please reply to the newsgroup)
>>>
>>
>>
>
> 

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>

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

-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

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

"Jasmine" <lalexanderusa@verizon.net> wrote in message 
news:O0i5yOFMIHA.4456@TK2MSFTNGP03.phx.gbl...
> Nuff said.
>
>
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message 
> news:u9Gv%23yEMIHA.3976@TK2MSFTNGP03.phx.gbl...
>> 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.
>>
>> -- 
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Jasmine" <lalexanderusa@verizon.net> wrote in message 
>> news:%23jLPxpEMIHA.3916@TK2MSFTNGP02.phx.gbl...
>>> 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.
>>>
>>>
>>>
>>>
>>>
>>> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
>>> news:e4HLAiEMIHA.5160@TK2MSFTNGP05.phx.gbl...
>>>> "Shiller" <shillerc@gmail.com> wrote in message 
>>>> news:574ab3a4-1407-4da8-9a8b-a293bd18c79e@i29g2000prf.googlegroups.com...
>>>>>
>>>>> 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.
>>>>
>>>> -- 
>>>> Dirk Goldgar, MS Access MVP
>>>> www.datagnostics.com
>>>>
>>>> (please reply to the newsgroup)
>>>>
>>>
>>>
>>
>>
>
> 

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote:

>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/