Reference TimeStamp in Delete ?

Posted on 2006-04-05
Last Modified: 2012-05-05

How do I reference TimeStamp data in a Delete query ?

I would like to delete all entries in a table that were
created before 2006. The data is stored in TimeStamp
format and is drawn from a linked MySQL table.

In Access, it appears as: 4/5/2006 1:47:25 PM

I would like to write a Delete SQL, such as:

DELETE * FROM Table WHERE TimeTS<2006;

Phil Miller
Question by:pillmill
    LVL 19

    Expert Comment

    DELETE * FROM Table WHERE year(TimeTS)<2006
    LVL 39

    Accepted Solution

    DELETE * FROM Table WHERE TimeTS<#2006/1/1#;

    Access stores all dates as doubles as the number of days since 12/30/1899 12:00:00 AM  (Format(0,"mm/dd/yyyy hh:nn:ss AM/PM") = 12/30/1899 12:00:00 AM).  For example 09/11/2005 10:56:40 AM is stored as 38606.4560185185 or 38606.4560185185 days since 12/30/1899 12:00:00 AM  (CDbl(#09/11/2005 10:56:40 AM#) = 38606.4560185185).  You can input and display the date/time in any format you choose.  

    Also keep in mind that Access is an American product so VBA is sometimes expecting mm/dd/yyyy even when the regional setting is set for something else.  yyyy/mm/dd will always work for VBA input.

    VBA data type-declaration characters:
    String: bracket with quotation marks or apostrophes (required)
    Example: "this is a string" or 'This is a string'

    Date/Time: Bracket with pound signs (required)
    Example: #6/1/1947#, #June 17, 1999#, #3:30pm#, #1/5/63 2:33#

    Number (Base 10): No type-declaration character needed (The number will automatically be converted to the type needed)
    Example: 1234, 67.77

    Hexadecimal number: precede with &h
    Example: &H10

    0ctal number: precede with &O
    Example: &O10

    Optionally, you can also explicitly specify the data type:
    Integer: Append with percent sign
    Example: 1234%

    Long: Append with ampersand  
    Example: 67877768&

    Currency: Append with at sign
    Example: 62.45@

    Single: Append with exclamation point  
    Example: 123.777!, 67.00!

    Double: Append with number sign
    Example: 677767.8888#

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now