Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL to delete specific records (2k)

    I have been running a version of the following SQL, from a macros, in order to delete the entire contents of a table in multiple databases.

    DoCmd.RunSQL "DELETE TBACTUAL.* FROM TBACTUAL IN "Jata204002.MDB""

    There are approx. 150 different MDB files which I delete in long SQL statement, changing the filename with each line.

    My problem is now... I need to delete only certain records from this same table, based on the date in a field named "DATE". In each case, I would need to delete records from a start date to an end date, in a table named TBACTUAL, in approx. 150 MDB files.

    Can anyone point me in the right direction to accomplish this?

    Let me know if you need more detailed information, or if my request is not sufficiently clear.

    Thanks in advance,

    Rob.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL to delete specific records (2k)

    You use a WHERE condition to limit the records to be deleted. If the dates are fixed, you can use the following SQL:
    "DELETE TBACTUAL.* FROM TBACTUAL IN 'Jata204020.MDB' WHERE [Date] Between #3/1/2003# And #6/1/2003#"
    (subsitute the appropriate dates; they must be in US date format)
    If the dates are variable, you could get them from variables, or from text boxes on a form, for example:
    "DELETE TBACTUAL.* FROM TBACTUAL IN 'Jata204020.MDB' WHERE [Date] Between #" & _
    Format(Forms!frmDateInput!txtStart, "mm/dd/yyy") & "# And #" & _
    Format(Forms!frmDateInput!txtEnd, "mm/dd/yyy") & "#"
    Side remark: Date is not a good name for a field in Access, for it is also the name of a VBA function and of a data type.

  3. #3
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL to delete specific records (2k)

    Thank you Hans!

    This works wonderfully, and it also means I can go home, rather than manually deleting the results of someone else's mess up.

    Thanks,

    Rob.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •