vb6 Using SQL 'Delete From' with a date

Posted on 2006-04-13
Last Modified: 2010-04-07
Experts please

In a vb6 app which draws data from an Access 2000 database, I want to delete all records that are 7 or more days old. The following deletes all records regardless of date. ActDate is a date/time field

    Dim dCutOff As Date
    dCutOff = DateAdd("d", -7, Date)
    sSQL = "DELETE FROM activity where ActDate" & " < " & "#" & dCutOff & "#;"
    Set rs = cn.Execute(sSQL, , adCmdText)

What's wrong, and what should be done to fix the problem, please

Question by:Gordon_Atherley
    LVL 69

    Accepted Solution

    Hi Gordon_Atherley,

    Try this
    sSQL = "DELETE FROM activity where ActDate < #" & format(dCutOff, "yyyy/mm/dd") & "#;"

    LVL 27

    Assisted Solution

    Hi Gordon_Atherley,
    Is your system set to dd/mm/yyyy? In that case, the program might be looking at say, the the 4th of June instead of the 6th of April, causing your problem.

    One way to overcome this is to change the following line:

        dCutOff = Format(DateAdd('d',-7,Now), "yyyy-mmm-dd")


    Author Comment

    emoreau and Dabas

    You diagnosed the problem and provided effective solutions which differed slightly in approach. Your responses were but 2 minutes apart. Splitting the points seemed the fairest way to go

    Thank you both


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    755 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

    22 Experts available now in Live!

    Get 1:1 Help Now