Query Not a Valid Name

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

Guest

Hello:

Access 2003

I'm mystified by a series of basic queries in a database I originally
created in Access 2000. They have simple names such as "qryCountByYr" but
when I try to run them, I get that invalid query name error message.There's
nothing unusual about these names.

Does anyone have an idea as to why that is?
 
Hello:

Access 2003

I'm mystified by a series of basic queries in a database I originally
created in Access 2000. They have simple names such as "qryCountByYr" but
when I try to run them, I get that invalid query name error message.There's
nothing unusual about these names.

Does anyone have an idea as to why that is?
Click to expand...

Try opening the query in SQL view and posting the SQL view here.
Perhaps the previous developer (you!?) created queries based on other
queries, and the base queries no longer exist.

John W. Vinson[MVP]
 
Hi John:

It's just a basic query, maybe I screwed up by designing it improperly?
Here's the SQL:

SELECT DISTINCTROW DatePart("yyyy",[tbl.exercise.date_exer]) AS YR,
Tblexercise.EXERCISE AS TYPE, Count(Tblexercise.EXERCISE) AS [Count]
FROM Tblexercise
GROUP BY DatePart("yyyy",[tbl.exercise.date_exer]), Tblexercise.EXERCISE;
 
Hello John:

I've been trying to tell you all day that I rebuilt the query, using the
same name that brought an error yesterday, and it seems to be working well. I
want the query to capture the current year from the [Date_Exer] field and
then count the different types of exercise I've done this year such as
"Treadmill", Weights", etc.

I'm new to Access queries but the rebuilt query seems to be doing well and
displays the current exercise totals for this year on the form. Here's the
SQL from the updated query. Please let me know if there's some way I can make
the query more efficent.

Thanks so much,
RT

SELECT DISTINCTROW DatePart("yyyy",Date()) AS YR, Tblexercise.EXERCISE AS
TYPE, Count(Tblexercise.EXERCISE) AS [Count]
FROM Tblexercise
WHERE (((Tblexercise.YR)=DatePart("yyyy",Date())))
GROUP BY DatePart("yyyy",Date()), Tblexercise.EXERCISE
ORDER BY Tblexercise.EXERCISE;
 
Hi John:

It's just a basic query, maybe I screwed up by designing it improperly?
Here's the SQL:

SELECT DISTINCTROW DatePart("yyyy",[tbl.exercise.date_exer]) AS YR,
Tblexercise.EXERCISE AS TYPE, Count(Tblexercise.EXERCISE) AS [Count]
FROM Tblexercise
GROUP BY DatePart("yyyy",[tbl.exercise.date_exer]), Tblexercise.EXERCISE;
Click to expand...

The syntax tbl.exercise.date_exer is very suspicious here. Are you
assuming that putting tbl. in front will open the Tables collection?
What IS the actual name of your table - Tblexercise, exercise, or
tbl.exercise?

If it's tblexercise, drop the . after tbl in both instances of the
DatePart expression.

John W. Vinson[MVP]
 
Hi John:

Wow! Getting onto this website today [Sunday] is a major hurdle.

Anyway, I solved the original query problem but I have a new one. First, let
me show you the query that solved my problem. I now have a subForm embedded
within the main form which queries all of the exercise I did and displays the
total, by type_exer, only for the current year. It works great! Here's the
SQL:

SELECT DISTINCTROW Tblexercise.EXERCISE AS TYPE, Count(Tblexercise.EXERCISE)
AS [Count]
FROM Tblexercise
GROUP BY Tblexercise.EXERCISE, DatePart("yyyy",[Date_Exer])
HAVING (((DatePart("yyyy",[Date_Exer]))=DatePart("yyyy",Date())))
ORDER BY Tblexercise.EXERCISE;
----------------------------------------------------------------------

New Problem:
-------------

I have a 2nd query which I use to count the [type_exer] records by year
for a report. It's a very simple query grouping by YEAR, yet every time I try
to run the report, the Access Parameter Query screen comes up and insists
that I enter a year. John, I don't understand this because there are no
parameters in the query. Here's the SQL for the query underlying the Report.

SELECT DISTINCTROW DatePart("yyyy",[Date_Exer]) AS [Year],
Tblexercise.EXERCISE AS [Type Exercise], Count(Tblexercise.EXERCISE) AS
[Count]
FROM Tblexercise
GROUP BY DatePart("yyyy",[Date_Exer]), Tblexercise.EXERCISE
ORDER BY Tblexercise.EXERCISE;

