6 Replies - 738 Views - Last Post: 05 February 2012 - 04:04 AM Rate Topic: -----

#1 raycomp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-August 09

Need help with deleting records not working

Posted 04 February 2012 - 02:03 PM

I hope someone here might have the answer


I have a form from where I would like to delete all records before a specified date, the form has a text field and command button that should delete records in the database older than 3 months but it does not delete the records.

the code on my form is as follows

Private Sub Form_Load()
Dim test As String

test = DateAdd("m", -3, Now)    'this uses the current date and subtract 3 months
Text1.Text = test
Text1.Text = Format$(test, "mm/dd/yyyy")

End Sub 


Private Sub Command1_Click()

Dim cs As New ADODB.Connection
Dim rec As New ADODB.Recordset
Dim flds As New ADODB.Recordset

cs.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & "C:\Program Files\Hazmat Control\HazMat.mdb"
cs.Open

Dim date1 As String
cs.BeginTrans
cs.Execute "DELETE * From ControlDetailsA where Date <= " & Text1.Text    'date is the field in the access table
cs.CommitTrans

End Sub


If I however change the code to delete by control number then it is successful. Looks like I am missing something when trying to use the date field to select the records.

Is This A Good Question/Topic? 0
  • +

Replies To: Need help with deleting records not working

#2 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 303
  • View blog
  • Posts: 1,796
  • Joined: 26-March 09

Re: Need help with deleting records not working

Posted 04 February 2012 - 02:09 PM

You SQL query doesn't look right.

You don't Delete * from table, you just Delete from table and your date text should be cast as a date. I'm not 100% sure on the access syntax, but I think it might be #date#, so your query would be something like

Delete from Table where [Date]<= #date#
Was This Post Helpful? 1
  • +
  • -

#3 raycomp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-August 09

Re: Need help with deleting records not working

Posted 05 February 2012 - 12:29 AM

View Postmaj3091, on 04 February 2012 - 02:09 PM, said:

You SQL query doesn't look right.

You don't Delete * from table, you just Delete from table and your date text should be cast as a date. I'm not 100% sure on the access syntax, but I think it might be #date#, so your query would be something like

Delete from Table where [Date]<= #date#


Thanks for the reply. I'm still not getting it to actually delete the records in access table
Was This Post Helpful? 0
  • +
  • -

#4 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 303
  • View blog
  • Posts: 1,796
  • Joined: 26-March 09

Re: Need help with deleting records not working

Posted 05 February 2012 - 02:51 AM

What errors do you get if any?

Can you post your updated code, then we can see what changes you've made and maybe someone with more Access knowledge than I can advise further.
Was This Post Helpful? 0
  • +
  • -

#5 raycomp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-August 09

Re: Need help with deleting records not working

Posted 05 February 2012 - 02:53 AM

if I use the code below it works properly if I put a specific date in the code


"DELETE From ControlDetailsA where [Date] <= #4/2/2011#"


If I however want to change the query to use a date stored in text1.text field I'm having trouble with it. The idea is to delete all records <= to the date stored in text1.text,
the value in text1 text = 5/11/2011 (Text1.Text = Format$(test, "d/m/yyyy")


I have tried "DELETE From ControlDetailsA where [Date] <= " & Text1.Text NOTHING DELETED
"DELETE From ControlDetailsA where [Date] <= " & #Text1.Text# SYNTAX ERROR
"DELETE From ControlDetailsA where [Date] <= #text1.text#" SYNTAX ERROR
Was This Post Helpful? 0
  • +
  • -

#6 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 303
  • View blog
  • Posts: 1,796
  • Joined: 26-March 09

Re: Need help with deleting records not working

Posted 05 February 2012 - 03:25 AM

You have to put the #'s as part of the string, not around the Text1.text, otherwise you would get a syntax error??

Look at what you've written and said that works.

Something like below, should work

Quote

"DELETE From ControlDetailsA where [Date] <= #" & Text1.Text & "#"

Was This Post Helpful? 1
  • +
  • -

#7 raycomp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 47
  • Joined: 25-August 09

Re: Need help with deleting records not working

Posted 05 February 2012 - 04:04 AM

View Postmaj3091, on 05 February 2012 - 03:25 AM, said:

You have to put the #'s as part of the string, not around the Text1.text, otherwise you would get a syntax error??

Look at what you've written and said that works.

Something like below, should work

Quote

"DELETE From ControlDetailsA where [Date] <= #" & Text1.Text & "#"



maj3091 thanks a ton. Works like a charm
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1