Query works in grid but not in code

[Bob Darlington]

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

[Bob Darlington]

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

[Bob Darlington]

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

[Andrew Chanter]

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

[Terry Bell]

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

[Bob Darlington]

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