SQL Problem Delete Query

[DanBurn]

Hi I’m trying to convert some old queries into VBA to be run on the Fly. I’m having a problem with this.

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]strSQLhistory = "DELETE tblCasualtyHistory.PeriodStartDate, tblCasualtyHistory.Comment, * FROM tblCasualtyHistory WHERE PeriodStartDate >31/12/ 2004 And Comment is null;"
    DoCmd.RunSQL strSQLhistory[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

When this is run from the Query builder in Ms Access it does what it is supposed to (delete all files that are after the date given as long as they don’t have anything in the comment box).

When I run this from VBA it deletes everything except for those records with a comment, for some reason it takes no account of the date part.

Any ideas?

 

[DCrake]

You need to encompass the date with # signs. vis

strSQLhistory = "DELETE PeriodStartDate, Comment, * FROM tblCasualtyHistory WHERE PeriodStartDate >#31/12/2004# And Comment Is Null;"
    DoCmd.RunSQL strSQLhistory

 

[georgedwilkinson]

Try:

DELETE * FROM tblCasualtyHistory WHERE PeriodStartDate > #31/12/2004# And nz(Comment,'') = '';

You had a couple of extra spaces and didn't delimit your date. You may need to change the date to the American version, too (12/31/2004).

HTH.

 

[DanBurn]

I have actually tried all those. In fact in my code i dont use the date, a variable is lookup from somewhere else i just put that in so it makes more sense on here

 

[DCrake]

So show us the actual problem then we can try and answer it correctly

 

[DanBurn]

Heres the main code.

Private Sub OLEPSok1_Click()
Dim strSQLhistory As String
Dim YearFilter As Variant
 
YearFilter = DMin("Year", "qryYear%change") - 1
 
strSQLhistory = "Delete * FROM tblCasualtyHistory WHERE PeriodStartDate >31/12/" & [YearFilter] & " And Comment is null;"
    DoCmd.RunSQL strSQLhistory

 

[DCrake]

Couple of issues here.

First DON'T use characters such as % in field names or object names.
Using reserved words can cause code to fail, Year is also a bad word because Access has a Year() function and it will get confused when interpreting the sql.


Next revise your code as follows

Dim strSQLhistory As String
Dim YearFilter As String
[COLOR="Red"]Dim DteCriteria As Date[/COLOR]

YearFilter = [COLOR="red"]CStr([/COLOR]DMin("Year", "qryYear%change") - 1[COLOR="red"])[/COLOR]
 
[COLOR="red"]DteCriteria = CDate("31/12/" & YearFilter)[/COLOR]

strSQLhistory = "Delete * FROM tblCasualtyHistory WHERE PeriodStartDate > [COLOR="red"]#" & DteCriteria & "#[/COLOR] And Comment [COLOR="Red"]Is Null[/COLOR];"

I am not aware what your qryYear%change is doing.

The code in red indicates the changes you need to make.

 

[DanBurn]

Not had chance to look but thaks for the help i will let you know if it works