none
Calculations not working on one computer but work on other computer running same version RRS feed

  • Question

  • Does anyone know what setting might limit a calculation query from working when it works perfectly fine on another computer?
    Thursday, August 13, 2015 6:03 PM

Answers

  • To see whether or not your query is finding any records to update, take and copy the following SQL and create a new query with it by selecting Query Design under the Create Tab.  Once there, select View>SQL under the Design Tab, then paste this SQL statement. 

    Next, under the design Tab again, Select View>Datasheet View.  If records are returned, then there is data that meets your criteria, otherwise, there isn't.  You will need to have your two forms open with the appropriate settings, of course.

    SELECT * FROM [Timecard Workshop Billing Daily]
    WHERE ((([Timecard Workshop Billing Daily].Wkhr1)>0.1) AND (([Timecard Workshop Billing Daily].BillCd) Not Like "W*") AND (([Timecard Workshop Billing Daily].[Worker Name])=[Forms]![Timecard Workshop NEW Billing]![Worker Name]) AND (([Timecard Workshop Billing Daily].Workdate)>=[Forms]![FRM_MAIN SHOP]![St Wk] And ([Timecard Workshop Billing Daily].Workdate)<=[Forms]![FRM_MAIN SHOP]![end wk]));

    If you aren't able to see any data returned, try removing your were conditions, one at a time to see if you get results.


    Thursday, August 13, 2015 9:37 PM

