Select statement in code

[ChoonBoy]

What I am trying to do does not work.

The objective is to reduce the number of queries in database windows.

- Instead of creating a query for another query to use its data
- I hope to use the sql statement within the code to be used by the
DoCmd.RunSQL part.

Something like this (what a mess). Please point me to the right direction.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set rst = dbs.OpenRecordset( _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, [Expense
Category].ExpenseGrpID FROM [Expense Category]")

DoCmd.RunSQL "INSERT INTO [Expense Report] ( ExpenseRptName, ExpenseID,
DateSubmitted ) SELECT rst.ExpRptName, rst.ExpenseGrpID, DateValue(Now()) AS
[Date] FROM [Expense Report] RIGHT JOIN rst ON ([Expense
Report].ExpenseRptName = rst.ExpRptName) AND ([Expense Report].ExpenseID =
rst.ExpenseGrpID)WHERE ((([Expense Report].ExpenseReportID) Is Null)); ", -1

Thanks

 

[Tom van Stiphout]

On Fri, 28 Aug 2009 21:46:01 -0700, ChoonBoy

Not the best of objectives. In recent versions of Access you can
create groups to organize queries. A strong naming convention helps as
well.

-Tom.
Microsoft Access MVP

 

[ChoonBoy]

Thanks for the reply, but is it possible and what will the coding looks like.

 

[Douglas J. Steele]

I agree with Tom that it's a bad idea, but yes, it can be done. The reason
what you has isn't working is because the Jet engine doesn't know anything
about VBA objects such as the recordset rst. However, I don't see any reason
for that recordset in the first place.

See whether the following works:

strSQL = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " & _
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"

CurrentDb.Execute strSQL, dbFailOnError

