Search Function with queries
new user to access 2000 and i am trying to create a search
query that will allow me to enter in a value and return that value. The part
i am having trouble with is i need to do this for many fields and if i use
the method i came up with, when i leave a entry empty using the "AND"
function i get nothing and when i use the "OR" function i get all the values
for both. maybe you guys can help me get in the right direction.
this is the command i am using in SQL:
SELECT [Chain Machine prod Machine list].Description, [Chain Machine prod
Machine list]...Provide number of results in advanced search
It would be helpful to get the number of items found as a part of the result
of advanced search. Many times I just want to know things like how many
accounts do not have a email address. A simple result counter would make life
much easier in that case.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsrea...macro query disable download failure
I have a macro which retrieve different queries from the web.
It happens that a resource at that moment isn''t available.
If this happens the querie gives a message which said unable to open
This message i don't want to display (other routines herefor).
I use "Application.DisplayAlerts = False" but this won't help.
Can anybody help me with this problem.
...Nz function in query
Hi All, the query below shows an error if any of the date in / date out
entries in the table are empty.
How can i impiment the Nz function to stop this.
SQL from query...
SELECT Employees.[First Name], Employees.[Last Name], Format([Normal start
Time],"h:nn AMPM") AS ['Normal Start Time], Format([Normal End Time],"h:nn
AMPM") AS ['Normal End Time], Format([scan time in],"h:nn AMPM") AS ['Scan
Time In], Format([scan time Out],"h:mm AMPM") AS ['Scan Time Out], Format(
[Scan Date In],"dddd dd,mm,yyyy") AS ['Scan Date ...Moving users to another OU results in unable to receive mail
We have got a problem where I cannot get my hands on.
In our exchange organization we use different aliasses for e-mail addresses
(@company.nl, @company.com, etc). We recently got another company and of
course the emails have to be deliverd in exchange. We took all te
arrangements and mail get delivered.
To seperate the new company users from the @company users, we created a sub
OU in the OU were the user accounts are located. And then the trouble begin.
I moved the user from the new company to this OU and they do not receive
e-mail on there @company.com address but they do receiv...how do i create subset and union in XPath Query
I need a XPath query, whcih ll return me subset of data....
check below xml stream
<label id="MyExpenseDetails_lbl" xlink:role="terseLabel">Short Expense
<label id="MyExpenseDetails_lbl" xlink:role="displayLabel">Expense
<label id="InternalExpense_lbl" xlink:role="displayLabel">Internal
<label id="ExternalExpense_lbl" xlink:role="terseLabel">Short External
See above xml data having 4 label ...Access 2003 query to Excel 2007 problem?
I have a query that I'd set up a while back for a user on an Access
2003 db. The main table has keys that point to other tables (schools
and departments). She needs the results in Excel, so we have been
exporting it directly to an excel file (v97-2003), which has been
working just fine. However, the other day when I was working with her
on this year's stuff, we'd run it once as a test, it worked fine, then
I made a minor change to add a field, and re-ran it, and got quite
The first time we ran it, the resulting Excel file had "resolved" the
k...Why does forumula show in cell instead of formula result?
Sometimes when I enter a formula into an Excel cell, the formula itself is
displayed in the cell instead of the formula result. However, when I open
Formula Wizard, the Wizard shows the correct formula result that should be
displayed in the cell. Why does this happen and how do I get it to stop
It sounds like the cell is formatted as TEXT.
Change the cell format to General. Then, with the cell still selected hit
function key F2 then hit Enter.
Microsoft Excel MVP
"Ashley" <Ashley@discussions.microsoft.com> wrote in message
news:6...Partial Text Match then enter desired result into appropriate colu
I have a list of company names in Column A.
On a separate tab I have a list of keywords.
What I'd like to do is to have a search done on the names in column A and if
any of the keywords on the separate tab match for it to then put in the
company type into column B.
Example of a company name in column A:
Get Things Done Construction Company
Keyword on separate tab: "construction:
Need the formula to return "construction" to Column B.
Thanks in Advance for any insight on this one!
Look in the help index for VLOOKUP
Microsoft MV...Double Payment
Customer double paid on an invoice. I now have the credit sitting in the
customer's account. We issued a return of the amount overpaid via charge
card. How do reverse the original payment creating a debit memo to wash the
customer's credit and have a negative amount in the Bank Deposit Entry module?
One way to do this (if you don't have the customer refund module) is create
a debit memo for the customer and apply to the over payment. Then enter a
Bank Transaction to deduct that amount from your checkbook. The Deposit
Entry module will be off by the amount of the Bank Tr...Import Access Query to Excel
I am trying to import MS Access Query to Excel.
I tried with "Data --> Import External Data --> Import Data" but I
found two issues.
First, "Select Table" lists only 5 of 15 query tables I made.
Second, data range only displays column headings, no record.
Both Access and Excel files are on local drive.
My computer is on domain network and I heard there is a security issue
with Import External Data within domain environment. However, I used
to do this on my previous employment within domain environment.
I appreciate any help!
wh...Rank Query With Groups (Array?)
Hello, I have a table of data with grades which are grouped by study. I need
to rank the grades relative to all the other grades in the same study. Below
is an example the study and grade fields with the desired outcome for the
Study Grade Rank
11111 89 1
11111 75 2
11111 65 3
22222 99 1
22222 87 2
Hopefully this can be done with simple SQL in a query and not with code but
I am open to anything that works. Many thanks in advance for your time and
Try this --
SELECT Study, Grade, (SELECT Count(*) FROM YourTable AS [XX] WHERE
You...Multiplying a cell and displaying the result in the same cell
Is it possible to perform a calculation on a cell (well a column) an
replace the data in the cell with the result? I have a spreadshee
which uploads data to a database (not sure which type - it's
mechanical estimating package) and I need to increase the price colum
by a certain percentage. I cannot add or delete any of the column
otherwise the upload won't work.
robspot's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1532
View this thread: http://www.excelfo...Web Query File Reference
Is there a Web Query File (.IQY) Format Reference anywhere?
...Routine to run/export queries
Looking for some links to examples that do the following (or something
1. user would create a whole bunch of queries themselves (using Access)
2. form that lets the user runs these queries to display results and/or
3. Would be good if user could select multiple queries and get multiple
resultsets to display or be exported to
an excel file with multiple worksheets. Example: user selects 5 queries and
wants an excel file made with 5 tabs with the 5 different outputs of the
queries, or the outputs of the 5 queries show in a form with multiple tabs
in Access (hide...Still unable to run successful query
I've used the IFF suggestions given but it returns only Not Active results.
I'm working on a case problem that reads:
Create a query to display all matching records from the tblProgram and
tblMember tables, selecting the ProgramType and MonthlyFee fields from the
tblProgram table, and the FirstName and LastName fields from the tblMember
Add a calculated field named MonthlyFeeStatus as the last column that equals
Active if the MembershipStatus field is equal to Active and equals Not Active
Assuming MembershipStatus is a string (varchar) data field,...Query Help Please
I have a relational db with a Client Table and a Project Table. The Client
table holds general info that usually doesn't change. The Project table has
a record for each project we've done for the client, and contains a Manager
field. The manager could change from year to year. I want to show by
current manager all the other projects for that client (regardless of who
previous manager was). Current manager is just the manager on the latest
project. I'm guessing I need a query of a query, but I'm stumped. Any
advice would be appreciated. Thanks.
Do you hav...Help Using APPEND Query
I'm looking to create a "LOG" table. This table will contain 4 fields;
1. SessionID - AutoNumber
2. DateTime - Text
3. User - Text
4. AccessLevel - Text
Purpose: I can eventually (6months or more from now) create a report to see
if more staff/users/admin are accessing the system and what times they are
accessing the system, am/pm etc.
I have created a public function which determines and returns the username
I have created a public function which determines and returns a string
identifying their access level. "Student" "Staff" "Adm...Activity Report/Query Questions
Im trying to get a report out of CRM (whether Crystal or Advanced Find) that
will pull all of the contacts that have not had an activity associated with
them in the past year.
Maybe it's me but i just cannot seem to find a way to do this. This must be
a common thing that most companies use so Im figuring it must be possible.
Has anyone done a data pull like this before and if so what did you do?
Thanks for any help
Both advanced find and Crystal reports using the Crystal Enhancements for CRM
use fetch XML for queries -- and this is the limiting factor in the type of
quer...Partial match for query criteria
I am trying to create a query that will deliver results based on matching
only a part of the text in a table field. For example, if I have a table that
contains the names, address and phone numbers of 10,000 businesses but I only
want my query to show me businesses that have the word "National" in their
name, how would I do that? I'm not sure how to set up the criteria.
Thanks for your help!
Use the Like operator with wildcards. In the 'criteria' row of the business
name column in query design view enter:
Or you can use a ...Query
I have crated a parameter query where I have a last name field and a first
name field from the same table, and want them strung together to be shown
like this Doe, John.
Does anyone know how to accomplish this?
In query design view enter this is an empty field "block":
FullName: [Last Name] & ", " & [First Name]
In SQL view
SELECT [Last Name] & ", " & [First Name] as FullName
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
OceanBreeze wrote...Need query to apply schedule to a category
I have created a sale schedule from 6pm-9pm Wed, Thr & Fri - named it
In RMS you must open each item - go to properties box - pricing tab - select
schedule button & then the schedule name -- item by item.
I want to apply this "sale schedule" to a CATEGORY using a query and a query
to remove the sale schedule from the same category after the sale is over.
My "NightSale" is schedule ID 9 - if that helps.
This week - apply "NightSale" schedule to all items in Category 1 & Category 2
End of week - remove "NightSale" sch...MS Query automatically fully qualifying SQL Select Statement
MS Query is fully qualifying my SQL Select statement which I a
creating using the Get External data function in Excel to get data fro
e.g. select * from person changes to select * fro
Is it possible to stop this happening or is there a different way I ca
the Get External Data function to get data from SQL Server?
I want to distribute the xls file with the queries but the databas
not be the same on other client machines. Table structure / login i
Message posted from http://www.ExcelForum.com
...Left join in query
I have a query set up with a one-to-many left join on two tables in order to
pull all records from one table (the "one" side) and the corresponding
records from another table (the "many" side), if they exist. It works well,
but only in THAT query. In other words, if I run that query, the records
show up as I want them to. But, if I then use that query in yet another
query to further manipulate the data, the only records that show up are the
records in the many table. Why and how can I fix it? I can post the query
I suspect you may h...Multiple Query questions
I'm trying to query a database of beach rentals and need a query with more
than 2 bedrooms, that rents for less than 1,000. When i put both criteria in,
it only registers one of them.
I also need to create a query for the highest and lowest weekly rate but
when I select Min or Max or doesn't show me anything.
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> I'm trying...