MSAcess VBA Running Update Query with a Variable
Posted on 2014-09-10
I am trying to update a table based upon a single variable that is calculated from the date difference between two dates. If I replace the variable AccrualFrac with an actual Fraction the code works fine. It prompts for a parameter the way I currently have it, and if I input a legitimate number it executes fine. The code snippet is attached along with the Immediate Window which shows the SQL that is being run:
If chkdate + 14 > MEndDate Then
' \ Calculate the Actual Accrual Fraction
AccrualFrac = (MEndDate - PEnddate) / DaysInMonth(PEnddate)
TBCOSQL = "DELETE AJEWORK_ACCRUAL.* "
TBCOSQL = TBCOSQL & "FROM AJEWORK_ACCRUAL"
TBCOSQL = "INSERT INTO AJEWORK_ACCRUAL ( End_Date, Entity, AcctNo, AJE_AMT ) "
TBCOSQL = TBCOSQL & "SELECT AJEWORK.End_Date, AJEWORK.Entity, AJEWORK.AcctNo, AJEWORK.AJE_AMT "
TBCOSQL = TBCOSQL & "FROM AJEWORK "
TBCOSQL = TBCOSQL & "WHERE (((AJEWORK.AcctNo) Like '5*' Or (AJEWORK.AcctNo) Like '6*' Or (AJEWORK.AcctNo) Like '7*' Or (AJEWORK.AcctNo) Like '8*' Or (AJEWORK.AcctNo) Like '9*'))"
'\ Modify the amounts to the correct amount of accrual based upon the payroll date
TBCOSQL = "UPDATE AJEWORK_ACCRUAL SET AJEWORK_ACCRUAL.AJE_AMT = " & "[AJEWORK_ACCRUAL]![AJE_AMT]" & " * " & "AccrualFrac"
The Immediate Window:
UPDATE AJEWORK_ACCRUAL SET AJEWORK_ACCRUAL.AJE_AMT = [AJEWORK_ACCRUAL]![AJE_AMT] * AccrualFrac