SQL DELETE WHERE Date = .... 2

[Dan688]

Good one today, using a VB6 application (currently programming) with a VFP database back end.

What I have as part of it is a table that lists relevant data and two date fields.

What I want to do is use a SQL delete where the date is equal to or less than another date entered.

The syntax looks fine but it comes up missing operator or type mismatch.

The field in the database is set to Date and is standard 8 in length ie DD/MM/YY.

The statement is below:

strSQL = "DELETE FROM serials WHERE " _
& "serials.date_shipped <= 27/04/05"

With rstDelSerials
.Open strSQL, mcnn2, , , adCmdText
End With 'rstDelSerials

p.s. just incase the date is set to American (which I won't expect I have also tried 01/01/01.

Any ideas?

 

[Mike Lewis]

Dan,

Try this:

strSQL = "DELETE FROM serials WHERE " _
 & "serials.date_shipped <= DATE(05,04,27)"

The arguments to the DATE() function are the year, month and day respectively. It makes no difference whether or not you are using American date formats in your app.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Dan688]

Many Thanks for that. I have added this in but now get:

Function arguement value, type, or count is invalid

 

[darrellblackhawk]

You have to use the full year with the Date() command.

strSQL = "DELETE FROM serials WHERE " _
& "serials.date_shipped <= DATE(2005,04,27)"

 

[Dan688]

Cheers, now works like a dream!

 

[Mike Lewis]

You have to use the full year with the Date() command.


Oops. Sorry, that slipped my mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)