my query is prompting me for parameters I haven't created

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My coworker and I both run the same macro (which contains 5 query runs), but
with different results. She is able to run it successfully from start to
finish. When I run the same macro, however, I am prompted to enter criteria
for parameters that have not been created.

She's running Access 2002 (10.2627.2625)
I'm running Access 2003 (11.6566.6568)
 
Please show us the SQL that's asking for a parameter and the parameter that
it's asking for.

If it happens to be a crosstab query, A03 is a little picky in that you must
define parameters data types explicitly. In query design view, go to Query,
Parameters and enter in any parameters in your criteria.
 
Below is the Sql for the 1st 3 queries in the macro. Once the macro gets to
the 3rd query (qLoad) I get prompted to enter data for each field in the
first 2 queries.

SELECT DISTINCT DataSCS.Student, DataSCS.CourseLevel, DataSCS.Cred,
DataSCS.Schools, DataSCS.ReportTerm, DataSCS.School, DataSCS.Dept,
DataSCS.CourseSection, DataSCS.Location, DataSCS.CredType,
DataSCS.CourseAcadLevel, DataSCS.Status, DataSCS.CurrentStatus,
DataSCS.SecAcadLevel, DataSCS.StcAcadLevel, DataSCS.StcStartDate,
DataSCS.ScsStartDate, DataSCS.ScsTerm, DataSCS.SecTerm, DataSCS.School1,
DataSCS.ExtractDate, DataSCS.ActiveProgs, DataSCS.SchoolActiveProgs,
DataSCS.LvlofActProgs
Sql for qDATASCS query:

FROM DataSCS
GROUP BY DataSCS.Student, DataSCS.CourseLevel, DataSCS.Cred,
DataSCS.Schools, DataSCS.ReportTerm, DataSCS.School, DataSCS.Dept,
DataSCS.CourseSection, DataSCS.Location, DataSCS.CredType,
DataSCS.CourseAcadLevel, DataSCS.Status, DataSCS.CurrentStatus,
DataSCS.SecAcadLevel, DataSCS.StcAcadLevel, DataSCS.StcStartDate,
DataSCS.ScsStartDate, DataSCS.ScsTerm, DataSCS.SecTerm, DataSCS.School1,
DataSCS.ExtractDate, DataSCS.ActiveProgs, DataSCS.SchoolActiveProgs,
DataSCS.LvlofActProgs
HAVING (((DataSCS.CurrentStatus) Like "A" Or (DataSCS.CurrentStatus)="N"));


Sql for qDATACS query:

SELECT DISTINCT DataCS.[Reporting Term], DataCS.Term, DataCS.[Section Name],
Sum(DataCS.Seats) AS Seats, Sum(DataCS.Revenue) AS Revenue, DataCS.Subject,
DataCS.Section, DataCS.[Acad Level], DataCS.Depts, DataCS.Location,
DataCS.[Start Date], DataCS.[End Date], DataCS.School, DataCS.ExtractDate,
DataCS.InsertDash AS Course
FROM DataCS
GROUP BY DataCS.[Reporting Term], DataCS.Term, DataCS.[Section Name],
DataCS.Subject, DataCS.Section, DataCS.[Acad Level], DataCS.Depts,
DataCS.Location, DataCS.[Start Date], DataCS.[End Date], DataCS.School,
DataCS.ExtractDate, DataCS.InsertDash;

