Q: Parameter driven queries sometimes ask multiple times...

MS Office Access


  • 1. Perhaps my question was misleading.
    in the design view click on the propperty sheet If this doesn't show the query properties, click on the area below where you have set your fields and filters, this will show the query propperties > On Wednesday, December 05, 2007 10:46 AM Nor wrote: > I am working with an ODBC connection to an SQL database. In attempting to > create a simple query for unique values in a field, I would expect to right > click in the quey and request properties. The only information that appears > in the property sheeet is the data source name. Is it a problem with the > ODBC connection? >> On Wednesday, December 05, 2007 1:22 PM ruper wrote: >> You need to know what tables and columns are in the database you are >> connecting to. I dont think Access can tell that since its not within the >> Access database. >> >> -Dorian >> >> "Norm" wrote: >>> On Wednesday, December 05, 2007 5:07 PM Nor wrote: >>> Perhaps my question was misleading. With previous versions of access, I was >>> able to set the property of the query to unique values and when I try to >>> perform the same request I do not see this as an option. The tables are in a >>> SQL database. I am using and ODBC connection to the tables. >>> >>> "mscertified" wrote:
  • 2. Really fundamental QUERY question!
    I'm sure I'm being a dolylum here and missing something obvious, so feel free to call me any names, but ONLY after you've answered my question successfully! If have 3 columns in my query:- Value A Value B Value (B minus A) but I only want a line to show when (B minus A) =2 So I have:- Column A (with no criteria) Column B (with no criteria) In Column C I have "Expr1:[B] - [A]" but when I put "=2" in criteria, it doesn't like it. Well, it passes the syntax ok, but when I run it, it comes up with a box wanting a parameter to be entered! A workmate thought it might be because "Expr1" is just a temporary working field, so you can't do tests on it, but I find that hard to believe..... SURELY it must be quite often that people need to check the value of a working field ????? I can't believe that Microsoft would not allow for that! Thanks!
  • 3. CrossTab Combining 2 fields into Column heading
    I've never done a crosstab where I combine 2 fields into the column heading.. The query is as follows: TRANSFORM Last(LMMI_Assessment.TestScore) AS TestScore SELECT LMMI_Assessment.StudentNumber, Max(LMMI_Assessment.TestingDate) AS MaxTestingDate FROM LMMI_Assessment GROUP BY LMMI_Assessment.StudentNumber PIVOT LMMI_Assessment.[AssessmentTestName] & "-" & AssessmentPartName; When I don't name the column headings, the results show as "3rd Ach-Math", 4th Ach-Reading", etc. However, when I try to name the fields as column headings in the properties box (as "3rd Ach-Math" etc) so that I can use these fields in another query, I get empty columns. Currently the 2 fields are 255 characters -- does that make a difference?
  • 4. Help Disambiguate my Join
    Hello Helpful People. I have an ambiguous join - I understand why it is ambiguous, I just cannot figure out a way around it to get the correct set of results. I have 2 tables, AllPlayers and PlayerHistory. The key fields are playerID and YearID - where a player can have 0 or 1 records in a year in either table. A player record can exist in multiple years - 1 or more - in either table. So, I do an outer join on playerId to get the set of all playerIDs. I want to use a query to add information from PlayerHistory to the AllPlayer information. So, I need to join on playerID and yearID, but if I add in yearID in the join, it becomes ambiguous. Doing the first join, saving the query, and making a second query using the first query and PlayerHistory, gets me the same ambiguity problem. So, what little technique am I missing?

Q: Parameter driven queries sometimes ask multiple times...

Postby TWFyaw » Thu, 29 Sep 2005 03:39:22 GMT

Using Access 2000.

Okay, sometimes I'll write a query that has date parameters, something like
"select * from table where [process_date] between [select start date] and 
[select end date]"

[select start date] and [select end date] are parameters where the user 
enters the start/end dates. 

I've done this query before, no problems. However, on one particular query, 
it'll ask the user to supply the start and end dates 3 times. It doesn't 
always do this. When I compact the db, it goes back to the regular asking 1 
time. But then it goes back to asking 3 times after running the query. 

I doubt if this is a query design problem. If it was, the error would be 
consistent, but it's not. I'm assuming it's some sort of bug and am wondering 
if anyone else has come across it.


RE: Q: Parameter driven queries sometimes ask multiple times...

Postby T2Zlcg » Thu, 29 Sep 2005 05:43:01 GMT

Not with this error, but some other strange messages, I found out that the 
best thing to do is to create a new query.
Dont copy and paste the sql, write the all thing, it had 90% of success
I hope that helped
Good luck

RE: Q: Parameter driven queries sometimes ask multiple times...

Postby JzY5IENhbWFybw » Thu, 29 Sep 2005 06:09:04 GMT

Hi, Mark.

The two most likely reasons you are getting this effect that "accummulates" 
over time are:

