Coding an open recordset statment

[bknight]

In A97 I'm trying to delete data in a date range. The first step is to
answer a message box.

intMsgBox = MsgBox("Do you wish to delete the only last data?",
vbYesNoCancel, "Delete data")
'Yes--Will Rs.MoveLast and delete data
'Cancel--Ends Sub
'No- Leads to the following steps
If intMsgBox = 7 Then '7 is the value of a No selection
dteStartDate = InputBox("What Is The Begining Date", "Start Date") 'Example
5/1/2009
dteEndDate = InputBox("What Is The Ending Date", "End Date") 'Example
5/22/2009
End If

Now that I have a date range, I build a SQL statement.

strDateRange = "Select * From tblName Where InvDay >=#" & dteStartDate & "#
And InvDay <=#" & dteEndDate & "# Order By DateIndex"
'Results in immediate window
'Select * From tblName Where InvDay >=#5/1/2009# And InvDay <=#5/22/2009#
Order By DateIndex
'This is the range that is to be deleted
'This looks good, BUT

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("'" & strDateRange & "'")' RTE #3078 Cannot find
table...

'In testing I eliminated the end date
strDateRange = "Select * From tblName Where InvDay >=#" & dteStartDate & "#
Order By DateIndex"
'Results in immediate window
'Select * From tblName Where InvDay >=#5/1/2009# Order By DateIndex

Set Db = CurrentDb
Set Rs = Db.OpenRecordset("'" & strDateRange & "'") ' Resulted in the same
error message

'So I made the steps look like this
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Select * From tblName Where InvDay >=#5/1/2009#
Order By DateIndex")

Everything worked and the rest of the code is easy. Any suggestions?

 

[Douglas J. Steele]

All you need is

Set Rs = Db.OpenRecordset(strDateRange)

However, I don't understand why you're opening a recordset if what you want
to do is delete data. Use a Delete query instead.

 

[bknight]

I tried that earlier and didn't work, but does now. I have been tinkering
with it and may have added a space or something.
It is done at run time from a form where queries would not be available. If
there is a better way to do it, please comment.

 

[Douglas J. Steele]

I don't understand why you say that "queries would not be available".

You can create queries or run SQL statements in the runtime.

 

[bknight]

I didn't post the entire bit of code, but I guess the recordset would be my
query. I guess you are suggesting that I Use something like this?

DELETE *
FROM tblName
WHERE (((InvDay)>=#5/1/2009# And (InvDay)<=#5/22/2009#));

 

[Douglas J. Steele]

If your intent is to delete all records from tblName where InvDay is between
1 May 2009 and 22 May 2009, then yes, you'd use something like

Dim strSQL As String

strSQL = "DELETE * FROM tblName " & _
"WHERE InvDay>=#5/1/2009# And InvDay<=#5/22/2009#"
CurrentDb.Execute strSQL, dbFailOnError

You could also use

strSQL = "DELETE * FROM tblName " & _
"WHERE InvDay BETWEEN #5/1/2009# AND #5/22/2009#"

 

[bknight]

What if the desire were to delete some information, not the date as that
won't change ever?

 

[Douglas J. Steele]

I'm afraid I don't understand what you mean by that.

Delete statements delete matching rows from the table, not just the data
contained in specific fields.