Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Unanswered: VBA Delete Using Date

    I have a piece of VBA which deletes a row from a linked table based on the data in the name column being 'John'...

    CurrentDb.Execute "DELETE FROM TABLE WHERE NAME = 'John'"

    This works fine but I now want to delete records with name = 'John' and where the date column is a specific date...

    CurrentDb.Execute "DELETE FROM TABLE WHERE DATE_REPORTED = #01/07/2009 12:00:00# AND NAME = 'John'"

    There is a record with the date and name 01/07/2009 12:00:00 and John but it doesnt seem to delete.

    Must be something wrong with the way I am using the date?

    Please Help.

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    The date is ambiguous, ie. Is it 1st July 2009 or 7th January 2009? VBA will automatically assume US settings, so 7th January. If your table is in dd/mm/yyyy format then you can use the format function to return the correct date:
    Date/Time: Return Dates in US #mm/dd/yyyy# format

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    You can avoid worrying about date formats if you use query parameters. It also protects you against SQL injection.

    Here's an MSDN article on how to do it.

Posting Permissions

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