Any thoughts?

Thanks,
RT
 
I'm new to Access queries but the rebuilt query seems to be doing well and
displays the current exercise totals for this year on the form. Here's the
SQL from the updated query. Please let me know if there's some way I can make
the query more efficent.
Click to expand...

Yes, a couple of ways. Be sure that there is a nonunique Index on the
Yr field in your table (it's not redundant with a Date/Time field, I
hope?) so it can search it faster, and also on EXERCISE so it can sort
faster. You also should use the WHERE operator which filters the
records before totalling, rather than the (default) HAVING operator
which totals all the records and then deletes most of them.

Try

SELECT DISTINCTROW Year(Date()) AS YR,
Tblexercise.EXERCISE AS TYPE,
Count(*) AS [Count]
FROM Tblexercise
WHERE (((Tblexercise.YR)=Year(Date())))
GROUP BY Tblexercise.EXERCISE
ORDER BY Tblexercise.EXERCISE;


John W. Vinson[MVP]
 
Hello John:

I tried your query but it returned an empty dynaset so I modified it
slightly and it worked! Here is the current SQL version of the query.

SELECT DISTINCTROW Tblexercise.EXERCISE AS TYPE, Count(Tblexercise.EXERCISE)
AS [Count]
FROM Tblexercise
WHERE (((DatePart("yyyy",[Date_Exer]))=DatePart("yyyy",Date())))
GROUP BY Tblexercise.EXERCISE
ORDER BY Tblexercise.EXERCISE;

Thanks so much for allof your help and suggestions.I have another problem
with queries that I'll post as a new question.

RT
 
Hello John:

I tried your query but it returned an empty dynaset so I modified it
slightly and it worked! Here is the current SQL version of the query.

SELECT DISTINCTROW Tblexercise.EXERCISE AS TYPE, Count(Tblexercise.EXERCISE)
AS [Count]
FROM Tblexercise
WHERE (((DatePart("yyyy",[Date_Exer]))=DatePart("yyyy",Date())))
GROUP BY Tblexercise.EXERCISE
ORDER BY Tblexercise.EXERCISE;
Click to expand...

Since you have a Date_Exer field, I'd suggest a slight change that
won't affect the results but should make the query faster (if
Date_Exer is indexed: replace the WHERE clause with

WHERE [Date_Exer] >= DateSerial(Year(Date()), 1, 1) AND [Date_Exer] <
DateSerial(Year(Date()) + 1, 1, 1)


John W. Vinson[MVP]
 
Hello John:

WHERE [Date_Exer] >= DateSerial(Year(Date()), 1, 1) AND [Date_Exer] <
DateSerial(Year(Date()) + 1, 1, 1)

I inserted the expression you gave me and it worked perfectly. It appears as
if the query is executing faster. I assume this new expression speeds up the
process but I'm new to Access and I don't understand why. Can you please
explain what the new expression does that the OLD "Where" criteria failed to
do?


John Vinson said:
Hello John:

I tried your query but it returned an empty dynaset so I modified it
slightly and it worked! Here is the current SQL version of the query.

SELECT DISTINCTROW Tblexercise.EXERCISE AS TYPE, Count(Tblexercise.EXERCISE)
AS [Count]
FROM Tblexercise
WHERE (((DatePart("yyyy",[Date_Exer]))=DatePart("yyyy",Date())))
GROUP BY Tblexercise.EXERCISE
ORDER BY Tblexercise.EXERCISE;
Click to expand...

Since you have a Date_Exer field, I'd suggest a slight change that
won't affect the results but should make the query faster (if
Date_Exer is indexed: replace the WHERE clause with

WHERE [Date_Exer] >= DateSerial(Year(Date()), 1, 1) AND [Date_Exer] <
DateSerial(Year(Date()) + 1, 1, 1)


John W. Vinson[MVP]
Click to expand...
 
I have a 2nd query which I use to count the [type_exer] records by year
for a report. It's a very simple query grouping by YEAR, yet every time I try
to run the report, the Access Parameter Query screen comes up and insists
that I enter a year. John, I don't understand this because there are no
parameters in the query. Here's the SQL for the query underlying the Report.

SELECT DISTINCTROW DatePart("yyyy",[Date_Exer]) AS [Year],
Tblexercise.EXERCISE AS [Type Exercise], Count(Tblexercise.EXERCISE) AS
[Count]
FROM Tblexercise
GROUP BY DatePart("yyyy",[Date_Exer]), Tblexercise.EXERCISE
ORDER BY Tblexercise.EXERCISE;

