Solved

MSAcess VBA Running Update Query with a Variable

Posted on 2014-09-10
1
147 Views
Last Modified: 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"
   
    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
Comment
Question by:rrudolph
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40315820
try this

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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question