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.


0
Jasmine
11/26/2007 2:58:45 PM
access.queries 6343 articles. 1 followers. Follow

14 Replies
1144 Views

Similar Articles

[PageSpeed] 6

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


0
Albert
11/26/2007 3:19:42 PM
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 <--
0
Stefan
11/26/2007 3:26:00 PM
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/ 


0
Jasmine
11/26/2007 3:49:14 PM
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.
> 
> 
> 
0
Utf
11/26/2007 4:00:01 PM
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
> 
> 
> 
0
Utf
11/26/2007 4:06:00 PM
"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)

0
Dirk
11/26/2007 4:21:54 PM
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/
>
> 


0
Douglas
11/26/2007 4:22:55 PM
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)
> 


0
Jasmine
11/26/2007 4:36:04 PM
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)
>>
>
> 


0
Douglas
11/26/2007 4:52:31 PM
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)
> > 
> 
> 
> 
0
Utf
11/26/2007 4:53:06 PM
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)
>>>
>>
>>
>
> 


0
Jasmine
11/26/2007 5:42:20 PM
"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


0
Albert
11/27/2007 7:52:07 PM
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)
>>>>
>>>
>>>
>>
>>
>
> 


0
BruceM
11/28/2007 4:27:44 PM
"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/
0
Tony
11/30/2007 11:36:40 PM
Reply:

Similar Artilces:

Public folders changes problem
Hi On Exchange 2007 I created public folder named "IT" and inside a calendar called "IT calnedar" that is used for all events in our IT department. It's strange when our secretary adds events into this calendar and mark them with different colors (categories) these changes (the right color for each event) are only shown in Outlook 2003 or OWA, but in Outlook 2007 all colors are mixed up. We all have Outlook 2007 in IT department, so we all see wrong colors, but when I try to log-on through OWA or with a test machine that has Outlook 2003 installed everything is O...

How do you send data to the same row of a text file everytime?
All, How do I output to the same position in a Text file everytime? I want to output data for a given Date so I'd like to put it in the same row for that Date everytime, and so overwrite the existing data in that row? I use the following command: Write #10, dateline, variable1, variable2, variable3 'where dateline is today's date in excel date code. I drop into the file at the row corresponding to last week's date e.g. row 40010. Write #10, dateline+1, variable1, variable2, variable3 'where dateline +1 is tomorrow's date in excel date code. I drop...

Lost Data Entry Field in Toolbar
In Excel 2000, I use to be able to see a data field in the toolbar area. If I entered something in the current field, it would be their too. I could also choose to enter data there. I noticed a while ago that it had disappeared. I have only just tried to restore it but not having much luck. Does anyone know how to do it or even what it is correctly called! This sounds like you're writing about the Formula bar. Make sure that View|Formula bar is checked. Alan Secker wrote: > > In Excel 2000, I use to be able to see a data field in the toolbar area. If > I entered something i...

Changing programmatically the color of a row
Like subject, is possible to change the color of an entiere row (o multiple cells) with a condition? Thanks -- masterx8 ----------------------------------------------------------------------- masterx81's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1525 View this thread: http://www.excelforum.com/showthread.php?threadid=26878 Hi rows(1).interior.colorindex=9 "masterx81" wrote: > > Like subject, is possible to change the color of an entiere row (or > multiple cells) with a condition? > Thanks! > > > -- > masterx81 > -...

Format the command for the Currency Button
This is a multi-part message in MIME format. ------=_NextPart_000_004A_01C8F258.4B91CF70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In Excel 2003, there was a way to change the commands linked to a = button. So for instance, I like making my negative numbers red, so I = reformatted the Currency button to make all the negative numbers red. I now have 2007 and I am battling. I have found a way to edit the = Currency button but it does not keep the setting if I close Excel.=20 Please can somebody advise how I can make this permanent ...

Order Data in Pivot Table Non ascending or Decending
I've had problems ordering my data in a pivot table. How do I order the data to be formatted the way I want it to be? e.g Want: Year 1 Year 2 Year 3 Sales 1000 1250 1500 Less: Cost of Goods Sold 500 450 500 Gross Profit 500 800 1000 Selling Expenses 200 400 600 Instead of: Gross Profit 500 800 1000 Less: Cost of Goods Sold 500 450 500 Sales 10...