Any thoughts?
Click to expand...

YEAR and COUNT are both reserved words. Access may be getting confused
about whether you mean the fieldname or the Year() function. Try
changing the fieldname.

John W. Vinson[MVP]
 
Hello John:

WHERE [Date_Exer] >= DateSerial(Year(Date()), 1, 1) AND [Date_Exer] <
DateSerial(Year(Date()) + 1, 1, 1)

I inserted the expression you gave me and it worked perfectly. It appears as
if the query is executing faster. I assume this new expression speeds up the
process but I'm new to Access and I don't understand why. Can you please
explain what the new expression does that the OLD "Where" criteria failed to
do?
Click to expand...

The old expression retrieved every single record in the table, and
called the DatePart() function on the Date_Exer field... so if you had
ten years' of data constituting 10,000 records, you'ld call the
function 10,000 times. Each resulting year integer would be compared
with the current year (it's smart enough to only call the
DatePart("yyyy", Date()) function once).

The new expression will use the Index on Date_Exer to retrieve only
those values between the first and last days of the year. Records from
other years won't even be examined.

John W. Vinson[MVP]
 
Hi John:

Thanks so much for the new query design and the explanation. That certainly
makes a lot of sense and explains why the query seems to execute almost
instantly with this updated expression.

Have a Great Day,
RT

John Vinson said:
Hello John:

WHERE [Date_Exer] >= DateSerial(Year(Date()), 1, 1) AND [Date_Exer] <
DateSerial(Year(Date()) + 1, 1, 1)

I inserted the expression you gave me and it worked perfectly. It appears as
if the query is executing faster. I assume this new expression speeds up the
process but I'm new to Access and I don't understand why. Can you please
explain what the new expression does that the OLD "Where" criteria failed to
do?
Click to expand...

The old expression retrieved every single record in the table, and
called the DatePart() function on the Date_Exer field... so if you had
ten years' of data constituting 10,000 records, you'ld call the
function 10,000 times. Each resulting year integer would be compared
with the current year (it's smart enough to only call the
DatePart("yyyy", Date()) function once).

The new expression will use the Index on Date_Exer to retrieve only
those values between the first and last days of the year. Records from
other years won't even be examined.

John W. Vinson[MVP]
Click to expand...
 
Hello John:

Since I'm relatively new to Access, I'm trying to learn as much as possible.
Would you be so kind as to explain how you constrcuted that query, especially
the part of all the number 1's. How do you know where to put them and what do
they mean?

WHERE [Date_Exer] >= DateSerial(Year(Date()), 1, 1) AND [Date_Exer] <
DateSerial(Year(Date()) + 1, 1, 1)

Thanks,
RT

John Vinson said:
Hello John:

WHERE [Date_Exer] >= DateSerial(Year(Date()), 1, 1) AND [Date_Exer] <
DateSerial(Year(Date()) + 1, 1, 1)

I inserted the expression you gave me and it worked perfectly. It appears as
if the query is executing faster. I assume this new expression speeds up the
process but I'm new to Access and I don't understand why. Can you please
explain what the new expression does that the OLD "Where" criteria failed to
do?
Click to expand...

The old expression retrieved every single record in the table, and
called the DatePart() function on the Date_Exer field... so if you had
ten years' of data constituting 10,000 records, you'ld call the
function 10,000 times. Each resulting year integer would be compared
with the current year (it's smart enough to only call the
DatePart("yyyy", Date()) function once).

The new expression will use the Index on Date_Exer to retrieve only
those values between the first and last days of the year. Records from
other years won't even be examined.

John W. Vinson[MVP]
Click to expand...
 
Hello John:

Since I'm relatively new to Access, I'm trying to learn as much as possible.
Would you be so kind as to explain how you constrcuted that query, especially
the part of all the number 1's. How do you know where to put them and what do
they mean?

WHERE [Date_Exer] >= DateSerial(Year(Date()), 1, 1) AND [Date_Exer] <
DateSerial(Year(Date()) + 1, 1, 1)
Click to expand...

Open the VBA editor (for example by typing Ctrl-G) and use Help to
search for the DateSerial function. This help topic will explain the
syntax of the function, and what those 1's mean...

John W. Vinson[MVP]
 
I've been struggling with the same error message today and resolved it. I
discovered that the criteria prompt [Enter criteria here.] included a period.
That was the problem. As soon as I deleted the period, my query started
working with the name I wanted.
 
Back
Top