Sql for qLoad query:
SELECT DISTINCT qDataSCS.Student, qDataSCS.CourseLevel, qDataSCS.Cred,
qDataSCS.ReportTerm, qDataSCS.Dept, qDataSCS.CourseSection, qDataCS.Location,
qDataSCS.CourseAcadLevel, qDataSCS.Status, qDataSCS.CurrentStatus,
qDataSCS.SecAcadLevel, qDataSCS.StcAcadLevel, qDataSCS.StcStartDate,
qDataSCS.ScsStartDate, qDataSCS.School1, qDataCS.ExtractDate, Load.[Full
Load], [cred]/[full load] AS FTE, qDataCS.[Start Date], qDataCS.Course,
qDataSCS.ActiveProgs, qDataSCS.SchoolActiveProgs, qDataSCS.LvlofActProgs,
qDataCS.Term, qDataCS.Revenue
FROM (qDataCS LEFT JOIN qDataSCS ON (qDataCS.Term = qDataSCS.SecTerm) AND
(qDataCS.[Acad Level] = qDataSCS.SecAcadLevel) AND (qDataCS.[Section Name] =
qDataSCS.CourseSection) AND (qDataCS.[Reporting Term] = qDataSCS.ReportTerm))
LEFT JOIN Load ON qDataSCS.SecAcadLevel = Load.Level
GROUP BY qDataSCS.Student, qDataSCS.CourseLevel, qDataSCS.Cred,
qDataSCS.ReportTerm, qDataSCS.Dept, qDataSCS.CourseSection, qDataCS.Location,
qDataSCS.CourseAcadLevel, qDataSCS.Status, qDataSCS.CurrentStatus,
qDataSCS.SecAcadLevel, qDataSCS.StcAcadLevel, qDataSCS.StcStartDate,
qDataSCS.ScsStartDate, qDataSCS.School1, qDataCS.ExtractDate, Load.[Full
Load], [cred]/[full load], qDataCS.[Start Date], qDataCS.Course,
qDataSCS.ActiveProgs, qDataSCS.SchoolActiveProgs, qDataSCS.LvlofActProgs,
qDataCS.Term, qDataCS.Revenue
HAVING (((qDataSCS.School1)<>"eli" And (qDataSCS.School1)<>"cnsrt" And
(qDataSCS.School1)<>"plus"))
ORDER BY qDataSCS.Student;
 
There seems to be a glitch with the import process. Instead of selecting
"Get External Data" on the File menu (which appears to be the source of my
problems), I right-click in the database window and select "Import". After
importing my Table, Queries, and Macro this way, I experience no problems
when running the macro.
Comments?
 
The first query is very strange in that you have both a DISTINCT clause and
GROUP BY all the fields. There is no summing or counting going on either. I'd
recommend getting rid of either the DISTINCT or the GROUP BYs (plus you'll
have to change the HAVING to a WHERE clause).

Same goes for the second query except that there is some summing. In this
case, try dumping the DISTINCT.

The third query also does the DISINCT and GROUP BY. Again try removing the
DISTINCT. If you are then getting duplicates, there may be a problem with the
relationships between your tables.

You also say that your coworker is using a different version of Access. Are
you both hitting the same tables? If not, possibly there are other design
issues causing your problems.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


KimF said:
Below is the Sql for the 1st 3 queries in the macro. Once the macro gets to
the 3rd query (qLoad) I get prompted to enter data for each field in the
first 2 queries.

SELECT DISTINCT DataSCS.Student, DataSCS.CourseLevel, DataSCS.Cred,
DataSCS.Schools, DataSCS.ReportTerm, DataSCS.School, DataSCS.Dept,
DataSCS.CourseSection, DataSCS.Location, DataSCS.CredType,
DataSCS.CourseAcadLevel, DataSCS.Status, DataSCS.CurrentStatus,
DataSCS.SecAcadLevel, DataSCS.StcAcadLevel, DataSCS.StcStartDate,
DataSCS.ScsStartDate, DataSCS.ScsTerm, DataSCS.SecTerm, DataSCS.School1,
DataSCS.ExtractDate, DataSCS.ActiveProgs, DataSCS.SchoolActiveProgs,
DataSCS.LvlofActProgs
Sql for qDATASCS query:

