Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

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
0
rrudolph
Asked:
rrudolph
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this

TBCOSQL = "UPDATE AJEWORK_ACCRUAL SET AJEWORK_ACCRUAL.AJE_AMT =  [AJEWORK_ACCRUAL]![AJE_AMT]" & "*" & AccrualFrac
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now