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
1177 Views

Similar Articles

[PageSpeed] 18

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:

Parsing data from one spreadsheet into another format
The data that we dump out of one machine comes in like below. %AT_1300 Bottoms|Conductivity| (Water Out) InputRange VDC1to5 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Low 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Hi 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MinScale 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MaxScale 20.0 %AT_1300 Bottoms|Conductivity| (Water Out) EngUnits mhos %AT_1300 Bottoms|Conductivity| (Water Out) StepResponseTime 1.0 %AT_1300 Bottoms|Conductivity| (Water Out) DigFiltTimeCnst 0.016 And I need to convert this data to this f...

How do you change the background color of a picture?
I have a image that I have copy and pasted, however I want to fill behind the image. I know how to fill and all that, my problem is that it recognizes the entire image as a picture, I was wondering if there is a way to change the background while the image lays on top? sureisdifferent wrote: > I have a image that I have copy and pasted, however I want to fill > behind the image. I know how to fill and all that, my problem is that > it recognizes the entire image as a picture, I was wondering if there > is a way to change the background while the image lays on top? =============...

Sort ascending, make changes, restore previous order
I've got an AutoFilter in a spreadsheet. I want to sort ascending, mak some changes to some cells, then restore the previous order. Can thi be done easily or will this require some programming?? Thanks in advane! Matt -- BVHi ----------------------------------------------------------------------- BVHis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=859 View this thread: http://www.excelforum.com/showthread.php?threadid=47508 I'd use a helper column. Put =row() and drag down. Convert it to values (edit|copy, edit|paste special|values) Do all y...

Can I send a recurring e-mail automatically each week
I want to send a e-mail with the same verbiage to the same person once a week and make it a recurrence with no end date. Can I set this up once in Outlook so it is scheduled automatically? -- Microsoft Office 2003 Version Students and Teachers Edition Windows Vista Home Premium Thank-you Happy new Year!! Carl R ...

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

Transferring over outlook data to new XP machine
How do I transfer over my old emails, address book to my new XP machine? I have looked over the internet and found nothing the tells me EXACTLY how to do this, any help would be greatly appreciated. senior_tech@yahoo.com If your using MS Outlook copy your .PST file across and import it into the new install. >If your using MS Outlook copy your .PST file across and import it into the new install. No, don't import it. Simply use "File">"Open" -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the nam...

MS Money 95 data files
I hope that some one can answer this for me. I have used MS Money 95 for years, and it works just fine for me on Windows XP, however, I now have to reformat my hard drive, and have discovered that I can nolonger find my original install disk. Will the latest versions of Money still read the MS Money 95 data files. All that I have ever used the program for is to track my investments, and am unlikely to do any different in the future. Thanks Stan B In microsoft.public.money, Stan Banner wrote: >I hope that some one can answer this for me. >I have used MS Money 95 for years, and...

Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...

How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...

New Disk storage
Hi, I'm planning on adding a new SAN to our Exchange system (Active/Passive 2 node cluster) and this has been connected to the Exchange cluster nodes and is accessible as shared disk storage. But that isn't the question I've got, basically the mail stores are currently located on a Powervault (Drive S:) and I need to present a plan for moving this data (14 databases) onto the SAN storage, I can see two options Option1) Use the ESM to migrate databases to new drive letter 1. Use exmerge to export all email to PST files (and ensure backup) 2. use the ESM utility to change the...

Options > Clean Up Now
First there is no community for Outlook Express 6 - Hopefully a Microsoft person will respond. Under 'options' > (tab) 'Maintenance' > there is a button that reads 'Clean Up Now'. The explanation reads that it will delete all downloaded messages. What the #%&!@ does that mean? Will all emails I have previously read be deleted? Is it only deleting the deleted messages? What? -- Shawn That should only apply to downloaded newsgroup messages. The outlook express newsgroup (yes there is one!) will be better able to answer the question. http://www...

Change File Locations to Private Drive (not Folder)
Hi, I know that I can use "File Locations" in "Options" so that whenever I use the "Open..." command in Word, it will open to a specific folder. I'm wondering if there is a way that this can be applied not to a folder but to a specific drive on a network. Our company has a main public drive and has also assigned each of the Staff their own private drive. Is there a way to access the "main page" (for lack of a better term) of my private drive each time I use "Open..." in Word? Right now it goes to "My Computer" or ...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

HELP! Need to export hourly sales data on POS (NOT RMS)
How can I export hourly sales data across a date range? For instance, I want to show hourly sales for the month of October so I can graph it and post it in our break room. If I can't export hourly data, can I export daily sales? The built-in reports don't address this data format. This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C826DC.CBC512D0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to load...

How can I change 'Normal' Style for Word e-mails to 'Normal (Web)'?
Hi, I'm using Word as my e-mail editor in Outlook 2003 and want to change the default Style for e-mails from 'Normal' to 'Normal (Web)'. The problem is that new e-mails and replies in HTML format use the 'Normal' Microsoft Word 'Style', and this has no gap after paragraphs. The upshot of this is that when sending an e-mail, I have to press return twice to create a gap, but when the recipient views this, their software shows it as four gaps (the extra carriage return I typed + their correctly viewed HTML carriage return after each line). E.g. I type this: ...

