Reference SQL in Access VB

  • Thread starter Thread starter NKA
  • Start date Start date
N

NKA

I currently have an event procedure where it opens a snapshot of some data.
This recordset references a query.

I would like to remove the query and replace with SQL in the module but have
no idea how to do that!

I have added:

Dim strSQL as String

strSQL = "SQL Statement here.....".

DoCmd.RunSQL strSQL

I keep getting Runtime Error 3141 - The SELECT statement includes a reserved
word or an argument name that is misspelled or missing, or the punctuation is
incorrect.

This is the SQL from the query:

SELECT joinEmployeeTask.TaskID, "[" & [ServiceReqID] & "]" & " " &
[ServiceReqName] AS ServiceReq, tblEmployee.LastName, [FirstName] & " " &
[Lastname] AS Name, tblTask.Status, tblTask.Priority, tblTask.StartDate AS
TaskStart, Date() AS StartDate, tblTask.EndDate AS TaskEnd,
IIf([taskend]>Date()+91,Date()+97,[taskend]) AS EndTask,
DateDiff("d",Date(),[TaskEnd]) AS NoDays,
IIf(IsNull([AvgOfComplete]/100),0,[avgofcomplete]/100) AS Complete
FROM (tblTask LEFT JOIN qryTaskComplete ON tblTask.TaskID =
qryTaskComplete.TaskID) INNER JOIN (tblEmployee INNER JOIN joinEmployeeTask
ON tblEmployee.EmpID = joinEmployeeTask.EmpID) ON tblTask.TaskID =
joinEmployeeTask.TaskID
WHERE (((tblTask.StartDate) Is Not Null And (tblTask.StartDate)<Date()+84)
AND ((tblTask.EndDate) Is Not Null) AND ((tblTask.Current)=-1))
ORDER BY tblEmployee.LastName;

I think the problem lies with the LEFT JOIN....

Any help would be appreciated.
 
No, the problem lies with the fact that you're trying to run a SELECT
statement.

You can only use RunSQL with Action queries (INSERT INTO, DELETE, UPDATE,
SELECT .... INTO)
 
I currently have an event procedure where it opens a snapshot of some data.
This recordset references a query.

I would like to remove the query and replace with SQL in the module but have
no idea how to do that!

I have added:

Dim strSQL as String

strSQL = "SQL Statement here.....".

DoCmd.RunSQL strSQL

I keep getting Runtime Error 3141 - The SELECT statement includes a reserved
word or an argument name that is misspelled or missing, or the punctuation is
incorrect.

This is the SQL from the query:

SELECT joinEmployeeTask.TaskID, "[" & [ServiceReqID] & "]" & " " &
[ServiceReqName] AS ServiceReq, tblEmployee.LastName, [FirstName] & " " &
[Lastname] AS Name, tblTask.Status, tblTask.Priority, tblTask.StartDate AS
TaskStart, Date() AS StartDate, tblTask.EndDate AS TaskEnd,
IIf([taskend]>Date()+91,Date()+97,[taskend]) AS EndTask,
DateDiff("d",Date(),[TaskEnd]) AS NoDays,
IIf(IsNull([AvgOfComplete]/100),0,[avgofcomplete]/100) AS Complete
FROM (tblTask LEFT JOIN qryTaskComplete ON tblTask.TaskID =
qryTaskComplete.TaskID) INNER JOIN (tblEmployee INNER JOIN joinEmployeeTask
ON tblEmployee.EmpID = joinEmployeeTask.EmpID) ON tblTask.TaskID =
joinEmployeeTask.TaskID
WHERE (((tblTask.StartDate) Is Not Null And (tblTask.StartDate)<Date()+84)
AND ((tblTask.EndDate) Is Not Null) AND ((tblTask.Current)=-1))
ORDER BY tblEmployee.LastName;

I think the problem lies with the LEFT JOIN....

Any help would be appreciated.
Click to expand...

Does the query return the correct records?
If so, be aware that you CANNOT run a Select statement using RunSQL.
See VBA help. Using runSQL you can only run an Action query, i.e.
Update, Append, Delete, etc.

