By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

Query works in grid but not in code

P: n/a
The query below works fine when run from the query grid, but generates Error
3051 when run from code. I have stripped out the Where clause, so that
wasn't the problem. I've tried it without the Sum() functions, but still the
same errors.
Could the problem be the complexity of the SQL?
INSERT INTO tArrearsGrouped ( PropNum, SortOrder, LAN, PropName, ShopNum,
TenantName, Arrears1, Arrears2, Arrears3, BF, Receipts, Bal, CurrentCharge,
Var, Archive, Manager, Contact1Tel, SelectRecord, SumofBP, SumofJ,
LesseeName, Comments ) SELECT DISTINCTROW tTenants.PropNum,
tTenants.SortOrder, tTenants.LAN, tProperty.PropName, tTenants.ShopNum,
tTenants.TenantName, qArrearsAmountsGrouped.Arrears1,
qArrearsAmountsGrouped.Arrears2, qArrearsAmountsGrouped.Arrears3,
qArrearsAmountsGrouped.BF, qArrearsAmountsGrouped.Receipts,
qArrearsAmountsGrouped.Bal, qArrearsAmountsGrouped.CurrentCharge,
qArrearsAmountsGrouped.Var, tTenants.Archive, tProperty.Manager,
tTenantDetails.Contact1Tel, tTenantDetails.SelectRecord, IIf([SumBP] Is
Null,0,[SumBP]) AS SumofBP, IIf([SumJ] Is Null,0,[SumJ]) AS SumofJ,
tTenants.LesseeName, qArrearsNotesCurrent.Comments FROM (tProperty INNER
JOIN ((tTenants INNER JOIN qArrearsAmountsGrouped ON tTenants.LAN =
qArrearsAmountsGrouped.LAN) LEFT JOIN qArrearsNotesCurrent ON
qArrearsAmountsGrouped.LAN = qArrearsNotesCurrent.LAN) ON tProperty.PropNum
= tTenants.PropNum) INNER JOIN tTenantDetails ON tTenants.LAN =
tTenantDetails.LAN ORDER BY tTenants.SortOrder;
--
Bob Darlington
Brisbane
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Sorry, forgot to mention that the SQL included in my post was cut from the
SQL view of the query grid.
I then pasted this into a 'Currentdb.Execute' statement (with line
formatting etc of course).

--
Bob Darlington
Brisbane
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:40***********************@news.optusnet.com.a u...
The query below works fine when run from the query grid, but generates Error 3051 when run from code. I have stripped out the Where clause, so that
wasn't the problem. I've tried it without the Sum() functions, but still the same errors.
Could the problem be the complexity of the SQL?
INSERT INTO tArrearsGrouped ( PropNum, SortOrder, LAN, PropName, ShopNum,
TenantName, Arrears1, Arrears2, Arrears3, BF, Receipts, Bal, CurrentCharge, Var, Archive, Manager, Contact1Tel, SelectRecord, SumofBP, SumofJ,
LesseeName, Comments ) SELECT DISTINCTROW tTenants.PropNum,
tTenants.SortOrder, tTenants.LAN, tProperty.PropName, tTenants.ShopNum,
tTenants.TenantName, qArrearsAmountsGrouped.Arrears1,
qArrearsAmountsGrouped.Arrears2, qArrearsAmountsGrouped.Arrears3,
qArrearsAmountsGrouped.BF, qArrearsAmountsGrouped.Receipts,
qArrearsAmountsGrouped.Bal, qArrearsAmountsGrouped.CurrentCharge,
qArrearsAmountsGrouped.Var, tTenants.Archive, tProperty.Manager,
tTenantDetails.Contact1Tel, tTenantDetails.SelectRecord, IIf([SumBP] Is
Null,0,[SumBP]) AS SumofBP, IIf([SumJ] Is Null,0,[SumJ]) AS SumofJ,
tTenants.LesseeName, qArrearsNotesCurrent.Comments FROM (tProperty INNER
JOIN ((tTenants INNER JOIN qArrearsAmountsGrouped ON tTenants.LAN =
qArrearsAmountsGrouped.LAN) LEFT JOIN qArrearsNotesCurrent ON
qArrearsAmountsGrouped.LAN = qArrearsNotesCurrent.LAN) ON tProperty.PropNum = tTenants.PropNum) INNER JOIN tTenantDetails ON tTenants.LAN =
tTenantDetails.LAN ORDER BY tTenants.SortOrder;
--
Bob Darlington
Brisbane

Nov 12 '05 #2

P: n/a
Please disregard this post. There was a parameter buried in a query inside a
query.
Sorry if anyone had wasted time on this.

