I have been wrestling for ages now with a query that I thought would be
simple .. but can't get the output I want!
I have a table [practice suppliers] with fields SUPPLIER and AUTO CATEGORY
(and others). The field SUPPLIER is not a unique (key) field, but in fact it
almost is: ~95% of the records in the table contain unique values in this
field.Of the remaining ~5%, about half of the records for a given SUPPLIER
value contain the same value of AUTO CATEGORY, and the other half contain
different values for AUTO CATEGORY.
I want a query that will return the number of diffe...query destination field
How do you change a destination field of a query in Excel? I have the data from the query being sent to cell A4 but need it to go to cell A1.
I tried the "edit query" option but with no luck
I think you can just delete Rows 1:3 and it will adjust accordingly.
MVP - Excel
Excel Blog - Daily Dose of Excel
"albert" <email@example.com> wrote in message
> How do you change a destination field of a query in Excel? I have the data
from the query b...Cashflow query to return previous valid field if date has null val
Hope someone can help as usual!
I have designed a simple query to control my cashflow, pulling data from a
topay_query and toreceive_query. I have managed to make all the calculations,
The thing is that I have an "Accumulated payed" and "Accumulated received".
When there's no moviment in a day these fields come out blank. How can I do
that when there's no movement, the query would consider always the value of
the previous valid field?
How are your queries set up? Are you linking by day? Is one of your
criteria to only ...Text box input based on a combo box selection
I have an inventory input form that has a combo box from which the
user selects the shift for which the inventory is being taken. On
this form I also have a sub form from which separate data is gathered
from the user, but the shift information is also needed. The form and
subform are bound to separate tables. I am using a subform to gather
the additional information to a separate table because the results are
used in a separate calculation. I only want the user to select the
shift information once. How can I populate the "shift" text box on
the subform with the same information ...Queries and Now()
Using Access 2007.
I have a database in mdb format that records the information of customer
sales on a web site. In the OrderDate field I am using Now() to record the
date and time the order is placed.
I also have a query which displays only currently open orders.
When I view either the table or query data locally within Access the
date/time information of each order displays correctly.
Likewise, when I view the table data online via an ASP VB web page, the
date/time information of each order displays correctly
However, when I view query data online via an ASP VB web page, the da...Make Table Query Question 07-21-07
I have a make table query and one of the field is a $$$ dollar amount.
I set the properties to "currency".
However, in the table, the field type is "Number".
Any ideas why this is happening?
You could try typecasting the field in your query.
For eample, if the design view shows the field like this:
Amount: [Quantity] * [UnitPrice]
try changing it to:
Amount: CCur(Nz([Quantity] * [UnitPrice],0))
In general, the better solution is to design the table the way you want it,
and then use an Append query to populate it, rather than a Ma...multi record query
I am taking 4 fields from a linked AS 400 database, the 4 fields are
IDAREA = T
IDAISL = RA
IDBAY = IL1
IDLEVL = 9
I need the word "TRAIL19" to be my parameter for the information I need for
a query to fill in the form and report
Is there any way to combine these 4 feilds?
On Mon, 11 Jan 2010 20:09:01 -0800, Norm
select IDAREA & IDAISL & IDBAY & IDLEVL as myCombinedField
(of course you change myObjectNames to yours)
Microsoft Access MVP
>I am taking 4 fields from a linked AS 4...How do I get a filter to "UPDATE" the rows selected?
I am using Excel 2002 and I have a speadsheet that has prices in it.
If I have the filter set to (the filter criteria is much more complex than
this though) prices for items that cost >$5.00.
Of I change an item to 4.59, it should not be seen. How can I get it to
re-update the rows shown, with out releaseing the filter and resetting it?
Is there something like refresh.filter or anything like that?
AFAIK you must release and reset but a macro with a worksheet_change event
could trigger this for you so that it would happen automatically.
SalesAid...Text "=SMTP:" appended to email addresses
Not sure if this is a server or an Outlook issue, so please bear with me if
in wrong place!
I've got one user (Outlook 2007) who is finding that when she replies to an
email, the text "=SMTP:" is appended to the front of the destination email
address, which renders the email undeliverable.
? Any ideas ?
"Baz" <Baz@discussions.microsoft.com> wrote in message
> Not sure if this is a server or an Outlook issue, so please bear with me if
> in wrong place!
> I've got one ...query form 12-29-09
I have a form that opens when you run a query for an employee name. It does
exactly what it is suppose to do. It displays all information that I have
requested. The problem I am having is when I enter a name of an employee that
is not in the database the form comes back as a grayed out box. Is there any
way to set the form or query to prompt the user that the name is not in the
database and allow the user to run the query again.
Check to see if record exist before you open the form.
If you are using a command button to open your form containing code like
the follow...Queries independent of accents?
We have a DB of people from many countries. Some names contain chars with
accents like � or � and other types of accents. Is there a way of searching
all names in the DB containing any kind of accent without specifiying the
acctents used? Is there a function like f("�") = "e" where f is the function
I look for.
Thx in advance!
Do you store the data as NAVARCHAR(n) datatype? Have you specify N just
before a value in WHERE condition?
Hmm, I think may want to take a look into REPLACE function to remove accents
I've seen some posts regarding a similar problem to what I'm having but cant
find the resolutution!
I have a database with tableA which contains basic inventory info. I have a
field in tableA named location. There is a second table (tableB) which has
a list of all the locations our company delivers to.
I've been warned to stay away from lookup fields, so I'm using combo boxes
on the form to look up the location and then populate the location field in
tableA when the value is selected.
Here is the issue (and again, I've found some posts similar): Running a...Help on muliple table queries
I wonder if you can help. I have 5 tables of data with the same column names.
I want to run a query which includes all records from all tables within a
certain time period using a from/to date.
Is this possible/simple, and how do I do it!!
> Hi all,
> I wonder if you can help. I have 5 tables of data with the same
> column names. I want to run a query which includes all records from
> all tables within a certain time period using a from/to date.
> Is this possible/simple, and how do I do it...How do I programmatically select various ranges in vba?
From what I understand you must use Application.Union to combine large Range
groups in vba to use in Excel.
Dim BigRange as Range, R(10) as String
R(1) = "1:1, 3:3"
R(2) = "7:7, 20:20, 43:43"
R(3) = "100:100"
Set BigRange = Application.Union ( Range (R(1)), Range (R(2)), Range
Which is fine and dandy with just the 3, but if I had a hundred or so ranges
how would I create a loop statment to combine the various ranges.
*The ranges would contain the entire row of a wo...Error Exporting Access Query Results to Excel
I'm having 2 different problems exporting query results from Access to Excel
I do what I've always done, in Access VBA get ADO recordset from query
results, instantiate Excel, get a range object, use the range object's
CopyFromRecordset method. This has always worked in the (pre-Office 2007)
past for me. It is much easier than iterating records and columns and
assigning values to cells. Now, I get an error -2147467259 Automation Error
Unspecified Error. I'm guessing this is because 2 of my recordset fields are
pictures in Attach...MS Query instead of "Edit OLE DB Query"
I am having a problem with editing some existing queries within Excel
2003 workbooks. When clicking on the "Edit Query" button from the
External Data toolbar, I now get the "Edit OLE DB Query" dialog box,
instead of MS Query, which was the interface that I previously would
get. Is there a way to switch this back?
I have been using Office 2003 on Windows XP Pro SP2. I recently had
Microsoft Update upgrade my version of Office 2003 from SP2 to SP3.
The problems appear to have started after that.
I can still create new queries in Excel using MS Query, which is...Moving Parameter Queries
I have created a Parameter Query that imports transactions from an Access Database on my 'C' drive to "work out the bugs".
It works just fine, however when I move it (as well as the related dqy file and the database) to a shared network drive so that others can access it, it sends back an ODBC driver error message. I am at a loss as to how to remedy this situation without recreating the query (very time consuming) at the new location
Has anyone run into this before
Any efforts will be much appreciated
Sounds like a confused link - am assuming your query pulls
from a...xPath query #2
I'm trying the select a element based on values of two attributes. In the
Xml doc i need to look at an attribute minVal and maxVal. if the Value I'm
passing in in the xPath is greate than min "and" less than max, I'll want
to pull all the attributes of that element.
Is this possible with xPath? I'm able to easily query 1 attribute, but not
> I'm trying the select a element based on values of two attributes. In
> the Xml doc i need to look at an attribute minVal and maxVal. if the
> Value I'm passing in in th...Append Query 08-21-07
I have two tables.
Table A has 349,353 records.
Table B has 377,787 records.
I need to add the 28, 434 records that are not in table A but are in table B.
Table A and B have a unique 6 digit number as its primary Key.
When I attempt to run an append query from B to A, I get an error message
stating it did not add 28, 434 records due to Key Violations.
Sorting the unique 6 digit numbers tells me right off the bat that there are
some in B that are not in A, yet when I attempt an append query, it tells me
there are Key violations.
I even selected on specific record whose 6 digit number I ...Excel DNS query
Is there a way for an Excel function to query a DNS server?
Not that I know of, but you can turn on the macro recorder, use 'get
external data' and tailor the resulting code into a user function of your
E.g. I've used this to create a button to get MS-Access data from a query
that has the same name as the sheet (tab) name. It saves me a lot of
<firstname.lastname@example.org> schreef in bericht
> Is there a way for an Excel function ...Learning XPath Query Language Resource Material/Books/White Papers
I am a total beginner to XPath and using Native .NET XML Classes (although I
have used XML with Datasets before). I have "XML for ASP.NET Developers"
which seems like a good .Net XML overview book but would like to learn more
about writing XPath Queries.
What do I need to learn the XPath Language?
This posting is provided "AS IS" with no warranties, and confers no rights.
"Earl Teigrob" <email@example.com> wrote in message
&g...Obtaining selected printer name
AfxGetApp()->SelectPrinter(m_hDevNames, m_hDevMode, FALSE);
how do I obtain the printer name, e.g. "\\Hostname\Printername"?
Also how do I get a handle to the selected printer?
DEVMODE FAR * pDevMode = (DEVMODE FAR *)::GlobalLock(m_hDevMode);
Sorry for the nuisance. Its been a long day.
"Jan M" <firstname.lastname@example.org> wrote in message news:#7MdFAhyHHA.5964@TK2MSFTNGP04.phx.gbl...
> Having called
> AfxGetApp()->SelectPrinter(m_hDevN...Multifile select in FileOpen dialog
Hello everyone. My app (SDI) requires several different
data files to be loaded, after which it does some serious
number crunching. It would be best if, rather than the
user having to open each file individually, they can
select several files at once in the standard OpenFile
dialog. Is this possible, and if so where do I go for more
info? Thanks in advance!
Since this is an MFC newsgroup, I'll assume you are using MFC and recommend
that you use the CFileDialog class.
Just set the OFN_ALLOWMULTISELECT flag in the OPENFILENAME structure (in
CFileDialog::m_ofn). The documentation for ...Printing a selected part of worksheet
I know it's possible to highlight a portion of a
worksheet and print just that part. That works fine, but
I have a super big spreadsheet that has to be broken into
several pieces before I can print it.
I'd like to make the process more efficient by assigning
names to various portions of the spreadsheet. Then, when
I would want to print, I could simply tell Excel to print
those predesignated portions. But how do I do that?
Any help is much appreciated.
> I ...[Right-clicking a CTreeCtrl] how to select the item under the mouse?
I need to move the current selection in the tree to the node
under the mouse pointer when the user right-clicks the
My problem lies in the use of HitTest:
cp.SetPoint( p.x, p.y );
HTREEITEM selected = faqTree_->HitTest( cp, &flags );
if( selected != NULL && (TVHT_ONITEM & flags) )
faqTree_->SelectItem( selected );
I don't think I'm getting the right coordinates above. What can I
use instead of GetCursorPos() ?
This will help: