Date Syntax in vba is wrong ?

[burrina]

If Me.TransDate(TransDate - 7) And Date And Date And Me.PeriodTypeID = "d" Or "ww" Then

I want to delete records older than a week from TransDate until today. If the date in TransDate is older than 7 days then delete record(s)


My syntax is wrong though?

[narang]

If Me.TransDate(TransDate - 7) And Date And Date And Me.PeriodTypeID = "d" Or "ww" Then

I want to delete records older than a week from TransDate until today. If the date in TransDate is older than 7 days then delete record(s)
My syntax is wrong though?

Hello,

The following could be a probable solution

Let the name of the table be table1
name of the field transdate

The following code may be enetered on the click event of the command button object

Dim con As New ADODB.Connection
Set con = Application.CurrentProject.Connection

con.Execute "delete from [table1] where (date()-[table1].[transdate])>7", , adCmdText
con.Close
Set con = Nothing

Rgds

[burrina]

Ok, I tried this but it would not compile?.

con.Execute "delete from [tblmissedtransactions] where (date()-[tblmissedtransactions].[transdate])>7" And Me.PeriodTypeID = "d" Or "ww", , adCmdText

[narang]

Ok, I tried this but it would not compile?.

con.Execute "delete from [tblmissedtransactions] where (date()-[tblmissedtransactions].[transdate])>7" And Me.PeriodTypeID = "d" Or "ww", , adCmdText

Hello,

To me it appears that the table name is tblmissedtransactions

This table has among others 2 fields namely

1. transdate- This is a date type of field
2. PeriodTypeID- This is a text type of field

If the above is correct you may try the following

Private Sub Command6_Click()
Dim con As New ADODB.Connection
Set con = Application.CurrentProject.Connection

con.Execute "delete from [tblmissedtransactions] where ((date()-[tblmissedtransactions].[transdate]>=7) and (trim(nz([PeriodTypeID]))='d' or trim(nz([PeriodTypeID]))='ww'))", , adCmdText
con.Close
Set con = Nothing



End Sub

Rgds