CODEWITHSUNDEEP
ms-accessvba
BossRoyce
BossRoyce

Reputation: 211

Access Delete Command Not Recognizing Variable

I'm trying to use Dlookup to set a variable that refers to a numerical value that is set in a table. I'm not able to get the formatting right to work the same way as if I just set a value directly. The first command works, the second attempts to delete an incorrect amount of records, and the third prompts me to "Enter Parameter"

Please help.

Sub purge_whse()

Dim purge As Integer

purge = DLookup("Setting_Value", "app_settings", "[Setting_Name]='data_retention'")

DoCmd.RunSQL "DELETE * FROM tbl_whse_cr WHERE DateDiff('m',[INSERT_DTTM],Now()) > 13;"
DoCmd.RunSQL "DELETE * FROM tbl_whse_cr_edi_cnfg WHERE DateDiff('m',[INSERT_DTTM],Now()) > 'purge';"
DoCmd.RunSQL "DELETE * FROM tbl_whse_cr_edi_cnfg WHERE DateDiff('m',[INSERT_DTTM],Now()) > purge;"

Upvotes: 0

Views: 78

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8402

What you want, I believe, is:

DoCmd.RunSQL "DELETE * FROM tbl_whse_cr_edi_cnfg WHERE DateDiff('m',[INSERT_DTTM],Now()) > " & purge & ";"

"Purge" is a variable, so it must exist outside of the RunSQL string.

Upvotes: 2

Related Questions