SQL Qury (Access XP)

Topic

New Reply

Could some kind person please help.

I have managed to write a delete query which does exactly what I would like, but I cannot work out how to phrase it in VBA

Could anyone help please.

Even a pointer in the right directtion would help. I’m sure I can get the syntax eventually if given a decent nudge in the right direction

Thanks

Code is below

Colin

DELETE tblBankAccount.*, tblBankAccount.DateInput, Left([Description],5) AS Expr1, Left([Description],7) AS Exp2, Left([Description],3) AS Exp3
FROM tblBankAccount
WHERE (((tblBankAccount.DateInput)=Date()) AND ((Left([Description],5))=”Mthly”)) OR (((tblBankAccount.DateInput)=Date()) AND ((Left([Description],7))=”Ann S/O”)) OR (((tblBankAccount.DateInput)=LastofPrevMonth(Date())) AND ((Left([Description],3))=”Int”));

Reply | Quote

Replies

You could set up the SQL in a text string and use the .execute or RunSql command to run it.

Setup the string variable:
strSQL = “DELETE tblBankAccount.*, tblBankAccount.DateInput, Left([Description],5) AS Expr1, Left([Description],7) AS Exp2, Left([Description],3) AS Exp3
FROM tblBankAccount
WHERE (((tblBankAccount.DateInput)=Date()) AND ((Left([Description],5))=’Mthly’)) OR (((tblBankAccount.DateInput)=Date()) AND ((Left([Description],7))=’Ann S/O’)) OR (((tblBankAccount.DateInput)=LastofPrevMonth(Date())) AND ((Left([Description],3))=’Int’));”

Be sure to enclose the Mthly with single quotes instead of double quotes.

HTH
Pat

Reply | Quote

Thanks Pat

Works just fine

I never even thought of its as simple as that.

Colin

Reply | Quote