--
Bob Darlington
Brisbane
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:40***********************@news.optusnet.com.a u...
The query below works fine when run from the query grid, but generates Error 3051 when run from code. I have stripped out the Where clause, so that
wasn't the problem. I've tried it without the Sum() functions, but still the same errors.
Could the problem be the complexity of the SQL?
INSERT INTO tArrearsGrouped ( PropNum, SortOrder, LAN, PropName, ShopNum,
TenantName, Arrears1, Arrears2, Arrears3, BF, Receipts, Bal, CurrentCharge, Var, Archive, Manager, Contact1Tel, SelectRecord, SumofBP, SumofJ,
LesseeName, Comments ) SELECT DISTINCTROW tTenants.PropNum,
tTenants.SortOrder, tTenants.LAN, tProperty.PropName, tTenants.ShopNum,
tTenants.TenantName, qArrearsAmountsGrouped.Arrears1,
qArrearsAmountsGrouped.Arrears2, qArrearsAmountsGrouped.Arrears3,
qArrearsAmountsGrouped.BF, qArrearsAmountsGrouped.Receipts,
qArrearsAmountsGrouped.Bal, qArrearsAmountsGrouped.CurrentCharge,
qArrearsAmountsGrouped.Var, tTenants.Archive, tProperty.Manager,
tTenantDetails.Contact1Tel, tTenantDetails.SelectRecord, IIf([SumBP] Is
Null,0,[SumBP]) AS SumofBP, IIf([SumJ] Is Null,0,[SumJ]) AS SumofJ,
tTenants.LesseeName, qArrearsNotesCurrent.Comments FROM (tProperty INNER
JOIN ((tTenants INNER JOIN qArrearsAmountsGrouped ON tTenants.LAN =
qArrearsAmountsGrouped.LAN) LEFT JOIN qArrearsNotesCurrent ON
qArrearsAmountsGrouped.LAN = qArrearsNotesCurrent.LAN) ON tProperty.PropNum = tTenants.PropNum) INNER JOIN tTenantDetails ON tTenants.LAN =
tTenantDetails.LAN ORDER BY tTenants.SortOrder;
--
Bob Darlington
Brisbane

Nov 12 '05 #3

P: n/a
Hey Bob,

There is no reason why a valid SQL string wont run in code regardless of
complexity (assuming we are talking about an Access database) so pulling out
the where and sum() will not achieve anything apart from possibly
identifying a syntax error. If you continue to have issues try this method
instead.

1. Save the query (if you havent already done so.)

2. Check that the query will execute. (double click to run).

3. Assuming the query is called 'qryBoBsQuery' and your current db object
is called 'CurrentDB' use this method to execute it:
CurrentDB.Execute CurrentDB.QueryDefs("qryBoBsQuery").SQL,dbFailOnEr ror

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:40***********************@news.optusnet.com.a u...
The query below works fine when run from the query grid, but generates Error 3051 when run from code. I have stripped out the Where clause, so that
wasn't the problem. I've tried it without the Sum() functions, but still the same errors.
Could the problem be the complexity of the SQL?
INSERT INTO tArrearsGrouped ( PropNum, SortOrder, LAN, PropName, ShopNum,
TenantName, Arrears1, Arrears2, Arrears3, BF, Receipts, Bal, CurrentCharge, Var, Archive, Manager, Contact1Tel, SelectRecord, SumofBP, SumofJ,
LesseeName, Comments ) SELECT DISTINCTROW tTenants.PropNum,
tTenants.SortOrder, tTenants.LAN, tProperty.PropName, tTenants.ShopNum,
tTenants.TenantName, qArrearsAmountsGrouped.Arrears1,
qArrearsAmountsGrouped.Arrears2, qArrearsAmountsGrouped.Arrears3,
qArrearsAmountsGrouped.BF, qArrearsAmountsGrouped.Receipts,
qArrearsAmountsGrouped.Bal, qArrearsAmountsGrouped.CurrentCharge,
qArrearsAmountsGrouped.Var, tTenants.Archive, tProperty.Manager,
tTenantDetails.Contact1Tel, tTenantDetails.SelectRecord, IIf([SumBP] Is
Null,0,[SumBP]) AS SumofBP, IIf([SumJ] Is Null,0,[SumJ]) AS SumofJ,
tTenants.LesseeName, qArrearsNotesCurrent.Comments FROM (tProperty INNER
JOIN ((tTenants INNER JOIN qArrearsAmountsGrouped ON tTenants.LAN =
qArrearsAmountsGrouped.LAN) LEFT JOIN qArrearsNotesCurrent ON
qArrearsAmountsGrouped.LAN = qArrearsNotesCurrent.LAN) ON tProperty.PropNum = tTenants.PropNum) INNER JOIN tTenantDetails ON tTenants.LAN =
tTenantDetails.LAN ORDER BY tTenants.SortOrder;
--
Bob Darlington
Brisbane

Nov 12 '05 #4

P: n/a
I've had this happen a couple of times - can't remember why - anyways
the workaround is to save the query and execute it, not the sql
Terry