All replies

  • Post your query's SQL...
    Thursday, August 13, 2015 6:08 PM
  • UPDATE [Timecard Workshop Billing Daily] LEFT JOIN [Workshop update calculator for time to pay time] ON ([Timecard Workshop Billing Daily].BillCd = [Workshop update calculator for time to pay time].StBillcd) AND ([Timecard Workshop Billing Daily].[Worker Name] = [Workshop update calculator for time to pay time].[Worker Name]) AND ([Timecard Workshop Billing Daily].Sttm1 = [Workshop update calculator for time to pay time].Sttm1) AND ([Timecard Workshop Billing Daily].Stp1 = [Workshop update calculator for time to pay time].Stp1) AND ([Timecard Workshop Billing Daily].Workdate = [Workshop update calculator for time to pay time].Workdate) SET [Timecard Workshop Billing Daily].StRate = " ", [Timecard Workshop Billing Daily].Wkhr1 = " "
    WHERE ((([Timecard Workshop Billing Daily].Wkhr1)>0.1) AND (([Timecard Workshop Billing Daily].BillCd) Not Like "W*") AND (([Timecard Workshop Billing Daily].[Worker Name])=[Forms]![Timecard Workshop NEW Billing]![Worker Name]) AND (([Timecard Workshop Billing Daily].Workdate)>=[Forms]![FRM_MAIN SHOP]![St Wk] And ([Timecard Workshop Billing Daily].Workdate)<=[Forms]![FRM_MAIN SHOP]![end wk]));
    Thursday, August 13, 2015 6:57 PM
  • Can I give you some advice before we begin?  First off, your table and other object names are insanely too long.  Furthermore, object names should not contain any spaces, regardless of whether or not Access will allow it.  Object names should be descriptive, but brief.  Your names are nearly sentences describing the object. 

    Please see the following link:

    https://support.microsoft.com/en-gb/kb/826763

    The next thing I see wrong in your SQL is that you have what appear to be numeric fields, StRate and WkHr1.  That said, you are attempting set the value of these fields to a single space (" ").  If the fields mentioned aren't numeric, they should be, and I can't think of a single good reason why anyone should ever assign a space character to a field.  If you don't want a value in either of these fields, then you should assign a null, otherwise you should assign a zero (0). 

    The next problem I see with your SQL is all the left joins between the two tables.  Following that, you seem to be requiring criteria from more than two different forms.  This just looks wrong all together as it isn't a good design.  You should only require criteria from one form at the most.

    What I don't see is anything calculated.

    Apparently, your records and/or your criteria possibilities are different between the two machines.




    Thursday, August 13, 2015 7:14 PM
  • So this is the other query that the macro button is supposed to run.  KimUPDATE [Timecard Workshop Billing Daily] LEFT JOIN [Workshop update calculator for time to pay time] ON ([Timecard Workshop Billing Daily].BillCd = [Workshop update calculator for time to pay time].StBillcd) AND ([Timecard Workshop Billing Daily].[Worker Name] = [Workshop update calculator for time to pay time].[Worker Name]) AND ([Timecard Workshop Billing Daily].Sttm1 = [Workshop update calculator for time to pay time].Sttm1) AND ([Timecard Workshop Billing Daily].Stp1 = [Workshop update calculator for time to pay time].Stp1) AND ([Timecard Workshop Billing Daily].Workdate = [Workshop update calculator for time to pay time].Workdate) SET [Timecard Workshop Billing Daily].StRate = [Rate], [Timecard Workshop Billing Daily].Wkhr1 = [tottm]
    WHERE ((([Timecard Workshop Billing Daily].[Worker Name])=[Forms]![Timecard Workshop NEW Billing]![Worker Name]) AND (([Timecard Workshop Billing Daily].Workdate)>=[Forms]![FRM_MAIN SHOP]![St Wk] And ([Timecard Workshop Billing Daily].Workdate)<=[Forms]![FRM_MAIN SHOP]![end wk]) AND (([Timecard Workshop Billing Daily].BillCd) Like "W*"));
    Thursday, August 13, 2015 7:22 PM
  • I am very new to this and honestly this stuff was built by a volunteer --- Her skill set isn't perfect but for the most part it gets the work done.  I appreciate your advice as I am learning Access --- I guess I was wrong when I said calculate --- actually what it is doing is posting and updating info from different areas onto a sheet.  When those times are posted and rates brought in from another table it does calculate to give me the amount of $ that we are looking to receive once these times are billed out.

    The thing that I am trying to solve is why a button function would work on one machine and not the next.  I tried to go in and check the settings between two of the machines and can't find where they differ. 

    I posted the SQL before I made the change from the " " to a 0 --- we were trying to avoid having a bunch of unnecessary zeros on the sheet we were trying to view.

     

    Thursday, August 13, 2015 8:03 PM
  • As I said before, either your records and/or your criteria possibilities are different between the two machines.

    After further analysis of your SQL, there is no need to even have the table "Workshop update calculator for time to pay time" in your query.  Remove it.

    Though you should not be acquiring criteria from more than one form source, your SQL should look like the following:

    UPDATE [Timecard Workshop Billing Daily] SET [Timecard Workshop Billing Daily].StRate = Null, [Timecard Workshop Billing Daily].Wkhr1 = Null
    WHERE ((([Timecard Workshop Billing Daily].Wkhr1)>0.1) AND (([Timecard Workshop Billing Daily].BillCd) Not Like "W*") AND (([Timecard Workshop Billing Daily].[Worker Name])=[Forms]![Timecard Workshop NEW Billing]![Worker Name]) AND (([Timecard Workshop Billing Daily].Workdate)>=[Forms]![FRM_MAIN SHOP]![St Wk] And ([Timecard Workshop Billing Daily].Workdate)<=[Forms]![FRM_MAIN SHOP]![end wk]));

    Thursday, August 13, 2015 8:17 PM
  • I will try your query --- I have notice that the main problem I seem to be having is in an "update" query is necessary....  I have a couple of different databases that I use but have this problem whenever I "update"....  As far as having different records that is not the case as we all operate off the same set of tables that are stored on our server.  I will have to check into the criteria possibilities.

    The other table that we reference is what we used to convert from 5:30 to 5.5 etc.

     
    Thursday, August 13, 2015 8:36 PM
  • Your query didn't work either --- I need to figure out why my machine handles the file one way and another machine handles it a different way -- the same exact file and same exact data.....

    Thanks for your time trying to figure this out.

    Thursday, August 13, 2015 9:09 PM
  • To see whether or not your query is finding any records to update, take and copy the following SQL and create a new query with it by selecting Query Design under the Create Tab.  Once there, select View>SQL under the Design Tab, then paste this SQL statement. 

    Next, under the design Tab again, Select View>Datasheet View.  If records are returned, then there is data that meets your criteria, otherwise, there isn't.  You will need to have your two forms open with the appropriate settings, of course.

    SELECT * FROM [Timecard Workshop Billing Daily]
    WHERE ((([Timecard Workshop Billing Daily].Wkhr1)>0.1) AND (([Timecard Workshop Billing Daily].BillCd) Not Like "W*") AND (([Timecard Workshop Billing Daily].[Worker Name])=[Forms]![Timecard Workshop NEW Billing]![Worker Name]) AND (([Timecard Workshop Billing Daily].Workdate)>=[Forms]![FRM_MAIN SHOP]![St Wk] And ([Timecard Workshop Billing Daily].Workdate)<=[Forms]![FRM_MAIN SHOP]![end wk]));

    If you aren't able to see any data returned, try removing your were conditions, one at a time to see if you get results.


    Thursday, August 13, 2015 9:37 PM
  • thanks -
    Thursday, August 13, 2015 10:35 PM
  • Just a quick thought...

    Are  you using mapped drives to the BE Tables or the UNC path. If using mapped drives (not recommended) then this could cause issues. It may not be the query at all just the routing of the Tables.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    • Proposed as answer by David_JunFeng Friday, August 21, 2015 4:25 AM
    Saturday, August 15, 2015 4:58 AM