FROM DataSCS
GROUP BY DataSCS.Student, DataSCS.CourseLevel, DataSCS.Cred,
DataSCS.Schools, DataSCS.ReportTerm, DataSCS.School, DataSCS.Dept,
DataSCS.CourseSection, DataSCS.Location, DataSCS.CredType,
DataSCS.CourseAcadLevel, DataSCS.Status, DataSCS.CurrentStatus,
DataSCS.SecAcadLevel, DataSCS.StcAcadLevel, DataSCS.StcStartDate,
DataSCS.ScsStartDate, DataSCS.ScsTerm, DataSCS.SecTerm, DataSCS.School1,
DataSCS.ExtractDate, DataSCS.ActiveProgs, DataSCS.SchoolActiveProgs,
DataSCS.LvlofActProgs
HAVING (((DataSCS.CurrentStatus) Like "A" Or (DataSCS.CurrentStatus)="N"));


Sql for qDATACS query:

SELECT DISTINCT DataCS.[Reporting Term], DataCS.Term, DataCS.[Section Name],
Sum(DataCS.Seats) AS Seats, Sum(DataCS.Revenue) AS Revenue, DataCS.Subject,
DataCS.Section, DataCS.[Acad Level], DataCS.Depts, DataCS.Location,
DataCS.[Start Date], DataCS.[End Date], DataCS.School, DataCS.ExtractDate,
DataCS.InsertDash AS Course
FROM DataCS
GROUP BY DataCS.[Reporting Term], DataCS.Term, DataCS.[Section Name],
DataCS.Subject, DataCS.Section, DataCS.[Acad Level], DataCS.Depts,
DataCS.Location, DataCS.[Start Date], DataCS.[End Date], DataCS.School,
DataCS.ExtractDate, DataCS.InsertDash;

Sql for qLoad query:
SELECT DISTINCT qDataSCS.Student, qDataSCS.CourseLevel, qDataSCS.Cred,
qDataSCS.ReportTerm, qDataSCS.Dept, qDataSCS.CourseSection, qDataCS.Location,
qDataSCS.CourseAcadLevel, qDataSCS.Status, qDataSCS.CurrentStatus,
qDataSCS.SecAcadLevel, qDataSCS.StcAcadLevel, qDataSCS.StcStartDate,
qDataSCS.ScsStartDate, qDataSCS.School1, qDataCS.ExtractDate, Load.[Full
Load], [cred]/[full load] AS FTE, qDataCS.[Start Date], qDataCS.Course,
qDataSCS.ActiveProgs, qDataSCS.SchoolActiveProgs, qDataSCS.LvlofActProgs,
qDataCS.Term, qDataCS.Revenue
FROM (qDataCS LEFT JOIN qDataSCS ON (qDataCS.Term = qDataSCS.SecTerm) AND
(qDataCS.[Acad Level] = qDataSCS.SecAcadLevel) AND (qDataCS.[Section Name] =
qDataSCS.CourseSection) AND (qDataCS.[Reporting Term] = qDataSCS.ReportTerm))
LEFT JOIN Load ON qDataSCS.SecAcadLevel = Load.Level
GROUP BY qDataSCS.Student, qDataSCS.CourseLevel, qDataSCS.Cred,
qDataSCS.ReportTerm, qDataSCS.Dept, qDataSCS.CourseSection, qDataCS.Location,
qDataSCS.CourseAcadLevel, qDataSCS.Status, qDataSCS.CurrentStatus,
qDataSCS.SecAcadLevel, qDataSCS.StcAcadLevel, qDataSCS.StcStartDate,
qDataSCS.ScsStartDate, qDataSCS.School1, qDataCS.ExtractDate, Load.[Full
Load], [cred]/[full load], qDataCS.[Start Date], qDataCS.Course,
qDataSCS.ActiveProgs, qDataSCS.SchoolActiveProgs, qDataSCS.LvlofActProgs,
qDataCS.Term, qDataCS.Revenue
HAVING (((qDataSCS.School1)<>"eli" And (qDataSCS.School1)<>"cnsrt" And
(qDataSCS.School1)<>"plus"))
ORDER BY qDataSCS.Student;


Jerry Whittle said:
Please show us the SQL that's asking for a parameter and the parameter that
it's asking for.

If it happens to be a crosstab query, A03 is a little picky in that you must
define parameters data types explicitly. In query design view, go to Query,
Parameters and enter in any parameters in your criteria.
Click to expand...
Click to expand...
 
Back
Top