Incidentally, you should rename your field from Date. Date is a reserved
word, and you should never use reserved words for your own purposes. For a
comprehensive list of names to avoid (as well as a link to a free utility to
check your application for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[ChoonBoy]

Thanks for the reply and pointers.

I replaced my codes, but each time I run it the following error massage appear

Run-time error 3131. Syntax error in From Clause and CurrentDb.Execute
strsql, dbFailOnError is highlighted yellow.

My code now looks like this

Dim strsql As String

strsql = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " & _
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"

CurrentDb.Execute strsql, dbFailOnError

Thanks

I agree with Tom that it's a bad idea, but yes, it can be done. The reason
what you has isn't working is because the Jet engine doesn't know anything
about VBA objects such as the recordset rst. However, I don't see any reason
for that recordset in the first place.

See whether the following works:

strSQL = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " & _
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"

CurrentDb.Execute strSQL, dbFailOnError

Incidentally, you should rename your field from Date. Date is a reserved
word, and you should never use reserved words for your own purposes. For a
comprehensive list of names to avoid (as well as a link to a free utility to
check your application for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What I am trying to do does not work.

The objective is to reduce the number of queries in database windows.

- Instead of creating a query for another query to use its data
- I hope to use the sql statement within the code to be used by the
DoCmd.RunSQL part.

Something like this (what a mess). Please point me to the right direction.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set rst = dbs.OpenRecordset( _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, [Expense
Category].ExpenseGrpID FROM [Expense Category]")

DoCmd.RunSQL "INSERT INTO [Expense Report] ( ExpenseRptName, ExpenseID,
DateSubmitted ) SELECT rst.ExpRptName, rst.ExpenseGrpID, DateValue(Now())
AS
[Date] FROM [Expense Report] RIGHT JOIN rst ON ([Expense
Report].ExpenseRptName = rst.ExpRptName) AND ([Expense Report].ExpenseID =
rst.ExpenseGrpID)WHERE ((([Expense Report].ExpenseReportID) Is Null));
", -1

Thanks

 

[Douglas J. Steele]

Sorry: there was a typo (I was missing an opening parenthesis)

strSQL = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"(SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " & _
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the reply and pointers.

I replaced my codes, but each time I run it the following error massage
appear

Run-time error 3131. Syntax error in From Clause and CurrentDb.Execute
strsql, dbFailOnError is highlighted yellow.

My code now looks like this

Dim strsql As String

strsql = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " & _
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"

CurrentDb.Execute strsql, dbFailOnError

Thanks

I agree with Tom that it's a bad idea, but yes, it can be done. The
reason
what you has isn't working is because the Jet engine doesn't know
anything
about VBA objects such as the recordset rst. However, I don't see any
reason
for that recordset in the first place.

See whether the following works:

strSQL = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " &
_
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"

CurrentDb.Execute strSQL, dbFailOnError

Incidentally, you should rename your field from Date. Date is a reserved
word, and you should never use reserved words for your own purposes. For
a
comprehensive list of names to avoid (as well as a link to a free utility
to
check your application for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

What I am trying to do does not work.

The objective is to reduce the number of queries in database windows.

- Instead of creating a query for another query to use its data
- I hope to use the sql statement within the code to be used by the
DoCmd.RunSQL part.

Something like this (what a mess). Please point me to the right
direction.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set rst = dbs.OpenRecordset( _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, [Expense
Category].ExpenseGrpID FROM [Expense Category]")

DoCmd.RunSQL "INSERT INTO [Expense Report] ( ExpenseRptName, ExpenseID,
DateSubmitted ) SELECT rst.ExpRptName, rst.ExpenseGrpID,
DateValue(Now())
AS
[Date] FROM [Expense Report] RIGHT JOIN rst ON ([Expense
Report].ExpenseRptName = rst.ExpRptName) AND ([Expense
Report].ExpenseID =
rst.ExpenseGrpID)WHERE ((([Expense Report].ExpenseReportID) Is Null));
", -1

Thanks

 

[ChoonBoy]

It works very well, thank you.

Sorry: there was a typo (I was missing an opening parenthesis)

strSQL = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"(SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " & _
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the reply and pointers.

I replaced my codes, but each time I run it the following error massage
appear

Run-time error 3131. Syntax error in From Clause and CurrentDb.Execute
strsql, dbFailOnError is highlighted yellow.

My code now looks like this

Dim strsql As String

strsql = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " & _
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"

CurrentDb.Execute strsql, dbFailOnError

Thanks

I agree with Tom that it's a bad idea, but yes, it can be done. The
reason
what you has isn't working is because the Jet engine doesn't know
anything
about VBA objects such as the recordset rst. However, I don't see any
reason
for that recordset in the first place.

See whether the following works:

strSQL = "INSERT INTO [Expense Report] " & _
"( ExpenseRptName, ExpenseID, DateSubmitted ) " & _
"SELECT rst.ExpRptName, rst.ExpenseGrpID, " & _
"Date() AS [Date] " & _
"FROM [Expense Report] RIGHT JOIN " & _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, " & _
"[Expense Category].ExpenseGrpID FROM [Expense Category]) AS rst " &
_
"ON ([Expense Report].ExpenseRptName = rst.ExpRptName) " & _
"AND ([Expense Report].ExpenseID = rst.ExpenseGrpID) " & _
"WHERE [Expense Report].ExpenseReportID Is Null"

CurrentDb.Execute strSQL, dbFailOnError

Incidentally, you should rename your field from Date. Date is a reserved
word, and you should never use reserved words for your own purposes. For
a
comprehensive list of names to avoid (as well as a link to a free utility
to
check your application for compliance), check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


What I am trying to do does not work.

The objective is to reduce the number of queries in database windows.

- Instead of creating a query for another query to use its data
- I hope to use the sql statement within the code to be used by the
DoCmd.RunSQL part.

Something like this (what a mess). Please point me to the right
direction.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set rst = dbs.OpenRecordset( _
"SELECT [expensegrpid] & [ExpenseCategoryID] AS ExpRptName, [Expense
Category].ExpenseGrpID FROM [Expense Category]")

DoCmd.RunSQL "INSERT INTO [Expense Report] ( ExpenseRptName, ExpenseID,
DateSubmitted ) SELECT rst.ExpRptName, rst.ExpenseGrpID,
DateValue(Now())
AS
[Date] FROM [Expense Report] RIGHT JOIN rst ON ([Expense
Report].ExpenseRptName = rst.ExpRptName) AND ([Expense
Report].ExpenseID =
rst.ExpenseGrpID)WHERE ((([Expense Report].ExpenseReportID) Is Null));
", -1

Thanks