Solved

Query Criertia (Drop Down Box)

Posted on 2013-05-15
11
416 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 39168941
or you can do

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

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 119

Expert Comment

by:Rey Obrero
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now