Re: Formula in MS Query #2

Thank you for your help. MS Query results in error "unrecognized function
name in MS Query", when I use INT(Filename.Time)

In MS Query, use the expression:  INT(Filename.Time)
This will return all dates with time set to zero.

Krish wrote:
> Unfortunately the format command wiill not allow grouping of the dates,
> since time is appended with date in every field.
>
> You could import the Time field in its Date/Time format, then reformat
> the column in Excel
>    (Format>Cells, Number tab -- Date category)
>
> Krish wrote:
>
>>I am trying to import external data into Excel spreadsheet. One of the
>>tables in the external data has a field "Time" for Date and time of
>>transaction. In MS query I inserted a new column with a new name and
>>inserted a formula Left(Filename.Time,11). The result came as "Dec
>
> 2,2003",
>
>>whereas I wanted this to show as "12/02/2003", in the true date format..
>>What function I should use to get the desired result from MS Query.
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


0
Krish (9)
12/3/2003 3:31:10 AM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
340 Views

Similar Articles

[PageSpeed] 24

Reply:

Similar Artilces:

Formula not being stored any more
Recently, Excel has stopped storing certain formulae in the formula bar. For example, if I type in say "=3*1/10" Excel will store "=0.3" in the formula bar. This is most inconvenient as I want to be able to tell what the constituent parts of the calculation are. It never used to do this so have I accidentally set an option on somewhere? How do I turn it off again? I couldn't duplicate this. If I typed: =3*1/10 and hit F9 (calculate)--not enter I got: 0.3 You're not getting close to the F9 key (with only portions of your formula selected? (Yeah, I did...

How can I insert a cell reference in a footer (eg for variable foo #2
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = .Range("A1").Text End With End Sub This code should go in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "wngg001" <wngg001@discussions.microsoft.com> wrote in message news:8A0F9D9E-269F-45CF-A6E3...

Test Mail...Please Ignore #2
Testing NNTP Client, Please ignore message Thanks ...

Days Old formula?
Hi im having a problem trying to figure out the forumla for days old. M teacher wants us to come up with a formula for the age of 2 dates. Does anyone know any formulas that will work? -- Message posted from http://www.ExcelForum.com If you need the days only, subtract =A1-A2 -- Regards, Peo Sjoblom "frackskat004 >" <<frackskat004.11hxa0@excelforum-nospam.com> wrote in message news:frackskat004.11hxa0@excelforum-nospam.com... > Hi im having a problem trying to figure out the forumla for days old. My > teacher wants us to come up with a formula for the age o...

Problems with MS-DateTime-Picker 6.0
My userform has a Multipage control on top. My Microsoft Date-Time-Picker 6.0 Control is sitting on "Page 1" of the Multi-page control. If I change to "Page 2" and try to access the ".Value" of the DTPicker control, it will return an error value. (It is returning the year 1899). What is wrong here? Does the DTPicker need to be on the visible page to be used?? Are there any other things I should know about this DTPicker control? Robert, You could assign the DTPicker's value to a public date variable in its change event. HTH, Be...

Drop down menu #2
Is is possible when you have a drop down menu, to have excel short list the menu as you type in more charcaters of the text. For example a have a list of several hundred customers. I start inputting the first few letters of the customers name, I would like that list of several hundred to be shortened to only include thaise customers that begin with the letters I have typed in. Thanks Think this previous post by Debra Dalgleish would be of help: "Although data validation doesn't support autocomplete, there's a sample file here that provides a combobox from which you can se...

Viewing Formulas instead of formula results
I want to view all of the formulas in my worksheets without going through each cell and typing " " around each formula. Is there any way to do this so I can check all of my formulas at once? Meghan, here is one way, use Ctrl and ~, this will toggle between formulas and results -- Paul B Always backup your data before trying something new Using Excel 97 & 2000 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Meghan" <mmckee15@yahoo.com> wrote in message news:00db01c351f6$4eb0...

Associated external Account #2
Dear all, Just got some of our exch2k users migrated to another org exch2k3. Found that associated external account rights which never seen before in 2k. Wish to know the purpose of this account and when is the best time to use it. Please help and thousand thanks. regards Granting Access to External Accounts http://www.microsoft.com/technet/prodtechnol/exchange/guides/WorkingE2k3Store/8c4befe3-3815-4b6b-a759-1e5a2878499d.mspx -- Bharat Suneja MCSE, MCT -------------------------------- "Elton Seng Yan Thung" <sengy01@pd.jaring.my> wrote in message news:enFBAvpwFHA...

virus scanning #2
I really get prompted for "requesting virus scan", and I don't know ho to disable it; the warning is located beside ready, in Excel bar -- Gerbati ----------------------------------------------------------------------- Gerbatin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1523 View this thread: http://www.excelforum.com/showthread.php?threadid=27651 What antivirus software do you use? I think/hope the solution will involve a setting that you can toggle in that program. (maybe you missed an option???) The antivirus program doesn't have t...

2 questions about Exchange Server 2000
1. How, or where, do I find out where Service Pack level I have installed? I see that I downloaded SP3, but I can't tell if it was ever installed. 2. Clients have Outlook 2000. Inbox is open. Last message is selected. No new mail. Then, if you click on a different message, you get the "new mail has arrived..." message. It seems to only deliver new mail when you change messages. Any thoughts? 1. In Exchange System Manager, highlight the Servers container. It should show you all servers in the right pane along with their service pack level. 2. Sounds like something is b...

Turn data #2
Is there a way to turn the data in an Excel spreadsheet so that what is on the top is now on the side and what is on the side is now on top? I need to export my spreadsheet into Access but, I can't get it to format correctly in its current orientation. ~Make a backup copy of your data to work from (just in case, so you don't lose data) ~Highlight the entire range of data; click >Edit >Copy ~Choose a new location, on a different sheet perhaps; click >Edit >Paste Special and select the Transpose box; click OK Does that do it for you? thank you so much. You are a genius...

Pivot Table fields #2
Is there a way to format multiple pivot table fields at one time and have them refresh that way, rather than having to select each column and choose the format options? Thanks CLou: I think the solution for you is to open the Pivot Table Toolbar and select the Field Settings icon. This sets the format for all results for that particular field. One limitation that I haven't found a way around is getting a preferred column size to "stick" and not reset after a table refresh. Bruce >-----Original Message----- >Is there a way to format multiple pivot table fields at ...

Only 1 taskbar button when i open 2 excel documents
Hi all. When i open 2 excel documents i have only 1 taskbar button on the taskbar even though i did not choose the option to group similar taskbar buttons under taskbar properties. Hence when i want to switch between the 2 documents i need to go to window and select them. Hence how do i solve this so that i can have 2 taskbar buttons of the excel documents on the taskbar? Thks in advance. Tools>Options>View, check Windows In taskbar -- HTH Bob Phillips "inenewbl" <inenewbl@discussions.microsoft.com> wrote in message news:6F715432-2EB2-47AC-B737-56D63F37537A@mi...

Business Cards #2
I am trying to create business cards using Microsoft Publisher 2002 on XP. Where would I find the template for Avery 8397, or for any of the Avery business card products. In page setup, select business cards, click Change copies per sheet and adjust the margins to fit your template. Avery has blank templates for downloading, choose Cards, there will be a list to select from. Your number was not among them but generally business cards are pretty much alike. http://www.avery.com/us/Main?action=software.BlankTPLHierarchy&catalogcode=WEB01&node=0 -- Mary Sauer MS MVP http://office.mi...

Conditional Formula?
Hi- I need help creating a formula that sums values in a list based on the value in an adjacent cell. Please see attached screen shot. Hopefully it explains what I'm trying to do. Thanks. +-------------------------------------------------------------------+ |Filename: excel help 3-10-06.gif | |Download: http://www.excelforum.com/attachment.php?postid=4442 | +-------------------------------------------------------------------+ -- rhovey ------------------------------------------------------------------------ rhovey's Profile: http://www.excelf...

What does the "E" in 5E
I am used MS EXCEL to perform a scatter plot. When the information was graph, Excel provided me with the formula 5E - 71e^0.085x. What does the "E" in 5E mean? The E is the way Excel represents 10 to the power. So 1.2E3 is 1.2X10^3 or 1200 and 1.2E-3 is 1.2 x 10^-3 or 0.0012. "College Professor" -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "College student" <College student@discussions.microsoft.com> wrote in message news:8F20E8A3-BDC6-42E5-A5D0-508D57C40F42@microsoft.com... >I am used MS EXCEL to perform a scatte...

Can not create Matrix Item please Help RMS 2.0
RMS 2.0 Can not create Matrix Item please Help When trying to create any new items I receive error message This is the message (-2147217864) Row Cannot be located for updating. Some values may have been change since it was last read. Manger still creates standard items but still receives message with out this number in message -2147217864 ...

Re: Two-Column Problem
After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Charley Kyd <kyd@incsight.com>... > But now, when I add a pair of pages, Publisher gives me guides for two > columns per page but gives me only one page-wide textbox per page, > not two. Can't you create your text boxes yourself? Or change the text box that appears to a two-column one? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer: http://mvps.org/the_nerd/disclaim.htm ...

MS Query #4
First, does someone know of a document that provides details on MS Query functions and their syntax. For example, I know I can use left, right, etc. What other functions exist? I want to do some parsing on some data fields and need to determine the length of the field and the place where certain characters are located within that field. Thanks. Hi ODBC query syntax is determined by driver, not by Excel. Usually it's like the syntax, used in database system, from where you get data. P.e. the syntax for FoxPro/VisualFox ODBC query is much like to syntax, you use with queries in F...

Macro Recorder #2
I have a very basic question( I think). When you are recording a Macro how do you stop and then restart where you left off? For example I have a repetitive task that moves columns from one spreadsheet to another. The problem is that there are about 40 columns I am required to move. As I record the macro can it be stopped and then restarted if I have to close Excel and return later? I can't find the answer to my question documented anywhere. Thank You, Greg You can stop it, but when you start again it will create a new subprocedure, you can go in and copy/paste the second after the firs...

2 mirror files with :1 and :2
Not sure how, but have 1 excel file, lets call it FILEA.xls and when I open it, it opens a FILEA:1 and a FILEA:2 workbook. Any change made to FILEA:1 gets repeated in FILEA:2 and vice versa. Looks like some sort of mirroring, or changes being tracked, but track changes is turned off. Any help is appreciated! Hi you've opened two windows of the same workbook (from the window /new window menu) - just close one and then save - this should solve the problem Cheers JulieD "DJR" <DJR@discussions.microsoft.com> wrote in message news:F0F6BDF5-9DC2-486E-947C-ABBB6C4544...

Date Range #2
Hi, Can someone please assist me with the following issue: I am currently using the formula noted below to direct another application ( Bloomberg ) to select prices from the previous 6th business day. My question is> Is there any way to revise the formula to exclude US holidays for successive years (i.e. for 2005, 2006...) without having to manually change the date entries each year, in the formula? =WORKDAY(Today(),-6, {"5/31/04","7/5/04","9/6/04","11/25/04","12/25/04","1/1/05" }) Thank you I'd recommend listing...

Multiple e-mail account in MS Outlook 2003
I have 4 e-mail accounts set up (all from the same server) and can send from all accounts but I only receive mail for the default account. Any suggestions? Does each account use the same account properties? (e.g. same mailbox name, userid, and password.) "Crazy Squaw" <Crazy Squaw@discussions.microsoft.com> wrote in message news:78F4A600-8A92-4011-8E4C-6094A77DEF4A@microsoft.com... >I have 4 e-mail accounts set up (all from the same server) and can send >from > all accounts but I only receive mail for the default account. Any > suggestions? ...

No way to have "event" action for query... right?
Often I create a query and corrosponding Form (in data sheet view) I then create a Double Click Event for one of the fields That opens a traditional form showing all the records fields. For instance a list of Accounts and Names. Double click the Account to open a form to see the entire Account record... or whatever. Is there any way to do this without creating the form? We need the query as we use it to select appropriate data. thanks for any help. Mel You can actually base the form off of an SQL statement, and not need to have a saved query. In the form's data tab, just paste ...

Uninstalling Office 2003 Service Pack 2
I have installed the Service Pack 2 for Office 2003 and have decided that it causes too many problems with Outlook 2003. For example, it sees certain emails as "Junk Mail" and (evidently) believes them to be "phishing". Even after placing these emails in my Inbox and adding the sender to the white list, it refused to allow me to open links from any web page link within the email. I understand that I cannot simply uninstall Service Pack 2, that I must install Office 2003 and then reinstall, without, of course, reinstalling the Service Pack. But if I do that, I wonder...