Format Cells Returned By Msquery

Jan 31, 2007

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.

View 4 Replies


ADVERTISEMENT

Grouping And Then Summing In Either Msquery Or Back After The Data Has Been Extracted From Msquery

Nov 19, 2009

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.

View 2 Replies View Related

Date Format In Msquery

Jul 11, 2007

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 & " )")

View 9 Replies View Related

Multiple Parameters Using A Range Of Cells To MSQuery

Apr 17, 2003

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 Related

Function: Returned Values To Be Entered Into Different Cells In A Column

Jun 20, 2006

I am trying to write a formula that will look at an array (containing text strings) and then for each occurrence of a particular value return the text string from another cell in another column but which matches the row of the occurring value. I would prefer all returned values to be entered into different cells in a column but I would be happy if they were all in one cell separated by a comma or whatever.

View 7 Replies View Related

Copy Range Of Cells - Returned Address To Some Offset

Oct 16, 2006

Sub TestSearch()
Dim FoundCell As Range
Dim SearchString As Variant
'Specify Value to Search
SearchString = " Excel"
'Perform the Search and Return a Cell Value
Set FoundCell = Worksheets("Source").Range("A1:A10000").Find(" Excel", lookat:=xlPart)
If Not FoundCell Is Nothing Then
MsgBox "Found at " & FoundCell.Address
End If
End Sub

I'd like to take the code above, which returns a cell address which contains the text " Excel" and modify or add to it the capability to copy a range starting from the address returned and ending 102 cells further down in Column A on the Source sheet to A1 of the Sheet1 sheet. The crux of the problem seems to be converting the address to a row number then adding the value of the number of cells in the range to find the end row number then converting that back to an address??

View 5 Replies View Related

Can You Use IIF In MSQuery

Apr 13, 2009

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).

View 9 Replies View Related

Change MSQuery References

Jan 13, 2009

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: ...

View 9 Replies View Related

MSQuery - Return Length Of String

May 21, 2012

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 Related

Data Source For Msquery Query

Nov 16, 2006

I 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?

View 6 Replies View Related

Oracle Date/Time As A Parameter In MSQuery

Nov 7, 2002

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 ]

View 6 Replies View Related

Disable Auto-calculate When Updating Queries Through MSQuery

May 21, 2009

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

View 9 Replies View Related

Reference Error Cells Without Error Being Returned

Oct 17, 2006

I have a VB function in a worksheet that requires data from a workbook that is not under my control. The problem is that the workbook does not always have complete data. Often cells are filled with #VALUE, when this occurs I need a way to tell my VBA to assume a value of 0. I have tried using if(cell="#VALUE", 0,cell) but to no avail. any suggestions?

View 7 Replies View Related

Simple Excel Using MSQuery To Access Data In One Of Tabs And Allow Updates

Jul 1, 2014

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?

View 3 Replies View Related

Getting Binary Bits Field In MsQuery To Show In Excel Spreadsheet

Feb 10, 2009

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.

View 14 Replies View Related

Convert Cells With Percentage Format To 'same' Decimal In A Column With Mixed Format

Jun 7, 2006

I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.

View 7 Replies View Related

Time Format: Format Cells To Contain Minutes, Seconds And Hundredths

Oct 15, 2009

How can I format cells to contain Minutes, Seconds and Hundredths of seconds to be used in calculations eg 1.24.99 means 1 minute and 24.99 seconds. Example calculation is: 1.24.99 - 1.24.90 =0.0.09

View 2 Replies View Related

Having A Solution Returned As A Value

Dec 9, 2009

Is it possible to have a formula that will return the answer as the value.

For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value.

I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution.

Would this require a macro?

View 5 Replies View Related

Format Cells To Change Name Format

Dec 6, 2013

Is it possible to change the contents of a cell (A1) displaying a name as:

John Smith

to Smith, John (in cell B1)

View 3 Replies View Related

Sumproduct Formula - #Value! Returned

Jan 29, 2009

I've got a workbook where sheet "Raw Data" is used to enter audit findings. Subsequent sheet "Analysis" contains formulas to extract quantities and nature of audit findings so that they can be shown on quarterly reports.

When I set up the workbook the formulas on the "Analysis" sheet worked fine. The department has filled the columns with data and now all the formulas are returning #Value! There's something fishy going on here.....

=SUMPRODUCT(--('Raw Data'!$H$7:$H$1000=A4),--('Raw Data'!$K$7:$K$1000=C$1))

View 4 Replies View Related

Add The Hyperlink To The Value Returned In The Cell

Jun 14, 2009

I'm trying to add a hyperlink to the final outcome of: =SUBSTITUTE(Info!$G$28,"village=99999","village=" & Z8). I need to add the hyperlink to the value returned in the cell. The value ends up being something like:

