Solved

Query Criertia (Drop Down Box)

Posted on 2013-05-15
11
418 Views
Last Modified: 2013-05-15
is it possible when you ask a question in query criteria like, Which Site?, to have the potential answers as a drop down box and not have to type in the whole answer?
0
Comment
Question by:SweetingA
  • 6
  • 4
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39168823
<to have the potential answers as a drop down box >  No
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39168829
The way to do that is to use a form to gather up your query criteria.

So, for example, the form could have a combobox with various choices available for Site; your query then looks like:

SELECT *
FROM SomeTable
WHERE Site = Forms![NameOfForm]![NameOfCombobox]

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39168941
or you can do

SELECT *
FROM SomeTable
WHERE Site Like [Which Site] & "*"
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:SweetingA
ID: 39169017
I get an error, the syntax in this subquery is incorrect, check syntax and enclose in parentheses.

SELECT * FROM tbl_Site WHERE Site Like [Which Site] & "*"

I tried from a form also and got the same error, any ideas what i am doing wrong?

Thanks for the help
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39169081
try doing a compact and repair,

then try the query again

is  tbl_Site a local table or linked table?

if linked to an SQL table, use


SELECT * FROM tbl_Site WHERE Site Like [Which Site] & "%"
0
 

Author Comment

by:SweetingA
ID: 39169214
No its not a linked table it is a local table, i have tried a compact and repair and still no joy?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39169259
upload a copy of your db


delete the query you created, then do a compact and repair

now create a new query

copy and paste this in SQL view of the query

SELECT *
FROM tbl_Site
WHERE [Site] Like [Which Site] & "*"
0
 

Author Comment

by:SweetingA
ID: 39169291
Attached is the original SQL - what do i change it to?

I was not changing the SQL before i was adding the script to the criteria field, hence the problem, sorry for the confusion

SELECT qry_CO_MonthlyPPM_PreFilter2.Site, qry_CO_MonthlyPPM_PreFilter2.Year, qry_CO_MonthlyPPM_PreFilter2.Month, qry_CO_MonthlyPPM_PreFilter2.[Delivered Quantity], qry_CO_MonthlyPPM_PreFilter2.[Rejected Quantity], qry_CO_MonthlyPPM_PreFilter2.PPM, qry_CO_MonthlyPPM_PreFilter2.SortID
FROM qry_CO_MonthlyPPM_PreFilter2
WHERE (((qry_CO_MonthlyPPM_PreFilter2.Site)=[Which Site?]) AND ((qry_CO_MonthlyPPM_PreFilter2.Year)=Year(Now())))
ORDER BY qry_CO_MonthlyPPM_PreFilter2.Site, qry_CO_MonthlyPPM_PreFilter2.SortID;

Thanks
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39169334
try this one, create a new query, (so you don't messed your old query)

copy and paste this


SELECT qry_CO_MonthlyPPM_PreFilter2.Site, qry_CO_MonthlyPPM_PreFilter2.Year, qry_CO_MonthlyPPM_PreFilter2.Month, qry_CO_MonthlyPPM_PreFilter2.[Delivered Quantity], qry_CO_MonthlyPPM_PreFilter2.[Rejected Quantity], qry_CO_MonthlyPPM_PreFilter2.PPM, qry_CO_MonthlyPPM_PreFilter2.SortID
FROM qry_CO_MonthlyPPM_PreFilter2
WHERE (((qry_CO_MonthlyPPM_PreFilter2.Site) Like [Which Site?] & "*") AND ((qry_CO_MonthlyPPM_PreFilter2.Year)=Year(Now())))
ORDER BY qry_CO_MonthlyPPM_PreFilter2.Site, qry_CO_MonthlyPPM_PreFilter2.SortID;


in this query, you can type a partial name of site and it will return all that begins with what you typed in the Input box
0
 

Author Closing Comment

by:SweetingA
ID: 39169407
OK its not a pick list or drop down but its better than users typing the whole thing.

Thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39169422
why a Grade of B?

the answer to your original question is at http:#a39168823 

the query is just an alternative method.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question