Here is part of the vba code for a query
"(Mr_Scrap.PART_NO='" & partno & "') AND (Mr_Scrap. DATE>={d '2006-12-25'}) AND (Mr_Scrap.DATE<={d '2007-07-04'})")
I would like to have the dates be from cells A2 and B2, but this does not work.
startdate = range("A2")
stopdate = range("B2")
"(Mr_Scrap.PART_NO='" & partno & "') AND (Mr_Scrap.DATE>=" & startdate & " ) AND (Mr_Scrap.DATE<=" & stopdate & " )")
I need grouping and then summing in either msquery or back in excel after the data has been extracted from msquery.
I have external data that i have interogated in msquery, and I would like to to sum the amount column if possible.
I have two fields, first field = account number
2nd field = amount due
the first field could contain 5 sets of the same account number, but with different amounts in the Amount Due column. I would like to sum the amounts due for each of the account numbers in one column.
If i cannot do this in msquery, how do i then go about doing it back in excel. I know i can use subtotals, but then when the data updates from the external data source.
I want to apply formatting to only the cells returned by MSQUERY.
I do not want to apply formatting to all 65535 rows on the basis they MIGHT be used by the returned data, I only want to use those that ARE.
I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.
So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be:
[StartDate] = #2002/10/1#
[EndDate] = #2002/11/1#
Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is:
Between [StartDate] + .25 and [EndDate] + .25
Note: The +.25 represents 1/4day = 6hrs (6:00AM)
If I use the hardcoded Start/End Dates, my query works fine, but if I try and
pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....
If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it.
I'm starting to pull my few remaining hairs out!
[ This Message was edited by: Tuner on 2002-11-07 12:23 ]
[ This Message was edited by: Tuner on 2002-11-07 12:28 ]
[ This Message was edited by: Tuner on 2002-11-12 12:51 ]
Is it possible to create a new field in MS Query which uses an IF / IIF statement to determine it's value?
I am querying an Excel file/table, and have a field called 'Mo Nm' (month name) which stores the name of the month of a sales record, with the sales value stored in 'Sale Value'.
I'm trying to create 12 new fields in my query output to report the total sales by customer by month in a single record, rather than 1 record per month, and having to then do a Pivot Table on the result.
MSQuery is using a table called Data$ (the XL file has a sheet called 'Data', which stores the XL list being queried).
I have a spreadsheet that queries another spreadsheet, using MSQuery. The query is of a named range on another workbook. What I am trying to do is change the workbook reference, to another similar workbook, with the same range name. MSQuery is not easily letting me do this. I figured there was a way to simply change the file that the created table (in MSQuery) is using. There doesn't seem to be a way to do this.
The named range is "data." This is what the SQL view looks like: ...
I have a table of data (total 142 rows). Column contains dates, in the format dd-mmm-yyyy.
I tried to filter using DATE FILTERS->EQUALS and in the custom filter window, I chose EQUALS then picked a date from the date picker icon. The date I picked was 5/4/2009 (this is May 4, 2009, formatted automaticall by excel as m/d/yyyy).
When I clicked OK, nothing showed up despite the fact that there are 6 occurences of May 4, 2009 (formatted as dd-mmm-yyyy in the data table)
So my questions are:
1. Is this due to the formatting?
2. Is there a way to change the date format supplied by the date picker?
I have an ODBC connection to a db2 database in MSQuery. I want to return the length of a string. I've tried Len(string) but it says its not in the library.
View 2 Replies View RelatedI have inherited a file that calls an Access database via Msquery. I can see the query that is being used with:
Data | Import External Data | Edit Query | Query Wizard
How can I determine the Access database that the query is using for its data source?
how to pass multiple parameters using a range of cells to MSQuery? When I try to it tells me that I can select a single cell only. Anyone know of a new and improved sql driver to use with Excel?
View 9 Replies View RelatedI have created a userform but I am having extensive problems with the date formats.
My system is set to UK and short date is set to: DD/MM/YYYY
When I used code to add the values in the userform to the spreadsheet, any that contained a date format would revert to the US format.
So I finally figured out to use DateValue to format it correctly for example: ...
Running XL07. Need to have one workbook pull data from several dozen others.
Have columns to the right that refer to the query table.
As I add in queries to other workbooks, the time to update each individual query goes up a lot; it feels as if the update time is increasing geometrically. I'm giving up after 2 hours, for query updates that used to take
One of the reports I run provides me information on lengths of time. Such a field displays as |0:09:16| indicating 9 minutes and 16 seconds. However, when the report generates the excel spreadsheet it saves these cells in date/time format ([h]:mm:ss). If I were to convert this field to the number format (so I can manipulate and graph it) it displays as such |0.00643460648148148| Ideally I would be able to have the data in the field stored as |556| (556 seconds, or 9 minutes 16 seconds). I have thousands of fields that I need to manipulate where the data is stored in this format and I can not figure out how to fix it.
View 5 Replies View RelatedEvery month, I have an import a report to a spreadsheet. At my column A, it supposed to be a date format.
For some reasons, I have a combination of a few cells of date format and a few cells of general format with no order at all.
What I need is: Search in the A column, if date format leave it , if not change from general to date format.
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
I'm trying to create a simple Excel spreadsheet (not looking for Access or complex relational database stuff) to have the following:
A tab with data about a person : name, date of birth, join date, and a list of sessions they attend (eg. Monday Morning, Monday afternoon etc).
A different tab in the same sheet, or a form to select from that list of people, and allow changes to the information, and the ability to save it back to the data tab. Ability to add new people or delete people would be useful.
I am looking to create this as a basis for managing sessions, creating a register etc....
Any simple spreadsheet which I can use as a starting point, or to use as a reference in getting it established?
I am trying to do a query against my database using MsQuery, but I am having a problem with it...
One of the tables in my database stores information as Binary (Bits) instead of actual text.... When I create the Query in MsQuery, the text is displayed as desired...but when I send it to Excel that column is not displayed..
Does anyone have any ideas on how I can get it to send the column to Excel?
See attached for what I mean. The Bits column shows in MSQuery, but not in Excel.
I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
View 9 Replies View RelatedI have a some dates in a format which are not being converted into the normal date format. So i made this formula to convert it into the normal date format:
=IF(LEN(B218)=10,(MID(B218,4,2)&"/"&LEFT(B218,2)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=9,(MID(B218,3,2)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,IF(LEN(B218)=8,(MID(B218,3,1)&"/"&LEFT(B218,1)&"/"&RIGHT(B218,4))+0,"")))
Formula is working fine except on this type of date "9/9/2013"..i also tried an OR formula with mid but didnt get the desired result.These are the type of dates:
12/10/2013
12/9/2013
9/11/2013
9/9/2013
7/25/2013
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another
18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
View 2 Replies View RelatedExample......
In D2 I have: =B2
In D3 I have manually input: (space)3-08
As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!
How we can convert any data which is in General format in to date format.For ex I've:
19/03/2012 14:57 (General format)
I want the output/result
19-Mar
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
Existing ----> Required
2/1/1980 --> '02-01-80
2/1/1970 --> '02-01-70
2/1/1983 --> '02-01-83
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
I have txtboxes in which I write date in format dd/mm/yyyy but if I want to put date in cell correctly from this textbox in need to declare event on exit -change format to mm/dd/yyyy and then it puts date in desired format which is dd/mm/yyyy and my regional settings are English(uk)
Is there a way to put any date format in textbox and in cell there will always be format dd/mm/yyyy or at least if date is entered in format dd/mm/yyyy that also in cell the same format is putted
I have a spread sheet with a date colume that reads: 2012-06-27-19 I need to have this read like 06/27/2012 but nothing I do is working I have tried to go to the formatting process and backing the hr:mm out and that doesn't work. I really don't want to go line by line to manually correct this issue.. HENCE ... over 2000 lines
Second question: If I have a column that reads 02/15/2012 and another column that reads 3/27/2012 how to a format a 3rd column to make it read total number of days between 1st date and 2nd date?
I have two questions regarding date format and hope you can provide input.
1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?
2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?
I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.
View 5 Replies View RelatedIn one excel file, i have a date , and i set this to "dd/mm/yy hh::mm am/pm" format. From another file i will open this file and i need to copy that date to this file. My question is , before copying i need to check it is in the "dd/mm/yy hh::mm am/pm" format using vba.
View 9 Replies View Related