MSAcess VBA Running Update Query with a Variable

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"
   
    DoCmd.RunSQL TBCOSQL
       
       
    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*'))"

    DoCmd.RunSQL TBCOSQL
   
'\ 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"

Debug.Print TBCOSQL



DoCmd.RunSQL TBCOSQL


The Immediate Window:

UPDATE AJEWORK_ACCRUAL SET AJEWORK_ACCRUAL.AJE_AMT = [AJEWORK_ACCRUAL]![AJE_AMT] * AccrualFrac
Rick RudolphAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

TBCOSQL = "UPDATE AJEWORK_ACCRUAL SET AJEWORK_ACCRUAL.AJE_AMT =  [AJEWORK_ACCRUAL]![AJE_AMT]" & "*" & AccrualFrac
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.