Phone number normalizing and auto-formatting
I really appreciate the way that Outlook auto-normalizes phone numbers - for example, here in the US, I can enter 5555555555 and Outlook automagically formats that to (555) 555-5555. Why CRM 3.0 doesn't do this out of the box is strange but if anyone has ideas on how to implement, that'd be greatly appreciated. I've worked very hard to normalize all my data before import, but I know it's a matter of time before our users (ok, our sales people) destroy all that hard work by entering garbage into every phone field entry that the system cannot auto-normalize and error ch...

Sorting Data #5
Is there formula or anyway to be able sort the below data into a format that I could create a pivot table on? I spend to many hours doing this every month. Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC Due Date: 1/16/1950 Total Payable: $100.00 Reference: Freight: Account #: 1234 Description: Name Reference 1 Amount: $100.00 Account #: 4321 Description: Name Reference 2 Amount: $100.00 Account #: 9876 Description: Name Reference 3 Amount: $100.00 Any help would be much appreciated!! You need to show a Before and After version. You still might not get any help, but your ...

formula based on format
Is there a way to have a formula that is based on a cell colour or based on the way a cell is formated? I need it to count the number of occurrences that this happens. -- Thank you for your time. Windows NT Office 97 Hi you'll need VBA. See for an example: http://cpearson.com/excel/colors.htm "James Kendall" wrote: > Is there a way to have a formula that is based on a cell colour or based on > the way a cell is formated? I need it to count the number of occurrences > that this happens. > -- > Thank you for your time. > Windows NT > Office 97 This ...

HELP! remote data not accessible msg
Hello, I have a use who is currently using a Bloomberg DDE add- in. Whenever he attempts to activate the add-in to retreive remote data, the system hangs. If I go to task manager, I then see a message stating "Remote data not accessible. To access this data Excel needs to open another program.... I have searched the knowledge base and didn't find much help. Does anyone have any ideas? I am desperate!!!! We are currently using Excel 2003 in XP Professional. TIA, Ramissah ...

Convert date
How can I convert a date in this format (mm-dd-yyyy) to a Julian-date (i.e., YYYYDDD)? For example: 09-13-2003 to 2003256 Hi see: http://www.cpearson.com/excel/jdates.htm -- Regards Frank Kabel Frankfurt, Germany gary wrote: > How can I convert a date in this format (mm-dd-yyyy) to a > Julian-date (i.e., YYYYDDD)? > > For example: 09-13-2003 to 2003256 On Wed, 8 Sep 2004 10:24:57 -0700, "gary" <anonymous@discussions.microsoft.com> wrote: >How can I convert a date in this format (mm-dd-yyyy) to a >Julian-date (i.e., YYYYDDD)? > >For exa...

formating date from general2 custom
I have an input xls file with the date but like this: Wed May 14 00:00:00 GMT-05:00 2008 I need to have just following format dd/mm/yyyy I tried to format it using Format->Cells->Date or Format->Cells- >Custom but this is failing. Nothing changed. Any ideas? Thanks for the help. Vlad <vladimir.vaynshtok@gmail.com> wrote in message news:1013b138-352b-4799-81ba-9508b74a1850@t54g2000hsg.googlegroups.com... >I have an input xls file with the date but like this: > > Wed May 14 00:00:00 GMT-05:00 2008 > > I need to have just following format > > d...

Font dosen't revert back to original after typing in date
Example I n my text I type I have December 28th The th automatically reduces to a much smaller font and once you hit the sapce bar to continue it reverts back to the larger font. It only does this some of times, why is this being so inconsistent?. I refortmated my comp. last week and when I reinstalled office I was hoping this inconsistent problem would have been resloved, but no 2nd question please. When I post a question in "discussion group" and check the box "Notify me of Replies" I never receice a reply to my question I then have to go back to ...

how do i change colour of scroll bar in worksheet
I find it very difficult to see the scroll bar in my excel workshhet as they are white, can they be changed to a colour and if so how ? Are the scrollbars white in other applications? In win98, I could change some display settings, but I couldn't change the scrollbar colors. But if the scrollbars aren't white in other applications, then this can't be the solution. Jayne wrote: > > I find it very difficult to see the scroll bar in my excel workshhet as they > are white, can they be changed to a colour and if so how ? -- Dave Peterson In word they are, but not on a...

How do I merge cells with multiple data values?
I've tried highlighting the two cells which are in the same row. It suggested that I format and align. Both of which I've tried. I keep getting the same error message, "The selection contains multiple data values. Merging into one cell will keep the upper-most data only." I need to make the cells one with all my information. Is this possible? Not knowing what you want to do, let me make a suggestion. Put all your data into the first cell and leave the second cell empty. Select both cells. Click on Format - Cells - Alignment tab. In the "Horizontal" b...

dates #5
Is there a formula to ad a numeric value to the name of a month. I am running a query in excel (pulling from an external source) and would like to sort as teh table refreshes by month and year. I have code to to do the refresh and the sort, but it sorts in alphabetical order. I was thinking a formula could place a numeric value to the month (1 = Jan, 2 = Feb, etc) and I can sort based on that. Is there an easy formula to do this. Thanks in advance Your easy formula could be: =MATCH(A1,{"Jan","Feb","Mar","Apr","May","Jun"...