How to delete part of a table based on dates

[anb001]

Below I have some code, that I use to delete all information in a table:

Private Sub cmdDeleteAllData_Click()

Dim answer As String
Dim answer2 As String
Dim answer3 As String
Dim strSQL1 As String

answer = MsgBox("Are you sure you want to delete all data?.", vbYesNo)

If answer = 6 Then

    answer2 = MsgBox("Are you absolutely sure? You can not undo the this!!!", vbYesNo)

    If answer2 = 6 Then

        strSQL1 = "DELETE * FROM qryReport"
        CurrentProject.Connection.Execute strSQL1

        answer3 = MsgBox("All data has been erased!", vbOKOnly)
    
    Else
    
        Exit Sub
    
    End If
Else

    Exit Sub
    
End If

End Sub

This code deletes all data in the table. I would like to have it tweaked a little bit, so it only deletes the data from the table, where the field "ReportDate" is between the specified dates in the text boxes "txtDate1" & "txtDate2".

I see four scenarios:

1. Both txtDate1 and txtDate2 has been updated. In this case all data between those two dates should be deleted.

2. None of the two text boxes has been updated. In this case all data should be deleted.

3. Only txtDate1 is updated, and txtDate2 is left blank. In this case all data after txtDate1 should be deleted.

4. Only txtDate2 is updated, and txtDate1 s left blank. In this case all data before txtDate2 should be deleted.

All assistance is highly appreciated.

/Anders

 

[Alansidman]

First look at this:
http://office.microsoft.com/en-us/a...from-an-access-database-HA001173951.aspx#BM13
Secondly: Make a backup of your table in case you make a mistake. You can always delete the backup once you have your records correct.

Create your delete query in the QBE, copy the SQL statement to your VBA code and insert it.

Alan

 

[anb001]

Alan,

I have tried your suggestion, and it brought me part of the way. I'm getting an error though: This is the code I have now (still a few criterias are missing):

Private Sub cmdDeleteAllData_Click()

Dim answer As String
Dim answer2 As String
Dim answer3 As String
Dim strSQL1 As String

answer = MsgBox("Are you sure you want to delete all data?.", vbYesNo)

If answer = 6 Then

    answer2 = MsgBox("Are you absolutely sure? You can not undo the this!!!", vbYesNo)

    If answer2 = 6 Then

        If IsNull(Me.txtAfterDate) And IsNull(Me.txtBeforeDate) Then

        strSQL1 = "DELETE qryReport.*, qryReport.DateReport FROM qryReport"
        CurrentProject.Connection.Execute strSQL1

        ElseIf Len(Me.txtBeforeDate) > 0 And IsNull(Me.txtAfterDate) Then
        
        strSQL1 = "DELETE qryReport.*, qryReport.DateReport FROM qryReport WHERE qryReport.DateReport < '" & Me.txtBeforeDate & "'"
        CurrentProject.Connection.Execute strSQL1
        
        answer3 = MsgBox("All data has been erased!", vbOKOnly)
    
        End If
    
    Else
    
        Exit Sub
    
    End If
Else

    Exit Sub
    
End If

End Sub

The problem is after the "ELSEIF". The line " CurrentProject.Connection.Execute strSQL1" produces following error:

"Runtime error: Data type mismatch in criteria expression."

What am I doing wrong???

/Anders

 

[vbaInet]

Here's the logic of what you're trying to do:

If answer2 = vbYes Then
    strSQL1 = "DELETE * FROM qryReport WHERE [DateField] BETWEEN " & _
               Nz(Me.txtDate1, [COLOR=Blue]DMin(...)[/COLOR]) & " AND " & _
               Nz(Me.txtDate2, [COLOR=Blue]DMax(...)[/COLOR]) & ";"
End If

Code DMin() to get the minimum date in the query and DMax() to get the max date in the query. Nz() means, use the alternative value when txtDateX is Null.

If you don't have a Date Input Mask or a Date Format set on those textboxes, then you will need to perform some validation on them to ensure that what was entered are actually valid dates. Hint: use IsDate()

You may need to format the dates too.

 

[anb001]

OK, I have updated my code to the suggested, and it work in the sence that no errors pop up, but it all doesn't delete anything. So I assume that something is still missing.

The text boxes are formatted as date and with proper input format.

The code now looks like this:

        strSQL1 = "DELETE * FROM qryReport WHERE qryReport.DateReport BETWEEN " & _
               Nz(Me.txtBeforeDate, DMax("[DateReport]", "qryReport")) & " AND " & _
               Nz(Me.txtAfterDate, DMin("[DateReport]", "qryReport")) & ";"
        CurrentProject.Connection.Execute strSQL1

 

[anb001]

I'm a little bit further now. The date in the textboxes needed to be a special date format in the SQL.

That means that the code works, if dates are entered in both text boxes. However, if either one is blank, it doesn't work, as it gives be a 'null' value, and not the minimum and maximum date.

Any idea how I get the DMin and DMax part solved?

         strSQL1 = "DELETE * FROM qryReport WHERE qryReport.DateReport BETWEEN " & _
         Nz(VBA.Format(Me.txtAfterDate, "\#mm\-dd\-yyyy\#"), DMax("[DateReport]", "qryReport")) & " AND " & _
         Nz(VBA.Format(Me.txtBeforeDate, "\#mm\-dd\-yyyy\#"), DMin("[DateReport]", "qryReport"))
         CurrentProject.Connection.Execute strSQL1

 

