Can't figure out SQL error

[rwr]

I'm trying to use the logic Allen Browne pointed me to, to
create a query to generate mtd and ytd totals.

I keep getting the "Run-time error 3265 item not found in
this collection" on the "Set qry = dbs.QueryDefs(tmpSQLString)".

I can't figure out what is wrong.

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qry As DAO.QueryDef
Dim tmpSQLString As String

Set dbs = CurrentDb
tmpSQLString = "SELECT [tblObservationMaster].[Observer],"
tmpSQLString = tmpSQLString &
"Year([tblObservationMaster].[ObservationDate]) AS TheYear, "
tmpSQLString = tmpSQLString &
"Month([tblObservationMaster].[ObservationDate]) AS TheMonth, "
tmpSQLString = tmpSQLString & " Count([tblObservationMaster].[Observer])
AS MonthCount "
tmpSQLString = tmpSQLString & " (Select Count(A.Observer) AS YTD, "
tmpSQLString = tmpSQLString & " FROM tblObservationMaster AS A INNER
JOIN tblObservationMaster ON [A].[Observer] =
[tblObservationMaster].[Observer], "
tmpSQLString = tmpSQLString & " Where [A].[ObservationDate] >=
DateSerial(Year([tblObservationMaster].[TheYear]), 1, 1), "
tmpSQLString = tmpSQLString & " And [A].[ObservationDate] <
DateSerial(Year([tblObservationMaster].[TheMonth]), "
tmpSQLString = tmpSQLString & "
Month([tblObservationMaster].[ObeservationDate])-1,1)) AS YTDCount "
tmpSQLString = tmpSQLString & " FROM tblObservationMaster INNER JOIN A
ON [tblObservationMaster].[Observer]=[A].[Observer] "
tmpSQLString = tmpSQLString & " GROUP BY
Year([tblObservationMaster].[TheYear]),
Month([tblObservationMaster].[TheMonth]);"
Set qry = dbs.QueryDefs(tmpSQLString)
Set rs = qry.OpenRecordset()
dbs.Execute = "SELECT * INTO tblYTDTemp FROM rs;"

What I'm trying to do is take the tblObservationMaster table and
calculate MTD and YTD totals by Each Observer.

Thanks for any help you may provide.
Ron

 

[Allen Browne]

Ron there are several issues with that SQL statement, e.g.:
- You need a comma after AS MonthCount.

- Your subquery aliases the table as "A", but then you tried to use A in the
main query. (Unless you really have a table named A that you want to use,
you don't use this in the main query.)

- I don't understand the last part of the WHERE clause in the subquery ,
i.e. the line:
Month([tblObservationMaster].[ObeservationDate])-1,1)

You might be best to get create a query and get this working first. Once you
have the query and it's subquery working you can then transfer it into the
string in your code. In fact, once you have the query working, you can use
this to get it into your code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

 

[rwr]

Ron there are several issues with that SQL statement, e.g.:
- You need a comma after AS MonthCount.

- Your subquery aliases the table as "A", but then you tried to use A in
the main query. (Unless you really have a table named A that you want to
use, you don't use this in the main query.)

- I don't understand the last part of the WHERE clause in the subquery ,
i.e. the line:
Month([tblObservationMaster].[ObeservationDate])-1,1)

You might be best to get create a query and get this working first. Once
you have the query and it's subquery working you can then transfer it
into the string in your code. In fact, once you have the query working,
you can use this to get it into your code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

Thanks Allen,

Ya, I can see the comma problem. The month command, I think should be +
not -. It is part of a dateserial command. I think the real problem is
trying to use the A in the main query. I'm using your example but I only
have one table tblObservationMaster. In you example you use Orders and
Order Details.

I'm confused about your statement:
"create a query and get this working first. Once you have the query and
it's subquery working you can then transfer". I did'nt think Access
query builder would allow subqueries.

Ron

 

[Bob Hairgrove]

I did'nt think Access
query builder would allow subqueries.

Well, I would say "yes and no" to this one. It is possible to use subqueries in
Access. You can either create the subquery the "normal" way (i.e. by typing in
the SQL statement in the SQL view window), or you can create another query as a
stand-alone query and add that to your final query as you would add another
table.

With the second method, you can create your final query totally by using the
graphical interface, but it won't create a true subquery. However, if allyou
need to do is create a join (inner or outer) between a table and the subquery,
you can do it this way.

For other types of subqueries (e.g. having criteria which include EXISTS or IN
statements, or their corresponding negations preceded by NOT), there is no
graphical tool to help you out. Likewise, you can create UNION or DDL queries in
Access, but only by typing in the corresponding SQL statements.

 

[Allen Browne]

Type the subquery into the Field row in query design.

Or, if you prefer, type it into SQL View. Later versions of Access will try
to highlight where the problem lies.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ron there are several issues with that SQL statement, e.g.:
- You need a comma after AS MonthCount.

- Your subquery aliases the table as "A", but then you tried to use A in
the main query. (Unless you really have a table named A that you want to
use, you don't use this in the main query.)

- I don't understand the last part of the WHERE clause in the subquery ,
i.e. the line:
Month([tblObservationMaster].[ObeservationDate])-1,1)

You might be best to get create a query and get this working first. Once
you have the query and it's subquery working you can then transfer it
into the string in your code. In fact, once you have the query working,
you can use this to get it into your code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

Thanks Allen,

Ya, I can see the comma problem. The month command, I think should be +
not -. It is part of a dateserial command. I think the real problem is
trying to use the A in the main query. I'm using your example but I only
have one table tblObservationMaster. In you example you use Orders and
Order Details.

I'm confused about your statement:
"create a query and get this working first. Once you have the query and
it's subquery working you can then transfer". I did'nt think Access query
builder would allow subqueries.

Ron

 

[rwr]

I'm trying to use the logic Allen Browne pointed me to, to
create a query to generate mtd and ytd totals.

--Cut --

Allen and Bob, I want to thank you for your help. I kept getting
a syntax error in the queries and couldn't figure it out.
This morning I made 1 change and its working great. I must be thinking
about spring... don't know. I truly don't know how any of us could do
what we do without newsgroups.

Again, thanks for your help.

Ron