query vs docmd.runsql

G

Guest

The following query which uses two linked summary queries, one of which has a
sub query, runs properly as a query, but will not run when assigned to a
string variable and run using docmd.runsql. The reason for wanting to use
the latter option is to make it easier to replace the literal critera with
variables assigned in an input form.

SELECT Act.Market, Act.Dept, Act.Account, Act.InvcAmt, Ref.RefAmt
FROM [SELECT stations.Market, APhist_PS.Dept, APhist_PS.Account,
CoreChart.Description, Sum(APhist_PS.[Invoice Amount]) AS InvcAmt
FROM ((APhist_PS INNER JOIN stations ON APhist_PS.[GL Unit] =
stations.StaNo) INNER JOIN datetab ON APhist_PS.[Invoice Date] =
datetab.date) INNER JOIN CoreChart ON APhist_PS.Account = CoreChart.Core
WHERE (((APhist_PS.[Invoice Date])>=#07/01/2006# And (APhist_PS.[Invoice
Date])<=#07/31/2006#))
GROUP BY stations.Market, APhist_PS.Dept, APhist_PS.Account,
CoreChart.Description
HAVING (((stations.Market)=382) AND ((APhist_PS.Dept)>"000"))]. AS Act LEFT
JOIN [SELECT FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno,
Sum(FlashImpt.Amt) AS RefAmt
FROM FlashImpt
GROUP BY FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno
HAVING (((FlashImpt.Month)=(SELECT datetab.Mnth
FROM datetab
WHERE (((datetab.date)>=#7/1/2006# And (datetab.date)<=#7/31/2006#))
GROUP BY datetab.Mnth)) AND ((FlashImpt.Acctno)>"499999"))]. AS Ref ON
(Act.Dept = Ref.Dept) AND (Act.Account = Ref.Acctno);

The above is the saved SQL view. I tried replacing the brackets around the
summary queries in the string assignment, but no dice. The query will run
either way: using parentheses for separators or the brackets inserted by
Access when the the query is saved.

DM
 
G

Guest

The RunSql will excute action queries (Insert , Delete , Update) it wont run
a select query.
There is no command that will run a select SQL, to open a select query you
can run
Docmd.OpenQuery "QueryName"

So what you can do is, create a query name it "MySelectQuery" and then
assign the SQL to the Query, and then run the query using the line above

Application.CurrentDb.QueryDefs("MySelectQuery").SQL = "SELECT Act.Market,
Act.Dept, Act.Account, Act.InvcAmt, Ref.RefAmt FROM [SELECT stations.Market,
APhist_PS.Dept, APhist_PS.Account, CoreChart.Description,
Sum(APhist_PS.[Invoice Amount]) AS InvcAmt FROM ((APhist_PS INNER JOIN
stations ON APhist_PS.[GL Unit] = stations.StaNo) INNER JOIN datetab ON
APhist_PS.[Invoice Date] =
datetab.date) INNER JOIN CoreChart ON APhist_PS.Account = CoreChart.Core
WHERE (((APhist_PS.[Invoice Date])>=#07/01/2006# And (APhist_PS.[Invoice
Date])<=#07/31/2006#)) GROUP BY stations.Market, APhist_PS.Dept,
APhist_PS.Account, CoreChart.Description HAVING (((stations.Market)=382) AND
((APhist_PS.Dept)>"000"))]. AS Act LEFT JOIN [SELECT FlashImpt.Month,
FlashImpt.Dept, FlashImpt.Acctno, Sum(FlashImpt.Amt) AS RefAmt FROM FlashImpt
GROUP BY FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno HAVING
(((FlashImpt.Month)=(SELECT datetab.Mnth FROM datetab WHERE
(((datetab.date)>=#7/1/2006# And (datetab.date)<=#7/31/2006#)) GROUP BY
datetab.Mnth)) AND ((FlashImpt.Acctno)>"499999"))]. AS Ref ON (Act.Dept =
Ref.Dept) AND (Act.Account = Ref.Acctno);

Docmd.OpenQuery "MySelectQuery"
--
Good Luck
BS"D


Dick Minter said:
The following query which uses two linked summary queries, one of which has a
sub query, runs properly as a query, but will not run when assigned to a
string variable and run using docmd.runsql. The reason for wanting to use
the latter option is to make it easier to replace the literal critera with
variables assigned in an input form.

SELECT Act.Market, Act.Dept, Act.Account, Act.InvcAmt, Ref.RefAmt
FROM [SELECT stations.Market, APhist_PS.Dept, APhist_PS.Account,
CoreChart.Description, Sum(APhist_PS.[Invoice Amount]) AS InvcAmt
FROM ((APhist_PS INNER JOIN stations ON APhist_PS.[GL Unit] =
stations.StaNo) INNER JOIN datetab ON APhist_PS.[Invoice Date] =
datetab.date) INNER JOIN CoreChart ON APhist_PS.Account = CoreChart.Core
WHERE (((APhist_PS.[Invoice Date])>=#07/01/2006# And (APhist_PS.[Invoice
Date])<=#07/31/2006#))
GROUP BY stations.Market, APhist_PS.Dept, APhist_PS.Account,
CoreChart.Description
HAVING (((stations.Market)=382) AND ((APhist_PS.Dept)>"000"))]. AS Act LEFT
JOIN [SELECT FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno,
Sum(FlashImpt.Amt) AS RefAmt
FROM FlashImpt
GROUP BY FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno
HAVING (((FlashImpt.Month)=(SELECT datetab.Mnth
FROM datetab
WHERE (((datetab.date)>=#7/1/2006# And (datetab.date)<=#7/31/2006#))
GROUP BY datetab.Mnth)) AND ((FlashImpt.Acctno)>"499999"))]. AS Ref ON
(Act.Dept = Ref.Dept) AND (Act.Account = Ref.Acctno);

The above is the saved SQL view. I tried replacing the brackets around the
summary queries in the string assignment, but no dice. The query will run
either way: using parentheses for separators or the brackets inserted by
Access when the the query is saved.

DM
Click to expand...
 
G

Guest

Try this SQL, change the double quote to single, and close the string with
double quote

Application.CurrentDb.QueryDefs("MySelectQuery").SQL = "SELECT Act.Market,
Act.Dept, Act.Account, Act.InvcAmt, Ref.RefAmt FROM [SELECT stations.Market,
APhist_PS.Dept, APhist_PS.Account, CoreChart.Description,
Sum(APhist_PS.[Invoice Amount]) AS InvcAmt FROM ((APhist_PS INNER JOIN
stations ON APhist_PS.[GL Unit] = stations.StaNo) INNER JOIN datetab ON
APhist_PS.[Invoice Date] =
datetab.date) INNER JOIN CoreChart ON APhist_PS.Account = CoreChart.Core
WHERE (((APhist_PS.[Invoice Date])>=#07/01/2006# And (APhist_PS.[Invoice
Date])<=#07/31/2006#)) GROUP BY stations.Market, APhist_PS.Dept,
APhist_PS.Account, CoreChart.Description HAVING (((stations.Market)=382) AND
((APhist_PS.Dept)>'000'))]. AS Act LEFT JOIN [SELECT FlashImpt.Month,
FlashImpt.Dept, FlashImpt.Acctno, Sum(FlashImpt.Amt) AS RefAmt FROM FlashImpt
GROUP BY FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno HAVING
(((FlashImpt.Month)=(SELECT datetab.Mnth FROM datetab WHERE
(((datetab.date)>=#7/1/2006# And (datetab.date)<=#7/31/2006#)) GROUP BY
datetab.Mnth)) AND ((FlashImpt.Acctno)>'499999'))]. AS Ref ON (Act.Dept =
Ref.Dept) AND (Act.Account = Ref.Acctno)"

Docmd.OpenQuery "MySelectQuery"

--
Good Luck
BS"D


Ofer Cohen said:
The RunSql will excute action queries (Insert , Delete , Update) it wont run
a select query.
There is no command that will run a select SQL, to open a select query you
can run
Docmd.OpenQuery "QueryName"

So what you can do is, create a query name it "MySelectQuery" and then
assign the SQL to the Query, and then run the query using the line above

Application.CurrentDb.QueryDefs("MySelectQuery").SQL = "SELECT Act.Market,
Act.Dept, Act.Account, Act.InvcAmt, Ref.RefAmt FROM [SELECT stations.Market,
APhist_PS.Dept, APhist_PS.Account, CoreChart.Description,
Sum(APhist_PS.[Invoice Amount]) AS InvcAmt FROM ((APhist_PS INNER JOIN
stations ON APhist_PS.[GL Unit] = stations.StaNo) INNER JOIN datetab ON
APhist_PS.[Invoice Date] =
datetab.date) INNER JOIN CoreChart ON APhist_PS.Account = CoreChart.Core
WHERE (((APhist_PS.[Invoice Date])>=#07/01/2006# And (APhist_PS.[Invoice
Date])<=#07/31/2006#)) GROUP BY stations.Market, APhist_PS.Dept,
APhist_PS.Account, CoreChart.Description HAVING (((stations.Market)=382) AND
((APhist_PS.Dept)>"000"))]. AS Act LEFT JOIN [SELECT FlashImpt.Month,
FlashImpt.Dept, FlashImpt.Acctno, Sum(FlashImpt.Amt) AS RefAmt FROM FlashImpt
GROUP BY FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno HAVING
(((FlashImpt.Month)=(SELECT datetab.Mnth FROM datetab WHERE
(((datetab.date)>=#7/1/2006# And (datetab.date)<=#7/31/2006#)) GROUP BY
datetab.Mnth)) AND ((FlashImpt.Acctno)>"499999"))]. AS Ref ON (Act.Dept =
Ref.Dept) AND (Act.Account = Ref.Acctno);

Docmd.OpenQuery "MySelectQuery"
--
Good Luck
BS"D


Dick Minter said:
The following query which uses two linked summary queries, one of which has a
sub query, runs properly as a query, but will not run when assigned to a
string variable and run using docmd.runsql. The reason for wanting to use
the latter option is to make it easier to replace the literal critera with
variables assigned in an input form.

SELECT Act.Market, Act.Dept, Act.Account, Act.InvcAmt, Ref.RefAmt
FROM [SELECT stations.Market, APhist_PS.Dept, APhist_PS.Account,
CoreChart.Description, Sum(APhist_PS.[Invoice Amount]) AS InvcAmt
FROM ((APhist_PS INNER JOIN stations ON APhist_PS.[GL Unit] =
stations.StaNo) INNER JOIN datetab ON APhist_PS.[Invoice Date] =
datetab.date) INNER JOIN CoreChart ON APhist_PS.Account = CoreChart.Core
WHERE (((APhist_PS.[Invoice Date])>=#07/01/2006# And (APhist_PS.[Invoice
Date])<=#07/31/2006#))
GROUP BY stations.Market, APhist_PS.Dept, APhist_PS.Account,
CoreChart.Description
HAVING (((stations.Market)=382) AND ((APhist_PS.Dept)>"000"))]. AS Act LEFT
JOIN [SELECT FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno,
Sum(FlashImpt.Amt) AS RefAmt
FROM FlashImpt
GROUP BY FlashImpt.Month, FlashImpt.Dept, FlashImpt.Acctno
HAVING (((FlashImpt.Month)=(SELECT datetab.Mnth
FROM datetab
WHERE (((datetab.date)>=#7/1/2006# And (datetab.date)<=#7/31/2006#))
GROUP BY datetab.Mnth)) AND ((FlashImpt.Acctno)>"499999"))]. AS Ref ON
(Act.Dept = Ref.Dept) AND (Act.Account = Ref.Acctno);

The above is the saved SQL view. I tried replacing the brackets around the
summary queries in the string assignment, but no dice. The query will run
either way: using parentheses for separators or the brackets inserted by
Access when the the query is saved.

DM
Click to expand...
Click to expand...
 
Top