|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Change Data from Now() to Date() Format - ** H E L P * *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 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 -- Show quoteDon''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 > > >
Show quote
On Nov 26, 9:58 am, "Jasmine" <lalexander***@verizon.net> wrote: First create a Back up copy of your database then open your table in> 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. Design View, click on the date field and change the format to "Short Date". 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" ) Show quote "Shiller" <shill***@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/ 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. -- Show quoteDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Jasmine" <lalexander***@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" <shill***@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/ > > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message Actually, DateValue is the way to go, or use int() which can return a long > > 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!) > 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 "Shiller" <shill***@gmail.com> wrote in message Note that this will change the way the dates are displayed, but will not 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". 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. 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. Show quote "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message news:e4HLAiEMIHA.5160@TK2MSFTNGP05.phx.gbl... > "Shiller" <shill***@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. -- Show quoteDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Jasmine" <lalexander***@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" <shill***@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.
Show quote "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" <lalexander***@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" <shill***@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, "I don't care"?
Show quote "Jasmine" <lalexander***@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" <lalexander***@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" <shill***@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 -- Show quoteDon''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" <shill***@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) > > > > >
Show quote
On Nov 26, 11:36 am, "Jasmine" <lalexander***@verizon.net> wrote: 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. > > "Dirk Goldgar" <d...@NOdataSPAMgnostics.com.invalid> wrote in message > > news:e4HLAiEMIHA.5160@TK2MSFTNGP05.phx.gbl... > > > "Shiller" <shill***@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) 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,
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 -- Show quoteDon''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. > > >  |
|||||||||||||||||||||||
Other interesting topics