Results 1 to 8 of 8
  1. #1
    sstiebinger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2015
    Posts
    105

    Calculated fields no longer work when moving back end to SQL

    I have a continuous form with some calculated fields in the form footer.

    For instance: the form lists sales opportunities and includes the amount for each opportunity record.
    In the footer is a text box which displays the sum of the sales amounts.
    Filter the continuous form; the sum shows only what's displayed on the filter.

    The field on the continuous form is EstimatedAmount and the control source for the footer text box is
    Code:
    =Sum([EstimatedAmount])
    The record source for the form is
    Code:
    SELECT tblOpportunities.OpportunityID, tblOpportunities.CreateDate, tblOpportunities.AccountID, tblOpportunities.OpportunityOwner, tblOpportunities.OpportunityName, tblOpportunities.LeadSource, tblOpportunities.EstimatedAmount, tblOpportunities.EstimatedRMR, tblOpportunities.Probability, tblOpportunities.Status, tblOpportunities.CloseDate, tblOpportunities.SalesStage, tblOpportunities.NewAccount, tblAccounts_Vendors.AccountName FROM tblOpportunities INNER JOIN tblAccounts_Vendors ON tblOpportunities.AccountID = tblAccounts_Vendors.AccountID;
    This works just fine in my native access database.



    Now, I used the Microsoft SQL Server Migration Assistant for Access (SSMA) to convert this app to use a SQL back-end.
    Everything appears to be working normally, the continuous form displays properly and filters normally, but those fields in my footer just show "#Error"

    There is absolutely no change to the form, or the record sources for anything, only difference is that the linked table is now SQL instead of Access.

    Access 2016.
    SQL 2014.
    Windows 10.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Calculated fields no longer work when moving back end to SQL
    If you truly have a calculated fields in tables (not a control on the form footer), I would think mostly because SQL Server doesn't (AFAIK) support calculated FIELDs (or look up FIELDS or MVFs).

    I think you will have to redesign/modify your dB.

    Good reason to not use those field types in Access tables, if there is a possibility of converting to SQL Server/MySQL/Oracle.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I use SQL Server all the time, and the =Sum(FieldName) should still work fine. Make sure textbox name isn't the same; maybe that confuses Access. Every once in a while I have a situation where the sum didn't update on the screen properly, but never that error without something else going on.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    sstiebinger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2015
    Posts
    105
    The calculation is in the form, not the table.

    The form object is txtEstimatedAmount so there shouldn't be a conflict there.

    Should I remove the square brackets around the field? I'm out of the office now so I can't check.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The brackets shouldn't matter, but who knows?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Now, I used the Microsoft SQL Server Migration Assistant for Access (SSMA) to convert this app to use a SQL back-end.
    Everything appears to be working normally, the continuous form displays properly and filters normally, but those fields in my footer just show "#Error"
    Did you try deleting the controls in the footer and re-creating them?

  7. #7
    sstiebinger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2015
    Posts
    105
    Microsoft's SSMA for Access tool made a lot of terrible changes to my forms in general.
    The tool seems perfectly fine for migrating tables, but forms get screwed up pretty badly.

    This was originally a split database that I combined into a single database so that I could use the SSMA to automatically generate the SQL links...
    The SSMA tool is terrible at this.

    My plan is to just use the SSMA tool on the back-end database and then manually link the tables in the front end.

    I'm going to close this thread with the reminder to use the right tool for the right job.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My plan is to just use the SSMA tool on the back-end database and then manually link the tables in the front end.
    That is what I did. SSMA is really just for tables (IMO).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 01-09-2015, 12:15 PM
  2. Moving the Back End of a split Access DB.
    By Robeen in forum Access
    Replies: 1
    Last Post: 11-06-2013, 12:09 PM
  3. Moving a new table to the back-end
    By premis in forum Access
    Replies: 3
    Last Post: 07-25-2013, 12:43 PM
  4. Replies: 16
    Last Post: 06-21-2012, 10:00 PM
  5. Moving a back-end file
    By Ted C in forum Security
    Replies: 1
    Last Post: 08-06-2010, 12:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums