Reference TimeStamp in Delete ?


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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DELETE * FROM Table WHERE year(TimeTS)<2006
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#

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.