Price List change the price element alone (CRM 4.0 Spanish)
When I add a product to the price list, I specified the price of it. Then I click under the SAVE option, and it change alone from 100.00 € to 1.000.000.00€. I am not using Discount Lists, and The Unit measurement is 1 quantity. We are working with Euro divisa. I upgraded from CRM 3 to 4 using the spanish version. Any idea about why it is failing? Check your number format in "Workspace" check the "Format-Tab" there the numberformat might be wrong (dots and commas) so it is for german (germany) not for german (austria). so wie fond another language which wasn't test...

save changes
How can I get excel to stop asking me to save changes when i didn't make any changes? ver 2003 thanks If the workbook you opened was created in an earlier version of Excel you will get the "save changes" message. Save as 2003 version. You will also get the message if you have used any volatile function(s) in your workbook. Could be a NOW() or TODAY() List of volatile functions........ AREAS() OFFSET() CELL() INDIRECT() NOW() TODAY() RAND() Any one or more of these would cause the workbook changes. Gord Dibben MS Excel MVP On Wed, 24 Jan 2007 14:41:54 -0...

change email address of contact in distribution group
Does Outlook2007 change the email address of member in a group if I have changed email address of individual's profile. When I hit UPDATE when I am in a group listing, joeblow@cox.net does not change to joeblow@cox.com even tho I have changed his email address in his individual listing. Must I update each group joeblow is in manually? Thanks for help Clicking "Update Now" will update any changed addresses of people in your Contacts list. -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.htm &quo...

Users unable to change password
We are running Exchange 2000 on a Windows 2000 server. Users are not members of the domain and never have been. They have previously been able to log into Outlook with their username and password, and change their password from the login box or using the "options" menu within Outlook. After a recent reboot, they are no longer able to change their own passwords. They get an error message about not having the required permissions to perform the request. As far as I am aware, nothing changed on the server before the reboot. I have already checked and the "Everyone" group alr...

how to change the pivot chart automaticaly as values in the worksheet change
Hi, I would like to know how I can make the pivot chart change automaticaly as I change the values in the work sheet? I also would like to know how can i merge file from such pivot chart/s? Jeevan ...

OWA and Must Change Password
We are running Windows 2003 SP1 and Exchange 2003 SP1. I have setup the Change Password feature of OWA (KB297121), and have the hotfix (included in SP1) 833734. Changing passwords via the 'Change Password' button within OWA works fine. My problem is that when the 'User Must Change Password at Next Login' is checked, the OWA login does not work. It insists the user must enter the correct user and password. The IISadmpwd change password utility works fine - even when the 'User Must change Password' box is checked. How do I get this functionality to work with O...

Need to Change Case from Mixed to Caps
Please help! I have a number of imported worksheets with some fields formatted in mixed case and some in UPPER case. I want all data to look consistent. Is there a way that I can quickly turn all of the mixed case to upper case? Word has a button to do this but I don't see one in Excel. Thanks in advance. Hi, have a look at the UPPER function in XLHelp -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Kathy" wrote: > Please ...

How to change currency
Hi Everyone, Can anyone help me out in solving the issue I would like to change the functional currency of a company, how do i do it Note: I have entered the transaction earlier with different currency GP 9.0 Thanks Asnsar Functional Currency is the first step in setting up system and it is linked to many tables. So, If you've done any transactions it is difficult to change the functional currency though you clear all data and transaction history and so on. The best way is to create a new company instead. Thanks ...

Standard Cost Roll up (using Standard Cost Changes)
Hi all, We are finding that when we change a component on a sub-assembly, the resulting roll-up is affecting other sub-assemblies that share the same parents but not the changed component. For example: We changed the cost a component item for sub-assembly 25-0051 and roll up the sub-assembly. This sub-assembly reports to our top level 30-0100 and 30-0101. The roll-up then went and changed ALL related assemblies that share the top level 30-0100 and 30-0101. These assemblies DO NOT share the changed component but do have other proposed changes since last roll/revalue. These proposed...

Pivot Table not pulling all fields or data
Hi All........ Without getting too verbose, I have a 900 row by 26 column database I am applying a Pivot Table to. The code seems to run fine, but it is not returning some of the fields, and of course the related data. I have tried reformatting the entire columns I am pulling from but no joy......however, when I reformat an errant cell from General to TEXT, that row comes in........and/or, if I change the value in one of the missing cells, it will bring that row in. The code runs fine, and was mostly derived by recording a macro in XL2k, but the data cannot be trusted becau...

