filter query based on single column list box
Thanks for taking the time to read my question.
I want to filter this query by the values in my single column list box. I
made this function to create the string, but the query doesn't like it. If I
paste the string that the function returns into the criteria line the query
What am I doing wrong?
SELECT tblPeripheral.Type, tblPeripheral.Location, tblPeripheral.[Serial
Number], tblPeripheral.Model, tblPeripheral.Brand, tblPeripheral.[Model 2],
ExportPeripheralInfo() AS Expr1
Func...oledb query problem
Hello. I have a connection with a Access 2007 db back end (tables only)
using OLEDB. The db is on a shared drive on the network. So a UNC is used.
A simple query works but a query in which other tables are included just
never works. Here is the query I'm trying to use;
LEFT JOIN tblProjectLead ON tblChangeRequest.Project...getpivotdata
I have a pivot table with multiple columns and need to create a summary
report by different views using getpivotdata. Below is just a small
summary. The actual data runs into hundreds of rows.
The Pivot looks as follows
Col 1 Col 2 Col 3 Col4
Country Segment Pricing Various months.......................
A AA XA
Tot...Using Lookup Query with Calculated Value in Table ComboBox
This question takes a complicated setup, but I'll try to keep it brief. I
have a database with a main table (tblMain) and a lookup table (tblLookup).
tblMain has an integer field (LookupMe) that is related to the ID field in
tblLookup. I set up LookupMe to use a ComboBox input display control that
uses a query as its source. The query contains two columns: tblLookup.ID and
a calculated value, say "[First Name] & " " & [Last Name]". Back in tblMain,
the LookupMe field has the following properties:
Bound Column: 1
Column Count: 2...querying a view
I've got 2 taables containing a couple of years worth of data and have
created a view which joins them and does a couple of calculations.
If I run a query against that view to select only the last weeks data, does
SQL Server haave to joint the whole contents of each table before the where
clause on the query is used?
No. The query optimizer will attempt to optimize the query as a whole.
That is, for example, if your view is
Select <column list>
Inner Join Table2 On <condition>;
and you do
Select <column list>
From <Your...web query autoformats numbers
I'm fetching data from a server (the URL of which I've excised
for security reasons) using a web query as below. The problem is,
sometimes the data is something like so:
This is supposed to be interpreted as text. I can't seem to find
a combination of settings that causes it to leave that as text.
It always seems to convert that to a number and display it as
Is there any way to force this to leave it as text?
- locking the formatting but leaving the page editable (the
query refuses to run)
- formatting the destination as text
- various s...Queries calculation
I have a table called “openbills”.
I have a query called “billsopenvalue”
In the table I have a field where I insert the amount a customer paid – this
can be as well a part payment of the bill.
The query calculates how much is still pending to pay.
Now the problem:
Ie: the total to be paid amount is 1000. The customer paid 100. = Result of
Now the customer paid another 100 – normally the result of the query should
be 800; but it is again 900 because the resting value is calculated: total to
be paid – paid amount = resting amount. How can I fix after each calculation
the restin...Convert Access SQL Query to Excel MS Query
I have a query that works in Access but when I convert it to Excel, I get
errors. Can someone help me with converting this to MS Query SQL Excel 2003
Here is the original Access coding:
SELECT HS_Incident.[Plant Name], HS_Incident.[Incident Date],
HS_Incident.[Incident Desc], HS_Incident.[Lost Time Case],
WHERE (Datediff('d',[HS_Incident].[Incident Date],date())<=7) and
[HS_Incident].[Lost Time Case] like 'Y%' and [HS_Incident].[Plant Name]
<>'MARKHAM ' or (Datevalue([HS_Incident].[Report Date])>...Trying to format the spreadsheet to fit on one page from a query
We have an Access Database with Employee Info.
Example: First Name, Last Name, Cell, Extention, Email, Title
We have some old timers that want their report a certain way. We have 6
different sheets in a workbook that are sorted by the above (by First, by
They do not want Access reports. They want one workbook, with 6 different
sheets, posted on the Intanet.
So I created a Workbook that way and used "Import from an access database".
Each sheet has it's own saved Query. The data comes across perfect. However,
there is 90 people on the list. So it goes from row A1...select status in a query
I have a table with account information. One of the fields is Account_Status,
there are 6 possible choices for account status going from 1 - 6. Wha I'd
like to do is run a qurey where the user can choose to return accounts with
status 4 or 6. Any help appreciated. I can use "between and" ut this returns
to much information if I need just 3 or 6. It also has to be user friendly as
I do not want someone in the query.
Any help appreciated.
See sample database at this site:
Look at how the listbox is set up (...Outlook 2007 Rules Query
I have 2 email addresses that I download into Outlook 2007.
Let's say, email@example.com and firstname.lastname@example.org
I have a rule to move emails to email@example.com into a particular
However, if people send an email to both of my addresses, both emails get
moved into this folder.
How can I have the mail to firstname.lastname@example.org stay in the inbox?
Add an exception to the rule - except if the other address is in the
header - except if received by the other acct may also be an option.
Diane Poremsky [MVP - Outlook]
Outlook Tips: http://www.outlook-tips....Queries 05-14-07
I have two select queries.
I would like to combine the data from this two, into one query (something
like merging the data) - query 1 + query 2 = query 3
Can someone tell me how to do do that?
We're not there. We can't see what you're doing. Post the SQL of the
two queries as well as information about what the queries do and what
you would like this 3rd query to output and we'll get back to you.
On May 14, 8:41 am, Amateur <Amat...@discussions.microsoft.com> wrote:
> Dear Sirs,
> I have two select que...Query runs slow.
I have a query joining few tables. One table is very large (500T
rows), but the other tables are having less than 100T records. Query
seem to run slow, 1000ms. I have proper indexing implemented. What
should i do?
I am thinking of few solutions.
Fix 1. Make a temp table and apply condition to largest table. Doing
so, the temp table will have only 100 records or less and then join
Fix 2. Instead of temp table create View for the larger table and
apply filter and then join the view with tables
Fix 3. implement caching of query (pls suggest if there is any other
way to cache the ...Web Query #6
I have this web site which shows the approval status of a number:
The sample numbers being checked include the following:-
I have the above numbers in an excel file.I would like to check the
approval status of these numbers from excel itself.
Can anybody help?
Turn on the macro recorder and do it all once manually, with the first number.
Then inspect the code and see where the number figures in the code. After
that, you can think of ways to automate the p...Run a query a few times
Is there a way to get a query to run 10 time. I would like a buttonthat will run a query 10 times.It would take a long time to expalin y i need to run it 10 times butthere is a reasonThanks Use a For..Next loop or a form timer and the DoCmd.RunSQL method....GETPIVOTDATA Link to External Pivot Table
In a workbook, I made a GETPIVOTDATA formula to link to a Pivot table in
another workbook, but...
If I open the workbook with the formula while the pivot table workbook is
closed, and then update links, I receive the dreaded "#REF!" error in the
cell where the formula is. (By the way, the fomula points to the correct
full path of the starting cell of the pivot table, and the formula works
correctly when the pivot table file is open.)
Is there any way to make GETPIVOTDATA to read pivot tables in other
workbooks that are closed???
I would prefer to use the GETPIVOT DATA formula rat...using Like in query
This is additional question from my post earlier:
I'm trying to use combo1 from existing open Form into query builder
as criteria for row source of a listbox in another open Form using
"Like" to find all title_description has keyword found in combo1
Thanks to Douglas J. Steele gives me a correct syntax for
SELECT dshet_apr.Number, dshet_apr.Title_Description
FROM dshet_apr INNER JOIN datasheet_d ON dshet_apr.Number =
WHERE (((dshet_apr.Title_Description) Like "* " &[Forms]![frmm]!
"*") AND ((datasheet_d.Dis)=[Forms]![...Combo Box Query Help
I have a combo box with hospital names in it. I am wanting to select a
hospital from the combo box and have the the address text box automatically
fill in with the hospitals address. How can I go about doing this. Please
Message posted via http://www.accessmonster.com
On Sat, 29 May 2010 23:00:34 GMT, "Scott_66701 via AccessMonster.com"
One way is to have an extra hidden column in the combobox. For example
the rowsource would be a query like this:
select HospitalID, HospitalName, Address & " " & City & &quo...Append Query 07-18-07
I am using Access2003
I am trying to set up an append query that will combine similar fields from
two tables onto one.
ex: FName, LName from Table A
FName, LName from Table B
dump into FName, LName on Table C
This is for a human resources type of database... Table A being the employee
and Table B being the dependents.
Table C would represent everyone from those two lists who let's say have
elected health insurance... so the employee from Table A that person's
dependents from Table B... all together in a single list on Table C...
I tried two separate append tables, and I am ...COUNTIF query #2
Hopefully a nice easy one....
We have a logging system, and the spreadsheet basically has the person
name doing a call, what the call is and how long it took. This is use
all day so loads of calls get logged by various people.
Say column A is Persons name and Column B is time spent. I need
calulation that checks how much time 'person 1', 'person 2', 'person 3
etc has spent on calls during the day and add's it all up.
Please can someone show me how to do this, i have tried a COUNTIF bu
cant quite get it to work.
Many thanks in advance.
Darren (digital_life...Complex Query not working on report
I recently started using 07 on a fe/be db created in 03. The query below
worked fine in 03 but now gives me the "enter parameter value" box asking for
"tblRECORDS.CREATE" when I open a report based on it with 07. The query
still opens and displays fine, but the report seems to hangup. Is it a
timing issue? I know the query is kind of busy, but it is based on a table
where the data is stored as 1s or 0's to make big queries calculate faster.
SELECT tblRECORDS.QAC, tblRECORDS.CHS, Sum(IIf([IO]="INBOUND",[BR],0)) AS
IBR, Sum(IIf([IO]="OUTB...Need focus on query first, then on Form when query is closed
I am using Microsoft Office Prof Ed 2003, SP2 for my access dbs and I wasn't
sure if this is a Form Coding question or a Macro question. I am having
problems in coding/macro usage on a cmdbutton: I am using the frmList of
Reports as Start Up Form. There are several cmdbuttons on this from, linking
to several Macros which in turn open several reports. One of my cmdbuttons
when pressed via Macro, opens [qryRequirement Status Comp by ETID], which
requires [Enter ETID] as a parameter. I want the user to be able to copy and
paste the results of that query directly into his own Excel ...Help with InStr in a query
I have values like "2010 WW14" and "2010 WW7" in a query and I'm trying to
grab the number part to the right of "WW". It will always be a 1 or 2 digit
number and I always want the format to be a number.
I'm currently using Left([WorkWeek2],(InStr(1,[WorkWeek2],"ww")-1)) to grab
the 4-digit year to the left of "WW", but I can't figure out how to alter it
for what I need above. Any suggestions?
"Give someone a fish and they eat for a day; t...GETPIVOTDATA and (blank)
I have created a spreadsheet which has two pivot tables in it on separate
sheets. In addition there is a summary sheet which I use the GETPIVOTDATA
function to extract data from the pivot tables referencing adjacent cells for
the Manager and Rep ID's. The original data includes some rows without ID's
in either the Rep or the Manager fields resulting in some "(blank)" entries
on both pivot tables. Where this has happened on the first table I have
entered "(blank)" in my reference cell on the summary sheet and the data is
extracted as nomal.
When I...Editing an External Web Query Import
I've currently got an Excel sheet with a table I've imported from a
web page I've created. This was done using the Data->Import External
Data->New Web Query. The page itself is a CGI script and as such
takes arguments in the address.
I was wondering if anyone knows how I can alter that URL from a cell
in Excel. Specifically I want to be able to change the hostname in a
cell in Excel and the corresponding ?hostname='blah' to change.
Any ideas would be greatly appreciated.
Data>get external data>run saved query. Right click the query and select