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.

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

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.