Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to cut off dates (Access 2000)

    I cannot explain myself what happens with my function.It deletes all the records
    if i write in the control of my form the date 01.01.2004, even though i have orders after 01.01.2004

    Why do i delete eveything in my table.It seems to me the function is OK, but it is not and i am looking for help

    Public Function RemoveBefore()
    Dim SqlRemoveFromOrders As String
    SqlRemoveFromOrders = " DELETE DISTINCTROW orders.orderdate AS Expr1 " & _
    " FROM orders " & _
    " WHERE orders.orderdate<#" & Format(Forms!FrmCutOff!CmdCutOff, "mm/dd/yyyy") & "#"
    CurrentDb.Execute SqlRemoveFromOrders
    End Function

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

    Re: how to cut off dates (Access 2000)

    Temporarily comment out the line <code>CurrentDb.Execute SqlRemoveFromOrders</code> by inserting an apostrophe ' in front of it, and insert a new line <code>Debug.Print SqlRemoveFromOrders</code> immediately above or below it.

    Execute the function, then look at the Immediate window in the Visual Basic Editor (Ctrl+G). Copy the result into a reply.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: how to cut off dates (Access 2000)

    Why do you use in mm/dd/yyyy, I don't believe you need them.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: how to cut off dates (Access 2000)

    A few suggestions:

    * Deleting always deletes whole records, not just fields, so you can just write "Delete * Orders " & _
    * I notice the control on the form you refer to is CmdCutOff Usually a CMD control is a command button. Are you sure you are using the right one?

    * I would put in msgbox(SqlRemoveFromOrders ) to inspect the sql before it executes.

    * Yes, You do need the format stuff "mmVddVyyyy", although I don't know why you have V in there rather than / . (This of course could just be my ignorance.)

    * Is the control on form where you put the date, formatted as a date field? I have seen people say you need to explicityly convert the form value to a date with a conversion function, although I have never found the need myself.
    Regards
    John



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

    Re: how to cut off dates (Access 2000)

    The backslashes are not needed, but they don't hurt either. A backslash in a format string indicates that the next character is a literal character. On systems where the forward slash / is NOT the system date separator, Windows adds backslashes if you enter a date format with forward slashes. The Format function doesn't need them, however.

Posting Permissions

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