Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

vb6 Using SQL 'Delete From' with a date

Posted on 2006-04-13
3
Medium Priority
?
320 Views
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

Gordon
0
Comment
Question by:Gordon_Atherley
3 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1000 total points
ID: 16450613
Hi Gordon_Atherley,

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


Cheers!
0
 
LVL 27

Assisted Solution

by:Dabas
Dabas earned 1000 total points
ID: 16450622
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")

Dabas
0
 

Author Comment

by:Gordon_Atherley
ID: 16452936
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

Gordon
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

577 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