change worksheet data without changing chart
I am attempting to create a scatter plot chart in Excel that will not get modified each time I sort my Excel worksheet. Does anybody know how to "lock" the chart or export it so that it is no longer linked to the worksheet? Thanks!!! Here's how to unlink a chart from its data: http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ shelly wrote: > I am attempting to create a scatter plot chart in Excel > that will not ge...

Data Validation: How to create a dynamic range in column A and use it for a drop-down list
I am trying to define a dynamic range on sheet 3, which I want to be able to use as a dropdown list as a part of the data validation "list" option. But whenever I select the drop down list after creating the validation rule (via data > validation), I cannot see my list. Perhaps I am using the wrong formula for the dynamic range? Here is what I am doing. List is in Sheet 3, rows 1-10 (and growing). Defined name is: =OFFSET('Sheet3!A1,0,0,COUNTA('Sheet3!!$A:$A)) In sheet 1, column C, I am selecting the column, then selecting data, validation, list (and placing "=n...

Worksheet Change Event
I'm looking for an easy way to track changes in a large spreadsheet Could someone help me with code to add a date into column P if any changes are made in the row. The range that this needs to apply to are is A2:N5000 I know this is a worksheet change event but don't understand the mechanics of writing the code to do what I want Hi Karen Try the below which makes use of Worksheet Change and Selection events to track real changes made to the worksheet. Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will...

How can I fill data in a list automatically?
I have a spreadsheet laid out like a list with indented items. The first 7columns contain data pertaining to an individual, while the next 5 columns contain data about events occuring to that individual. The problem is that one individual may have several events, but the personal data is filled in only once, for the first event; the first 7 columns are simply left blank for subsequent events (these are all listed following each other). there are several thousand events in this list. How can I copy the personal data so that each row is complete, with all columns filled in? K, You'...

changing details for a contact-person that another user created
Hi All, we have several departments using the CRM, and people would like to have the possibility for changing the details in a contact person that was created by another user. Normally a chief would make a contact and add it in CRM, and then the stuff takes over, and maintains those contact - but they don't have the right to edit those contact... How do I solve this? Thanx in advance, Regards, Zoran I assume you have one Business Unit. If you look at security role , there are options "Write " "Append" "AppendTo" under the tab"Core Records"...

Default Account keeps changing
I am running SBS 2003 and I have 2 Vista Laptops running Office 2007. When I set an smtp account as the default account in Outlook, after I reboot, my default email account gets changed back the Exchange Account, which I don't want. This happens with 2 different laptops belonging to 2 different users. Is there a setting in SBS that overrides this each time the laptops log onto the domain? I've gone through it and I can't find anything. You can't have both POP and Exchange in the same profile. You need to set up separate profiles. "rcolburn3@verizon.net" wrote: &g...

How do I change the color scheme of a pre-existing border in Publ.
I am using a border from those available within Publisher, and I would like to change the color scheme when I print it. What is the best way to do that? >-----Original Message----- >I am using a border from those available within Publisher, and I would like >to change the color scheme when I print it. What is the best way to do that? >. > You didn't say what version of Publisher that you are using. You didn't say what you are placing the border around. So I'm going to spend my time telling you how to put a border around a text box in Publisher 2003. Right clic...

What is an "Internal Support Function" error in e-mail?
I was copying text from another message into a new e-mail message I was creating and when I tried to 'send' it gave me the message "An internal support function returned an error". What do I do now? Not a Word problem. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Jewel" <Jewel@discussions.microsoft.com> wrote in message news:76B20FF6-B730-45B9-A5F2-5C310BED8A3F@microsoft.com... >I was copying text from another message into a new e-mail message I was > creating and when I tr...

Is it possible to change User-Agent for Outlook 2010?
Can this header be set or disabled programmatically or through registry? It is set to custom string even for traffic inside add-in's web browser control. "DamianD" <DamianD@discussions.microsoft.com> wrote in message news:2A514043-18A9-42A5-828F-0283A1897FB3@microsoft.com... > Can this header be set or disabled programmatically or through registry? It > is set to custom string even for traffic inside add-in's web browser > control. I don't know of any way to alter that value. -- Brian Tillman [MVP-Outlook] Damien Did you read the a...