I would suggest you leave your query as it is.
If you wish to run it, using VBA from code, you can use:
DoCmd.OpenQuery "QueryName"
 
Brilliant! That's the first hurdle... So do I just remove the docmd.runsql
line?

The next hurdle (which I forgot to post earlier) is that the next piece of
code references the original query (qryAssignment). How do I change that to
look at strSQL?

Set rs = db.OpenRecordset("SELECT Min([STARTDATE]) AS MinOfStartDate " _
& " FROM qryAssignment", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatEarliest = rs!MinOfStartDate
End If



Douglas J. Steele said:
No, the problem lies with the fact that you're trying to run a SELECT
statement.

You can only use RunSQL with Action queries (INSERT INTO, DELETE, UPDATE,
SELECT .... INTO)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


NKA said:
I currently have an event procedure where it opens a snapshot of some data.
This recordset references a query.

I would like to remove the query and replace with SQL in the module but
have
no idea how to do that!

I have added:

Dim strSQL as String

strSQL = "SQL Statement here.....".

DoCmd.RunSQL strSQL

I keep getting Runtime Error 3141 - The SELECT statement includes a
reserved
word or an argument name that is misspelled or missing, or the punctuation
is
incorrect.

This is the SQL from the query:

SELECT joinEmployeeTask.TaskID, "[" & [ServiceReqID] & "]" & " " &
[ServiceReqName] AS ServiceReq, tblEmployee.LastName, [FirstName] & " " &
[Lastname] AS Name, tblTask.Status, tblTask.Priority, tblTask.StartDate AS
TaskStart, Date() AS StartDate, tblTask.EndDate AS TaskEnd,
IIf([taskend]>Date()+91,Date()+97,[taskend]) AS EndTask,
DateDiff("d",Date(),[TaskEnd]) AS NoDays,
IIf(IsNull([AvgOfComplete]/100),0,[avgofcomplete]/100) AS Complete
FROM (tblTask LEFT JOIN qryTaskComplete ON tblTask.TaskID =
qryTaskComplete.TaskID) INNER JOIN (tblEmployee INNER JOIN
joinEmployeeTask
ON tblEmployee.EmpID = joinEmployeeTask.EmpID) ON tblTask.TaskID =
joinEmployeeTask.TaskID
WHERE (((tblTask.StartDate) Is Not Null And (tblTask.StartDate)<Date()+84)
AND ((tblTask.EndDate) Is Not Null) AND ((tblTask.Current)=-1))
ORDER BY tblEmployee.LastName;

I think the problem lies with the LEFT JOIN....

Any help would be appreciated.
Click to expand...
Click to expand...
 
Thanks Fred for you reply.

I have done away with the docmd.runsql and changed the 'qryAssignment' to
strSQL - I get a different error now "Syntax error in FROM clause".

Yes, the query does return the right records - I was hoping to do away with
the query and have everything sat in the code - but looks as though I am over
complicating my life again!

fredg said:
I currently have an event procedure where it opens a snapshot of some data.
This recordset references a query.

I would like to remove the query and replace with SQL in the module but have
no idea how to do that!

I have added:

Dim strSQL as String

strSQL = "SQL Statement here.....".

DoCmd.RunSQL strSQL

I keep getting Runtime Error 3141 - The SELECT statement includes a reserved
word or an argument name that is misspelled or missing, or the punctuation is
incorrect.

This is the SQL from the query:

SELECT joinEmployeeTask.TaskID, "[" & [ServiceReqID] & "]" & " " &
[ServiceReqName] AS ServiceReq, tblEmployee.LastName, [FirstName] & " " &
[Lastname] AS Name, tblTask.Status, tblTask.Priority, tblTask.StartDate AS
TaskStart, Date() AS StartDate, tblTask.EndDate AS TaskEnd,
IIf([taskend]>Date()+91,Date()+97,[taskend]) AS EndTask,
DateDiff("d",Date(),[TaskEnd]) AS NoDays,
IIf(IsNull([AvgOfComplete]/100),0,[avgofcomplete]/100) AS Complete
FROM (tblTask LEFT JOIN qryTaskComplete ON tblTask.TaskID =
qryTaskComplete.TaskID) INNER JOIN (tblEmployee INNER JOIN joinEmployeeTask
ON tblEmployee.EmpID = joinEmployeeTask.EmpID) ON tblTask.TaskID =
joinEmployeeTask.TaskID
WHERE (((tblTask.StartDate) Is Not Null And (tblTask.StartDate)<Date()+84)
AND ((tblTask.EndDate) Is Not Null) AND ((tblTask.Current)=-1))
ORDER BY tblEmployee.LastName;

I think the problem lies with the LEFT JOIN....

Any help would be appreciated.
Click to expand...

Does the query return the correct records?
If so, be aware that you CANNOT run a Select statement using RunSQL.
See VBA help. Using runSQL you can only run an Action query, i.e.
Update, Append, Delete, etc.

I would suggest you leave your query as it is.
If you wish to run it, using VBA from code, you can use:
DoCmd.OpenQuery "QueryName"
Click to expand...
 
Hold on. That makes no sense. In your SQL, you've got

", Date() AS StartDate, "

That means every single row in your query will have the same value for
StartDate: today's date!

Generically, though, you could do something like:

Set rs = db.OpenRecordset("SELECT Min(S.[STARTDATE]) AS MinOfStartDate " _
& " FROM (" & strSQL & ") As S", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatEarliest = rs!MinOfStartDate
End If

Is there a reason for wanting to eliminate the query? You don't really need
to use a recordset. You could simply use:

mdatEarliest = DMin("StartDate", "qryAssignment")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


NKA said:
Brilliant! That's the first hurdle... So do I just remove the
docmd.runsql
line?

The next hurdle (which I forgot to post earlier) is that the next piece of
code references the original query (qryAssignment). How do I change that
to
look at strSQL?

Set rs = db.OpenRecordset("SELECT Min([STARTDATE]) AS MinOfStartDate " _
& " FROM qryAssignment", dbOpenSnapshot)
If rs.RecordCount > 0 Then
mdatEarliest = rs!MinOfStartDate
End If



Douglas J. Steele said:
No, the problem lies with the fact that you're trying to run a SELECT
statement.

You can only use RunSQL with Action queries (INSERT INTO, DELETE, UPDATE,
SELECT .... INTO)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


NKA said:
I currently have an event procedure where it opens a snapshot of some
data.
This recordset references a query.

I would like to remove the query and replace with SQL in the module but
have
no idea how to do that!

I have added:

Dim strSQL as String

strSQL = "SQL Statement here.....".

DoCmd.RunSQL strSQL

I keep getting Runtime Error 3141 - The SELECT statement includes a
reserved
word or an argument name that is misspelled or missing, or the
punctuation
is
incorrect.

This is the SQL from the query:

SELECT joinEmployeeTask.TaskID, "[" & [ServiceReqID] & "]" & " " &
[ServiceReqName] AS ServiceReq, tblEmployee.LastName, [FirstName] & " "
&
[Lastname] AS Name, tblTask.Status, tblTask.Priority, tblTask.StartDate
AS
TaskStart, Date() AS StartDate, tblTask.EndDate AS TaskEnd,
IIf([taskend]>Date()+91,Date()+97,[taskend]) AS EndTask,
DateDiff("d",Date(),[TaskEnd]) AS NoDays,
IIf(IsNull([AvgOfComplete]/100),0,[avgofcomplete]/100) AS Complete
FROM (tblTask LEFT JOIN qryTaskComplete ON tblTask.TaskID =
qryTaskComplete.TaskID) INNER JOIN (tblEmployee INNER JOIN
joinEmployeeTask
ON tblEmployee.EmpID = joinEmployeeTask.EmpID) ON tblTask.TaskID =
joinEmployeeTask.TaskID
WHERE (((tblTask.StartDate) Is Not Null And
(tblTask.StartDate)<Date()+84)
AND ((tblTask.EndDate) Is Not Null) AND ((tblTask.Current)=-1))
ORDER BY tblEmployee.LastName;

I think the problem lies with the LEFT JOIN....

Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top