Can query not run in VBA *and* run in Access?

  • Thread starter Thread starter VM
  • Start date Start date
V

VM

I have this query that I built in MS Access that's something like this:
SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#],
FROM Assets LEFT JOIN (SELECT * FROM History WHERE TranDate >= #1/1/2003#
AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo = T.AssetNo WHERE
Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<= #2/1/2003# AND
T.AssetNo Is Null

When I execute it through Acces it'll return a valid recordset.
When I execute it through VBA (the same exact query), it'll return a
run-time error '3306': "You have written a subquery that can return more
than one field without using the EXISTS...."

Is this possible?

Thanks,
VM
 
VM said:
I have this query that I built in MS Access that's something like
this: SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#],
FROM Assets LEFT JOIN (SELECT * FROM History WHERE TranDate >=
#1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo =
T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And
Assets.Purch_Date<= #2/1/2003# AND T.AssetNo Is Null

When I execute it through Acces it'll return a valid recordset.
When I execute it through VBA (the same exact query), it'll return a
run-time error '3306': "You have written a subquery that can return
more than one field without using the EXISTS...."

Is this possible?
Click to expand...

Seems odd. Although the query designer will rewrite this query slightly
to conform to its own syntax, the query looks okay to me. I think
you're going to have to post both the exact SQL from the query
designer's SQL View, and the exact VBA code that you run to get this
error.
 
They're exactly the same.

This is the query in the Access SQL view (I copy/pasted from the VBA form to
the SQL view):

SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null
_________________________________
This is the code in VBA with the *exact* sql query :
Dim strDocName As String, strSQL As String
Dim datBefore As Date, datAfter As Date

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "AssetsNotInventoriedReport_2"

strSQL = "SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo
= T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And Assets.Purch_Date<=
#2/1/2003# AND T.AssetNo Is Null"

DoCmd.OpenReport "AssetsNotInventoriedReport_2", acViewPreview, , strSQL


In the SQL view, it'll bring me the correct recordset. When running through
VBA I'll get the error 3306: "You have written a subquery that can return
more than one field without using the EXISTS reserved word in the main
query's FROM clause. Revise the SELECT statement of the subquery to request
only one field."

Note: While typing the error I did notice that the error tells me that my
subquery should only request only *one* field. But that would mean that the
engine that runs my VBA query is different than the Jet engine that runs my
Access query.

Vaughn

Dirk Goldgar said:
VM said:
I have this query that I built in MS Access that's something like
this: SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#],
FROM Assets LEFT JOIN (SELECT * FROM History WHERE TranDate >=
#1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON Assets.AssetNo =
T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And
Assets.Purch_Date<= #2/1/2003# AND T.AssetNo Is Null

When I execute it through Acces it'll return a valid recordset.
When I execute it through VBA (the same exact query), it'll return a
run-time error '3306': "You have written a subquery that can return
more than one field without using the EXISTS...."

Is this possible?
Click to expand...

Seems odd. Although the query designer will rewrite this query slightly
to conform to its own syntax, the query looks okay to me. I think
you're going to have to post both the exact SQL from the query
designer's SQL View, and the exact VBA code that you run to get this
error.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
They're exactly the same.

This is the query in the Access SQL view (I copy/pasted from the VBA
form to the SQL view):

SELECT Assets.AssetNo, T.TransType, Assets.User1, Assets.User2,
Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM History WHERE
TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# ) AS T ON
Assets.AssetNo = T.AssetNo WHERE Assets.Purch_Date >= #1/1/2003# And
Assets.Purch_Date<= #2/1/2003# AND T.AssetNo Is Null
_________________________________
This is the code in VBA with the *exact* sql query :
Dim strDocName As String, strSQL As String
Dim datBefore As Date, datAfter As Date

datBefore = Me.BeforeDate.Value
datAfter = Me.AfterDate.Value

strDocName = "AssetsNotInventoriedReport_2"

strSQL = "SELECT Assets.AssetNo, T.TransType, Assets.User1,
Assets.User2, Assets.[Serial#] FROM Assets LEFT JOIN (SELECT * FROM
History WHERE TranDate >= #1/1/2003# AND TranDate <= #2/1/2003# )
AS T ON Assets.AssetNo = T.AssetNo WHERE Assets.Purch_Date >=
#1/1/2003# And Assets.Purch_Date<= #2/1/2003# AND T.AssetNo Is Null"

DoCmd.OpenReport "AssetsNotInventoriedReport_2", acViewPreview, ,
strSQL


In the SQL view, it'll bring me the correct recordset. When running
through VBA I'll get the error 3306: "You have written a subquery
that can return more than one field without using the EXISTS reserved
word in the main query's FROM clause. Revise the SELECT statement of
the subquery to request only one field."

Note: While typing the error I did notice that the error tells me
that my subquery should only request only *one* field. But that would
mean that the engine that runs my VBA query is different than the Jet
engine that runs my Access query.
Click to expand...

But that's not correct syntax for the WhereCondition argument of the
DoCmd.OpenReport method! No wonder Access is getting confused. The
WhereCondition argument must be a valid SQL "WHERE" clause, but without
the WHERE keyword. This clause will be used to filter the report's
recordsource query. But you've passed a complete SQL SELECT statement.
Apparently it's being interpreted as a WHERE-clause subquery, but
regardless of how it's being interpreted, it won't work.

It seems to me that you either need to set the report's RecordSource
property at run time -- possibly by passing the SQL statement to the
report via OpenArgs (if using A2K2) or by some other mechanism, and then
assigning it to the property in the report's Open event -- or you need
to rephrase the string so that it's a valid WHERE clause with a
subquery, that can be applied to the report's RecordSource. Given a
choice, I would do the former. If you have to do the latter, it might
look like this:

Dim strWhere As String

strWhere = "AssetNo In (" & _
"SELECT Assets.AssetNo FROM Assets " & _
"LEFT JOIN (" & _
"SELECT * FROM History " & _
"WHERE TranDate >= #1/1/2003# " & _
"AND TranDate <= #2/1/2003#" & _
" ) AS T " & _
"ON Assets.AssetNo = T.AssetNo " & _
"WHERE Assets.Purch_Date >= #1/1/2003# " & _
"And Assets.Purch_Date<= #2/1/2003# " & _
"AND T.AssetNo Is Null" & _
")"

DoCmd.OpenReport _
"AssetsNotInventoriedReport_2", _
acViewPreview, , strWhere

However, as I said, I think the best thing to do is probably to set the
report's RecordSource property at run time.
 
VM,
I cannot point you in the right direction, but I can agree with you
that an SQL query may not translate into VBA. I pasted an SQL
statement from Queries in RecordSource for a field, where I wanted to
concatenate two fields AS a new, named field. It runs perfectly as a
query, but I'm sure the problem is that the syntax is not the same for
VBA. I am working on various combinations and havn't found the answer.
I can only agree with you that SQL will not 'copy' and 'paste'
 
Joe Mc Cormack said:
VM,
I cannot point you in the right direction, but I can agree with you
that an SQL query may not translate into VBA. I pasted an SQL
statement from Queries in RecordSource for a field, where I wanted to
concatenate two fields AS a new, named field. It runs perfectly as a
query, but I'm sure the problem is that the syntax is not the same for
VBA. I am working on various combinations and havn't found the answer.
I can only agree with you that SQL will not 'copy' and 'paste'
Click to expand...

You may want to post a followup message with more detail, showing what
you've tried. I'm a little perplexed because a field doesn't have a
RecordSource property, and you can't use a SQL statement in a
ControlSource property.
 
Dick,
I'm sorry, wrong info. I was looking at my database today and I
am running the SQL statement as the RecordSource for the subform. The
ControlSource for the field is the name of the concatenated tables.
 
Joe Mc Cormack said:
Dick,
I'm sorry, wrong info. I was looking at my database today and I
am running the SQL statement as the RecordSource for the subform. The
ControlSource for the field is the name of the concatenated tables.
Click to expand...

If you're having a problem, perhaps you'd like to post the RecordSource
and the ControlSource, along with a description of the problem. Then
maybe we could see what's wrong.
 
Dirk,
My original reply was to agree with VM, that SQL statements do not Copy
and Paste into VBA. However, my own specific problem was that I could
not get my code to recognise the two inverted commas with a space which
in SQL in Queries allows concatenated fields to have a space. The code
seemed to see those inverted commas as the end of the RecordSource
code. I eventually found that replacing the ' " " ' with 'Chr(32)'
gave the same result as the Query.
 
Back
Top