1.)  Track Name AutoCorrect is turned on;  (Turn this off!) or

2.)  A sort order or filter was applied to the query while it was open, then 
the user selected the "Yes" button when prompted to save the changes to the 
query.  To get rid of these extra prompts, just open the query in Design 
View, right click in the upper pane of the QBE grid, and select 
"Properties..." from the pop-up menu.  Scroll down to the Order By and Filter 
Properties and delete these values, then save the form and open it in 
Datasheet View again.  You should only be prompted once for the parameter -- 
until someone applies another sort order or filter again.


See  http://www.**--****.com/  for all your database needs.
See  http://www.**--****.com/  for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will 
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question 
"Did this post answer your question?" at the bottom of the message, which 
adds your question and the answers to the database of answers.  Remember that 
questions answered the quickest are often from those who have a history of 
rewarding the contributors who have taken the time to answer questions 

RE: Q: Parameter driven queries sometimes ask multiple times...

Postby TWFyaw » Thu, 29 Sep 2005 08:45:04 GMT

Thanks! I gotta check out if #2 is the case. Now I actually want to see the 
error again...


Similar Threads:

1.Q: query asks for parameters multiple times

Hi all,

Using Access 2003. I have a query that's based on one table. I need to 
search a few fields for text so I concatenated those fields and have the 
criteria as shown below: 

Field: [CSFNM1] & [CSLNM1] & [CSADD1] & [CSADD2] & [CSADD3] & [TNFNAM] & 
Criteria:  Like "*" & [Enter Search Text] & "*"

All other fields in the query are there for viewing, ie. there's no other 
criteria. I have added [Enter Search Text] as a query parameter, set to text. 
There are no other query parameters. 

For some reason, when I run the query, it asks me to fill the [Enter Search 
Text] parameter 4 times. When it does run, it returns what I'm looking for, 
but why does it have to ask 4 times? 


2.query filter asking multiple times


I have a query which i have created by two linked tables.  I have then based 
a report on this query.  In the query i have written [Enter Date] in the date 
column and [Enter Shift] in the shift column.  When i open the query it asks 
me once to enter the date and enter the shift and seems to work.  When I open 
the report it asks enter date and enter shift multipl times (like it is 
asking per page)  What am i doing wrong?

3.Issue with multiple parameter driven form- based Query

Ok, here's the dilemma.  9 variables in drop downs on my form that 
am using as values for a parameter query. 

In my criteria for one of the fields in the query there is:
Criteria: [Forms]![Sort]![Contact]
Or: [Forms]![Sort]![Contact] Is Nul

This brings back information only if I put in a value on the form
when I don't put in a value the return is totally blank. 

When I recode to:
Criteria: Where Contact Like Forms![Sort]!Contac
Or: Forms![Sort]![Contact] like "*

I get both the ones I need and all the rest (because of wildcard).
How do I get the search to return ONLY the value I put on the form
OR everything if no value is specified.  Basically I want somethin

Criteria: [Forms]![Sort]![Criteria
Or: Where Contact Like Forms![Sort]![Criteria] = null sub "*

When I put this all in the criteria line:
[Forms]![Sort]![Contact] OR [Forms]![Sort]![Contact] Is Nul
I get the correct response when I enter a dropdown selection on th
form (contact), but when I don't enter a contact I get nada...

Any advice

4.multiple apps oppening by themselves sometimes 20 at a time

Access or Word open, all of a sudden 10-20-30 windows open!!! as if you have 
double clicked the app icon 10-20-30 times, all blank not just a repeat of 
the app originally opend. Does it with word access etc so office is affected 
in someway.
Happens in a split second, no warnings at all.

Any ideas?


5.parameter query prompting multiple times

I'm having a problem with subreports based on parameter query, however, I 
don't understand about the master child fields.

My report is set up with one subreport (that will only return one value) in 
the page footer section, another 2 subreports (returning multiple values) are 
setup in the report footer section.  All are run off of parameter queries.  
They all have the date (warehouse load or unload) is common.  The report 
currently prompts for each query 3 times to open the report.

Can I make this work for my situation?

"Marshall Barton" wrote:

> JohnLute wrote:
> >I posted this previously but the resolution didn't work and was rather 
> >complicated. I'm wondering if there's a more simple approach.
> >
> >I have a parameter query in a subreport that works fine. However, when I add 
> >this subreport to the main report and execute it the parameter query prompts 
> >me four times before it finally returns the report. The Master/Child fileds 
> >are setup properly.
> >
> >I can't see where/why this is happening. Any ideas?
> You are being prompted each time the subreport appears in
> the main report.
> A better way is to use a form for the user to enter the
> parameter value.  Then modify the query to refer to the text
> box on the form instead of using a prompt string (e.g.
> Forms!theparameterform.thetextbox).
> -- 
> Marsh
> MVP [MS Access]

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 75 guest