[Privateer]

Anders,
You don't need multiple answer variables and you do need to check the dates before you start anything. Try using the code below, it has worked well for me.

dim Answer as byte 'not a string
dim Criteria as string
dim strMSG as string
Dim strSQL as string

If isdate(Date1) = false then 'This is a fatal error, make sure the text box has a valid date!
strmsg = "You are missing a date."
MsgBox strMsg, vbCritical + vbOKOnly, "Date Error"
exit sub
end if

If isdate(Date2) = false then
strmsg = "You are missing a date."
MsgBox strMsg, vbCritical + vbOKOnly, "Date Error"
exit sub
end if

strMsg = "Do you want to delete these records?"
Answer = MsgBox(strMsg, vbQuestion + vbDefaultButton3 + vbYesNoCancel, "Delete Information")
Select Case Answer
Case vbYes
strMsg = "Are you certain you want to delete these records?"
Answer = MsgBox(strMsg, vbCritical + vbDefaultButton2 + vbYesNo, "Delete Information")
If Answer = vbYes then
Criteria = "([qryReport].[Date] Between #" & Date1 & "# AND #" & Date2 & "#)"
strSQL = ""DELETE * FROM qryReport WHERE (" & Criteria & ");"
CurrentProject.Connection.Execute strSQL
end if
Case vbNo
'call ExitForm 'You may have a different subroutine
Case vbCancel
'do nothing, person wants to go back and edit the form
End Select

There might be a few errors, I just typed it in from memory and I didn't use the real names of your date text boxes. I would also suggest you delete the records in the table not in the query. That way when you execute the report query you will get the desired results. Hope this helps.
Privateer

 

[vbaInet]

         strSQL1 = "DELETE * FROM qryReport WHERE qryReport.DateReport BETWEEN " & _
         Nz(VBA.Format(Me.txtAfterDate, "\#mm\-dd\-yyyy\#"), [COLOR=Red]DMax[/COLOR]("[DateReport]", "qryReport")) & " AND " & _
         Nz(VBA.Format(Me.txtBeforeDate, "\#mm\-dd\-yyyy\#"), [COLOR=Red]DMin[/COLOR]("[DateReport]", "qryReport"))

Compare where you wrote your DMin() and DMax() to mine and you will see where you went wrong. Again, you may need to format the date returned from those two as well, and if they both require formatting then I suggest you format them as a whole.

 

[vbaInet]

Anders,
You don't need multiple answer variables and you do need to check the dates before you start anything. Try using the code below, it has worked well for me.

If isdate(Date1) = false then 'This is a fatal error, make sure the text box has a valid date!
strmsg = "You are missing a date."
MsgBox strMsg, vbCritical + vbOKOnly, "Date Error"
exit sub
end if

If isdate(Date2) = false then
strmsg = "You are missing a date."
MsgBox strMsg, vbCritical + vbOKOnly, "Date Error"
exit sub
end if

Privateer, this doesn't meet the requirements. These are the OP's requirements:

I see four scenarios:

1. Both txtDate1 and txtDate2 has been updated. In this case all data between those two dates should be deleted.
2. None of the two text boxes has been updated. In this case all data should be deleted.
3. Only txtDate1 is updated, and txtDate2 is left blank. In this case all data after txtDate1 should be deleted.
4. Only txtDate2 is updated, and txtDate1 s left blank. In this case all data before txtDate2 should be deleted.

What you've just suggested qualifies only number 1.

 

[Privateer]

vbaInet,
Thanks for setting me straight, I focused on the last few posts and only read the original code, I never saw the four scenarios. Lesson learned. My apologies for barging in, thought the dates were going through as strings rather than dates.

Having read them, you could set up four if statements:
1) checking if both date fields are populated, criteria = between d1 and d2
2) checking if both are null, criteria = ""
3) checking if date1 is populated and date2 is null, criteria >d1
4) checking if date1 is null and date2 is populated, criteria <d2

Then, if len(criteria) > 0 then
SQL with Where Criteria ...
else
SQL without any criteria string
end if
Good Luck
Privateer

 

[vbaInet]

No problemo!

Having read them, you could set up four if statements:
1) checking if both date fields are populated, criteria = between d1 and d2
2) checking if both are null, criteria = ""
3) checking if date1 is populated and date2 is null, criteria >d1
4) checking if date1 is null and date2 is populated, criteria <d2

You could but that will be too much work (for the OP) which was why I gave a solution in my first post using Nz() and Dmin()/DMax() functions as the alternative values. It covers all four scenarios.

The OP will eventually need to use the IsDate() example you gave in your first post to validate the date. Only valid date it if the control is Not empty.

 

[anb001]

Finally got it to work. Thanks for all the help. In the end, this is what it looks like:

         strSQL1 = "DELETE * FROM tblStability WHERE tblStability.DateReport BETWEEN " & _
                   VBA.Format(Nz(Me.txtAfterDate, DMin("[DateReport]", "tblStability")), "\#mm\-dd\-yyyy\#") & " AND " & _
                   VBA.Format(Nz(Me.txtBeforeDate, DMax("[DateReport]", "tblStability")), "\#mm\-dd\-yyyy\#")

 

[vbaInet]

I can see you took on board all that was mentioned.

Good work Anders!