SQL error message

[Tom d]

' strSql = "Delete * from [tlb Blood Pressures] where [date Entered] >= " & fromdate & " AND [date Entered] <= " & todate
strSql = "Select * from [tlb Blood Pressures] where [date Entered] >= " & fromdate & " AND [date Entered] <= " & todate

MsgBox " strSql=" & strSql
DoCmd.RunSQL strSql, dbFailOnError

The select statement produces the follow error on the doCmd.RunSql command
A runsql action requires an argument consisting of an SQL statement.
Runtime error '2342'
The SQL Delete statement works in another module.

 

[arnelgp]

try:

strSql = "Select * from [tlb Blood Pressures] where [date Entered] >= " & Format$(fromdate, "\#mm\/dd\/yyyy\#") & " AND [date Entered] <= " & Format$(todate, "\#mm\/dd\/yyyy\#")

 

[Tom d]

try:

strSql = "Select * from [tlb Blood Pressures] where [date Entered] >= " & Format$(fromdate, "\#mm\/dd\/yyyy\#") & " AND [date Entered] <= " & Format$(todate, "\#mm\/dd\/yyyy\#")

Same error message

 

[cheekybuddha]

RunSQL only works with action queries (UPDATE/DELETE/INSERT etc), not SELECT

To view the records either copy the SQL in the MsgBox and paste into a new query in SQL view, or create a QueryDef in code and open that.

Ask if you're not sure how to do it.

 

[sonic8]

The SQL Delete statement works in another module.

Please double check your code. In this posting the Delete statement is commented out and replaced by a Select statement. - DoCmd.RunSql expects an action query (Insert, Delete, Update), not a Select statement.

 

[Minty]

You can't RUN a SELECT query, you can only RUN an action query.

EDIT : Blimey is this an Echo Chamber

 

[Tom d]

RunSQL only works with action queries (UPDATE/DELETE/INSERT etc), not SELECT

To view the records either copy the SQL in the MsgBox and paste into a new query in SQL view, or create a QueryDef in code and open that.

Ask if you're not sure how to do it.

How do you do that

 

[Tom d]

How do you do that

I have attached my access data base if that helps

 

[cheekybuddha]

Untested, but try something like:

' strSql = "Delete * from [tlb Blood Pressures] where [date Entered] >= " & Format$(fromdate, "\#mm\/dd\/yyyy\#") & " AND [date Entered] <= " & Format$(todate, "\#mm\/dd\/yyyy\#")
strSql = "Select * from [tlb Blood Pressures] where [date Entered] >= " & Format$(fromdate, "\#mm\/dd\/yyyy\#") & " AND [date Entered] <= " & Format$(todate, "\#mm\/dd\/yyyy\#")

MsgBox " strSql=" & strSql

  Const TMP_QRY As String = "TEMP_QRY"

  On Error Resume Next
' Delete the query if it exists already
  DoCmd.DeleteObject acQuery, TMP_QRY
  On Error GoTo 0
' Create  a query called "TEMP_QRY" using the SQL, and open
  CurrentDb.CreateQueryDef TMP_QRY, strSql
  DoCmd.OpenQuery TMP_QRY

 

[Tom d]

You can't RUN a SELECT query, you can only RUN an action query.

EDIT : Blimey is this an Echo Chamber

I have attached my access data base for you to see., how do you add an action query

 

[The_Doc_Man]

Running a SELECT SQL statement could have just silently done nothing. At least it let you know that the "action" was not going to work right.

So you SELECTed a few records. What was going to come next? Because a RunSQL is a run-and-done type of action. A SELECT is reserved for something with persistence, like maybe using a recordset or maybe feeding a COMBO or LIST BOX. There is no persistence in what you showed. You must ask yourself what you were going to do next... and then tell US so we can better advise you.

 

[arnelgp]

yes, like what the other says. RunSQL is an action command.

check the new code.

 

[cheekybuddha]

I have attached my access data base for you to see., how do you add an action query

Let's rewind a second - what are you actually trying to do?

It seems as if you wish to inspect the records that you are about to delete before deleting them. Is this just for you as developer, or do the users need to see this too?

 

[Tom d]

Running a SELECT SQL statement could have just silently done nothing. At least it let you know that the "action" was not going to work right.

So you SELECTed a few records. What was going to come next? Because a RunSQL is a run-and-done type of action. A SELECT is reserved for something with persistence, like maybe using a recordset or maybe feeding a COMBO or LIST BOX. There is no persistence in what you showed. You must ask yourself what you were going to do next... and then tell US so we can better advise you.

There is a text box on the same form as the button containg AvgOf Systolic
I want to load the text box with the average of the selected table records

Let's rewind a second - what are you actually trying to do?

It seems as if you wish to inspect the records that you are about to delete before deleting them. Is this just for you as developer, or do the users need to see this too?

Forget abut the commented out delete statement that is on another form and works fine.

Running a SELECT SQL statement could have just silently done nothing. At least it let you know that the "action" was not going to work right.

So you SELECTed a few records. What was going to come next? Because a RunSQL is a run-and-done type of action. A SELECT is reserved for something with persistence, like maybe using a recordset or maybe feeding a COMBO or LIST BOX. There is no persistence in what you showed. You must ask yourself what you were going to do next... and then tell US so we can better advise you.

 

[cheekybuddha]

I want to load the text box with the average of the selected table records

Then you probably don't need any code at all!

Try setting the ControlSource of the textbox to:

=DAvg("Systolic", "[tlb Blood Pressures]", "[date Entered] >= " & Format$(fromdate, "\#mm\/dd\/yyyy\#") & " AND [date Entered] <= " & Format$(todate, "\#mm\/dd\/yyyy\#"))

You may need to adjust the fromdate and todate variables for the names of controls on your form

 

[Tom d]

There is a text box on the same form as the button containing AvgOf Systolic
I want to load the text box with the average of the selected table records

Forget about the commented out delete statement that is on another form and works fine.

The code is on the button on the form frmSelected_statistics

 

[Tom d]

Then you probably don't need any code at all!

Try setting the ControlSource of the textbox to:

=DAvg("Systolic", "[tlb Blood Pressures]", "[date Entered] >= " & Format$(fromdate, "\#mm\/dd\/yyyy\#") & " AND [date Entered] <= " & Format$(todate, "\#mm\/dd\/yyyy\#"))

You may need to adjust the fromdate and todate variables for the names of controls on your form

I just use the list boxes to get the dates . in the SQL statement that does not work. I am trying to get selected records from the access table.

 

[cheekybuddha]

OK, you need to change your listboxes a little bit. You only need the dates from the table as RowSource, not the whole table!

So, for listboxes lst_from_date and lst_to_date, change:
RowSource: SELECT DISTINCT [Date Entered] FROM [tlb Blood Pressures] ORDER BY [Date Entered];
ColumnCount: 1
ColumnWidths: {empty}
Multi Select: None

Then, for the ControlSource of Text15:
=DAvg("Systolic","[tlb Blood Pressures]","([date Entered] >= " & Format$([lst_from_date],"\#mm\/dd\/yyyy\#") & " OR [lst_from_date] IS NULL) AND ([date Entered] <= " & Format$([lst_to_date],"\#mm\/dd\/yyyy\#") & " OR [lst_to_date] IS NULL)")

Then when you view your form the textbox should update as you select different dates in the listboxes.

 

[arnelgp]

here is your form.

 

[Tom d]

here is your form.

IT WORKED. Thank You
The problem was the SQL statement, other code fixed the problem.