Date minus zeven days, How hard can it be ?

[JeroenMioch]

The code below works and deletes records that are older then two years from the tblWaarschuwingen table.

Dim dteExpiry As Date
Dim strSQLDelete As String


dteExpiry = DateAdd("yyyy", -2, Date)


strSQLDelete = "DELETE from tblWaarschuwingen WHERE tblWaarschuwingen.datum2 <= #" & dteExpiry & "#;"
DoCmd.RunSQL strSQLDelete

If i try to do exaclty the same on another table but then date minus seven days, it fails !

Dim dteExpiry2 As Date
Dim strSQLDelete2 As String


dteExpiry2 = DateAdd("d", -7, Date)


strSQLDelete2 = "DELETE from tblBlauweKaart WHERE tblBlauweKaart.Kaart_tot <= #" & dteExpiry2 & "#;"
DoCmd.RunSQL strSQLDelete2

What am i missing here ?

[JeroenMioch]

It seems dteExpiry2 returns a time rather then a date 0:00:00
But i cant see why as im dimensioning dteExpiry2 as date

And when i use the DateAdd("yyyy", -2, Date) it returns the right value.

Im going berserk !

[orange]

These work for me aCC2010

?DateAdd("d", -7, Date)
01/11/2014
?Date
08/11/2014

What exactly is this
tblBlauweKaart.Kaart_tot
and how is it defined/dimmed?

[JeroenMioch]

I have aCC2010 as well.

The translation of Blauwe kaart is blue card.
And Kaart_tot means "Card untill"
So the data in the field is the date the card is valid.

Its so strange that if i code the date minus two years all goes well, and when i put date minus zeven days it doesnt work.
While i am certain that the code im using DateAdd("d", -7, Date) is valid

The field in the table is a date/time field

[thebigthing313]

It seems dteExpiry2 returns a time rather then a date 0:00:00
But i cant see why as im dimensioning dteExpiry2 as date

The Date datatype includes time in it as a decimal. Getting 0:00:00 just means that dteExpiry2 is set to 0.

As for what you're missing, I'm not entirely sure.

?DateAdd("d", -7, Date) in the immediate window works for me.

Alternatively, you can just do Date - 7.

dteExpiry2 = Date - 7