http://en28.tribalwars.net/game.php?...5&screen=place but it isn't a hyperlink.

The hyperlink can either be like this:

http://en28.tribalwars.net/game.php?...5&screen=place or like this

View 3 Replies View Related

Remove The (x) Or What Value Is Returned When The (x) Is Clicked

Nov 20, 2008

I have a user form where a user chooses a file. The form works fine, but if the user clicks the (x) in the top right of the box I get an error. how to either remove the (x) or what value is returned when the (x) is clicked so that I can put some handeling for it in my script.

View 5 Replies View Related

Formula To Keep Dates If Value Is Returned?

Nov 9, 2011

Here is my file after two rows of headers:

A3: any date
B3: any date

C3: =IF(A3>DATE(YEAR(TODAY()),1,1),A3,"") ... I need excel to return the date here only if it is within this year

D3: =IF(B3>DATE(YEAR(TODAY()),1,1),B3,"") ... same as above
E3: =IF(COUNT(C3:D3)

View 9 Replies View Related

Blank Cell Returned Instead Of 0?

Oct 10, 2013

This formula works great but in my example if A1 cell is blank how can i get it to return a blank instead of a 0?

=SUMPRODUCT((RANKING SALES[Master.xlsx]Tyre!$A$2:$A$156
=A1)*(RANKING SALES[Master.xlsx]Tyre!$B$1:$N$1
=$I$4)*'RANKING SALES[Master.xlsx]Tyre!$B$2:$N$156)

View 4 Replies View Related

Does Not Find A Value Returned By A Formula

Apr 2, 2007

I am using a vlookup formula. In searching my sheet that uses this formula and it does not find a value that is in formula cell, why? Is there a setting that can be changed?

View 9 Replies View Related

VBA Code To Display A Msg Box If The Returned Value Is #N/D

Jan 13, 2010

I have a sheet where I put a code from a product in Column B, and the name of the product will appear in Column C, with a VLOOKUP Formula. What I would like to do is, get a VBA code to display a msg box, if somebody enter a the wrong code. If that happen a #N/D will appear in Column C.
In this Sheet I already have the bellow VBA Code, to avoid repeated product codes:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range
If Target.Count 1 Then Exit Sub
Set Rng = Range(Range("B1"), Range("A" & Rows.Count).End(xlUp))
If Application.CountIf(Rng, Target) > 1 Then
MsgBox "O valor introduzido " & "(" & Target & ")" & " é duplicado. O Menino está a Dormir???"
End If
End Sub

View 9 Replies View Related

Vlookup & Autofilter On Returned Value

Aug 23, 2006

I am trying to write a macro that will search in column ONE, then autofilter in column TWO.

So for example....

aaa | abab
bbb | abab
ccc | sdsd
aaa | abab
bbb | sdsd


I then search for "aaa"

then...(it autofilters by what is associated with column TWO, regardless of what was in column ONE)

aaa | abab
bbb | abab
aaa | abab

I was thinking...using Vlookup in column ONE, then Autofilter with column TWO.

My code right now is...but clearly it doesnt work.

Sub CommandButton1_Click()

Sheets("Sheet1").Activate

Dim Var As String

Var = Application.VLookup(TextBox1, Range("A2:AI772"), 34, False)

Selection.AutoFilter Field:=34, Criteria1:="*" & Var & "*"

End Sub

View 5 Replies View Related

Nested IF AND OR Formula Its Does Not Returned The Value 15

Nov 1, 2006

I have the following IF formula. It is working fine except the underlined part; it does not returned the value 15 ...

View 4 Replies View Related

Concatenate Returned Value With Its Corresponding Match

Nov 17, 2007

I have a 2 column array of numbers. Column A has duplicate values, column B has unique values.

I have a lookup which is counting the number of occurences of any given value in column A, but now need to populate a cell with a concatenated string of the values from column B that correspond with all instances of each unique value in column A.

View 9 Replies View Related

Multiple Values Returned From Same Column

Apr 3, 2014

I have IDs in the first column of an excel chart. After that I have three more columns, being date of test, type of test (start, 3 months, 6 months, 9 months, finish), and lastly the result for the test.

Right now, the same IDs are listed multiple times for different results, so for example:

ID | Date | Type | Result
27 | 3/27 | Start |8.3
27 | 6/27 |3 Mon |7.9
27 | 9/27 |6 Mon |7.4
27 | 12/3 |9 Mon |7.2
27 | 3/27 | FINISH |6.5

What I need is the following layout:

ID | Start | Date | 3 Months | Date | 6 Months | Date | 9 Months | Date | Finish | Date

ID is only shown at left, and the values for the test result and corresponding dates are shown in their respective columns.

I tried to do an IF function with a LOOKUP inside, and it worked originally, but when I add more values for the same ID to the original column, it only shows the latest date, and only gives that result.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved