Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date as constant (Access 2000)

    I am deleting data from six tables before a certain date, for example < #1/1/2002#". Could i set up a constant and write there the date only once for example CnstDate = < #1/1/2002#" and why do i get errors when i try to replace it ?

    Public Function RemoveBefore() '
    Dim SQL As String
    SQL = " DELETE * FROM tblOffers " & _
    " WHERE tblOffers.offerdate < #1/1/2002#"
    CurrentDb.Execute SQL

    SQL = " DELETE * FROM orders " & _
    " WHERE orders.invoicedate < #1/1/2002#"
    CurrentDb.Execute SQL

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

    Re: Date as constant (Access 2000)

    You could do it like this:

    Public Function RemoveBefore()
    Dim SQL As String
    Const CnstDate = #1/1/2002#
    SQL = " DELETE * FROM tblOffers " & _
    " WHERE tblOffers.offerdate < #" & Format(CnstDate, "mm/dd/yyyy") & "#"
    CurrentDb.Execute SQL

    SQL = " DELETE * FROM orders " & _
    " WHERE orders.invoicedate < #" & Format(CnstDate, "mm/dd/yyyy" & "#"
    CurrentDb.Execute SQL
    End Function

    Note: it doesn't matter in this example, but you must specify the constant in US date format. For example, to set the constant to the 1st of August, 2006, you would use

    Constant CnstDate = #8/1/2006#

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date as constant (Access 2000)

    Thank you so much, Hans !

Posting Permissions

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