"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message news:<40*********************@news.optusnet.com.au >...
Sorry, forgot to mention that the SQL included in my post was cut from the
SQL view of the query grid.
I then pasted this into a 'Currentdb.Execute' statement (with line
formatting etc of course).

--
Bob Darlington
Brisbane
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:40***********************@news.optusnet.com.a u...
The query below works fine when run from the query grid, but generates

Error
3051 when run from code. I have stripped out the Where clause, so that
wasn't the problem. I've tried it without the Sum() functions, but still

the
same errors.
Could the problem be the complexity of the SQL?
INSERT INTO tArrearsGrouped ( PropNum, SortOrder, LAN, PropName, ShopNum,
TenantName, Arrears1, Arrears2, Arrears3, BF, Receipts, Bal,

CurrentCharge,
Var, Archive, Manager, Contact1Tel, SelectRecord, SumofBP, SumofJ,
LesseeName, Comments ) SELECT DISTINCTROW tTenants.PropNum,
tTenants.SortOrder, tTenants.LAN, tProperty.PropName, tTenants.ShopNum,
tTenants.TenantName, qArrearsAmountsGrouped.Arrears1,
qArrearsAmountsGrouped.Arrears2, qArrearsAmountsGrouped.Arrears3,
qArrearsAmountsGrouped.BF, qArrearsAmountsGrouped.Receipts,
qArrearsAmountsGrouped.Bal, qArrearsAmountsGrouped.CurrentCharge,
qArrearsAmountsGrouped.Var, tTenants.Archive, tProperty.Manager,
tTenantDetails.Contact1Tel, tTenantDetails.SelectRecord, IIf([SumBP] Is
Null,0,[SumBP]) AS SumofBP, IIf([SumJ] Is Null,0,[SumJ]) AS SumofJ,
tTenants.LesseeName, qArrearsNotesCurrent.Comments FROM (tProperty INNER
JOIN ((tTenants INNER JOIN qArrearsAmountsGrouped ON tTenants.LAN =
qArrearsAmountsGrouped.LAN) LEFT JOIN qArrearsNotesCurrent ON
qArrearsAmountsGrouped.LAN = qArrearsNotesCurrent.LAN) ON

tProperty.PropNum
= tTenants.PropNum) INNER JOIN tTenantDetails ON tTenants.LAN =
tTenantDetails.LAN ORDER BY tTenants.SortOrder;
--
Bob Darlington
Brisbane

Nov 12 '05 #5

P: n/a
Thanks Terry and Andrew.
The problem was a parameter I had buried in a query within the query (see my
earlier post).
Sorry to have wasted your time.

--
Bob Darlington
Brisbane
"Bob Darlington" <bo*@dpcmanAX.com.au> wrote in message
news:40***********************@news.optusnet.com.a u...
The query below works fine when run from the query grid, but generates Error 3051 when run from code. I have stripped out the Where clause, so that
wasn't the problem. I've tried it without the Sum() functions, but still the same errors.
Could the problem be the complexity of the SQL?
INSERT INTO tArrearsGrouped ( PropNum, SortOrder, LAN, PropName, ShopNum,
TenantName, Arrears1, Arrears2, Arrears3, BF, Receipts, Bal, CurrentCharge, Var, Archive, Manager, Contact1Tel, SelectRecord, SumofBP, SumofJ,
LesseeName, Comments ) SELECT DISTINCTROW tTenants.PropNum,
tTenants.SortOrder, tTenants.LAN, tProperty.PropName, tTenants.ShopNum,
tTenants.TenantName, qArrearsAmountsGrouped.Arrears1,
qArrearsAmountsGrouped.Arrears2, qArrearsAmountsGrouped.Arrears3,
qArrearsAmountsGrouped.BF, qArrearsAmountsGrouped.Receipts,
qArrearsAmountsGrouped.Bal, qArrearsAmountsGrouped.CurrentCharge,
qArrearsAmountsGrouped.Var, tTenants.Archive, tProperty.Manager,
tTenantDetails.Contact1Tel, tTenantDetails.SelectRecord, IIf([SumBP] Is
Null,0,[SumBP]) AS SumofBP, IIf([SumJ] Is Null,0,[SumJ]) AS SumofJ,
tTenants.LesseeName, qArrearsNotesCurrent.Comments FROM (tProperty INNER
JOIN ((tTenants INNER JOIN qArrearsAmountsGrouped ON tTenants.LAN =
qArrearsAmountsGrouped.LAN) LEFT JOIN qArrearsNotesCurrent ON
qArrearsAmountsGrouped.LAN = qArrearsNotesCurrent.LAN) ON tProperty.PropNum = tTenants.PropNum) INNER JOIN tTenantDetails ON tTenants.LAN =
tTenantDetails.LAN ORDER BY tTenants.SortOrder;
--
Bob Darlington
Brisbane

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.