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.
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 show numbers on my excel spreadhseet in thousands. However, the challenge is I want to show negtive numbers in red within brackets. For instance, I have a number -104,784,089. I want to display it as (104,784) in red color. Similarly, I want to show 577,090 as 577. Is there a customer formatting I can set up in excel that do that?
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 want to convert Hex to Binary. I want the Binary in 8 different cells.The hex input would be lets say A1 I would want to convert that to eight different cells Bit7 G10, Bit6 G12, Bit5 G14, Bit4 G16, Bit3 E10, Bit2 E12, Bit1 E14 and Bit0 E16.
Then I want another Hex input on A2 and convert that to eight diferent cells.
How do I graph this data? I am plotting the test results (Column A is the employee number, B is the Employee name, then each of the questions are Columns D through DX with DY being the total correct by each employee) (Rows are employees and then the Ratio that the question was answered correctly) The goal is to find questions that are routinely answered in correct or that have a problem with the question.
I was hoping to graph the correct and incorrect questions so those that are either high frequency correct, incorrect or not answerd identified I can improve the test.
Example of the spreadsheet. I am using =COUNTIF(D3:D15,"1")/12 for the ratio answered correct - this should also see the ones not answered as incorrect. 1 = correct answer 0 = incorrcet null=not answered
NO Employee Name Question 1 2 3
[Code]...
How can I graph these results to answer which questions have a high frequency of incorrect answers? I am thinking some kind of scatter plot.
"One or more formulas in this workbook are longer than the allowed limit of 8192 characters. To avoid the limitation, save the workbook in the Excel 2007 Binary workbook format."
The longest formula is 29 characters long. Even if I add the number of characters in every formula in the workbook, it only adds up to 1664.
I have 2 sheets in my excel spreadsheet. One tracks data for a number of projects five different employees are working on. The other sheet is where I want to total up the number of minutes each employee has worked on their individual projects. I tried writing an IF statement like below but I am only getting the total in the first field even if the employee's name is not Employee 1....
[Code] .....
How I can write this so their totals show up in the correct row?
I have a [COLOR=blue !important][COLOR=blue !important]worksheet[/COLOR][/COLOR] containing a large amount of [COLOR=blue !important][COLOR=blue !important]data[/COLOR][/COLOR] and then another worksheet made up of summary tables. The summary table uses SUM on various fields in the data to get monthly totals.
Some of the other sums can be made up of 100+ cells.
I would like to be able to keep the sum in [COLOR=blue !important][COLOR=blue !important]the [COLOR=blue !important]cell[/COLOR][/COLOR][/COLOR] but somehow get a breakdown of what that sum is made up of and the description in the adjacent column. So I could [COLOR=blue !important][COLOR=blue !important]click[/COLOR][/COLOR] on the cell and ideally it would create a new worksheet listing all the criteria values and description for that sum, looked up using the references in the sum cell.
So clicking on the one above would give me something like
I have a spreadsheet where I keep track of sales that I make in ebay. The spreadsheet has the following columns: Name, Product, Prod Cost, Shipping Cost, Payment Received, Total. I have made a list way off the the right of the spreadsheet with all the products that I sell, so in the "Product" column I can use a pulldown menu and choose the item from the list. However, I was wondering if I could create it in such a way that when I chose the item from the list, it would automatically put the cost in the "Product Cost" field. Is this possible?
I have 2 related columns with one to many relationship, on a tab populated with some data. On another tab I'm referencing these 2 columns. When I populate one column I would like a drop down showing all the related data from the 2nd column of the first tab in the 2nd tab. I'm unable to do a named range as my first column has spaces in its name.
I don't know how I done it. But I have managed to totally goober up my pivot table settings at a global level.
Start with a data worksheet and do Data | PivotTable and Pivot Chart Report... and just click the Finish button and you get a skeleton with the grey "Drop Column Fields Here", "Drop Data Items Here", "...Row...", "...Page..." and blue highlights around each. I don't know when -- but it must be recently, perhaps even this morning... I did something that turned off this behavior. Now I don't see the grey messages. Now I don't see the blue outline. Normally, if that were the case, one simple clicks on the Show Field List buttons and voilá. But neither on existing pivots in workbooks that have always been well-behaved and new pivots too, I cannot get the Show Field List buttons to work! Neither the default feller on the PT toolbar, nor the same button on the popup menu that you get when right-click the PT. Even more amusing? The button(s) are not disabled. If I click somewhere off the PT, then yes, the buttons disable.
Click back on the PT and the button on the PT toolbar "enables". They just don't do anything.
I have a section of code that takes SO long, but I have to have it. Bascially, I need a pivot field to be set to "All", but there HAS to be a faster way. Here's my
Sub FloorCompareSetter() Dim pt As PivotTable Dim pi As PivotItem Set pt = ActiveSheet.PivotTables("PinPointPivot") ' Speeds up code dramatically pt.ManualUpdate = True 'Set the floor comparison for managers, coaches, and reps ' Make sure all PivotItems along line are visible For Each pi In _ pt.PivotFields("Manager").PivotItems pi.Visible = True Next pi pt.ManualUpdate = False End Sub
Auto Merged Post Until 24 Hrs Passes;Also, note that I have used other techniques to speed up the process:
With Application . ScreenUpdating = False .EnableEvents = False .Calculation = xlManual End With
I have a spreadsheet of over 8000 addresses. I have the street, state and zip but not the cities.
Is there a way I can do a search and mass import of cities from a website, like USPS? I'd like to know if there is something I can do quickly rather than manually look each zip code up and enter it in one by one.
I have three search boxes in XL , two are search from and to dates and the other is search be ref.
I can currently search by dates and ref but would like to show all records in the specified dates if the ref field is left blank.
I am using the query writer and referencing back to cells in XL. I don't use the wizard or SQL. I have tried many options and think I should be using a OR statement with a wildcard but can't seem to get the result, does the wildcard just show the blank cells in the actual data?
I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.
[URL]....
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B1")) Is Nothing Then Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text End If End Sub
Basically I have a form where the list can expand and contract so there will always be varying row lengths.
What I would like to see is a drop down list that shows a) the actual populated field and b) a description of the field when the drop down list is present.
I believe this can be done with a combo box however that would be a lot of work.
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).
Is it possible to set something up on a spreadsheet which shows who last updated a cell and when they did it? I have a huge spreadhseet with lots of data on which gets updated by a number of users, they update a lot of different cells and this leads to errors being made with the data in the spreadsheet.
is a way I can show who updated a cell and what date - is this doable?
I am trying to create a spreadsheet that will show vacation time; accrued, taken, leftover. There are different accrual rates for years of service, and caps on carryover.
I have been trying to figure this out, however the accruals for employees over 1 year are higher than they should be.
Breakdowns are as follows: 1-2 years - 1 week vacation - 40 hours eligible for carryover each year 3-4 years - 2 weeks vacation - 80 hours eligible for carryover each year 5-9 years - 3 weeks vacation - 120 hours eligible for carryover each year 10+ year - 4 weeks vacation - 160 hours eligible for carryover each year
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 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.
