Results 1 to 2 of 2
  1. #1
    Voodeux2014 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Location
    Slidell, LA
    Posts
    130

    calculated field in query not functioning when yes/no field is checked...

    here is the SQL for the query in question:


    Code:
    SELECT qryClassTakenCost.ClassID, qryClassTakenCost.ClassName, qryClassTakenCost.CostPerStudent, qryClassTakenCost.CostPerCourse, qrySeatsUsed.CountOfStudentID, [qryClassTakenCost].[CostPerStudent]*[qrySeatsUsed].[CountOfStudentID]+[qryCourseTotal].[TotalClassCost] AS TotalClassCost, qrySeatsUsed.EmployerContribution
    FROM (qryClassTakenCost LEFT JOIN qrySeatsUsed ON qryClassTakenCost.ClassID = qrySeatsUsed.ClassID) LEFT JOIN qryCourseTotal ON qryClassTakenCost.ClassID = qryCourseTotal.ClassID;
    The query's last field is a calculated field as follows:
    Code:
    TotalClassCost: [qryClassTakenCost].[CostPerStudent]*[qrySeatsUsed].[CountOfStudentID]+[qryCourseTotal].[TotalClassCost]
    The weird thing that is going on, i noticed first that a specific class nothing was being returned in the TotalClassCost field. I thought it was strange since it had values in both of the required fields in order to produce a result.

    To dig further I changed a record to be similar to the one that was having an issue.
    * It appears that when the yes/no field (EmployerContribution) is checked, it will not calculate the total.

    * Any class that has a value in the two fields required to calculate in the TotalClassCost AND does not have the EmployerContribution field checked, it calculates the total.

    Why is it that a yes/no field would cause this issue? And more of a focal point, how do I correct it?

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    SELECT qryClassTakenCost.ClassID, qryClassTakenCost.ClassName, qryClassTakenCost.CostPerStudent, qryClassTakenCost.CostPerCourse, qrySeatsUsed.CountOfStudentID, [qryClassTakenCost].[CostPerStudent]*[qrySeatsUsed].[CountOfStudentID]+[qryCourseTotal].[TotalClassCost] AS TotalClassCost, qrySeatsUsed.EmployerContribution
    FROM (qryClassTakenCost LEFT JOIN qrySeatsUsed ON qryClassTakenCost.ClassID = qrySeatsUsed.ClassID) LEFT JOIN qryCourseTotal ON qryClassTakenCost.ClassID = qryCourseTotal.ClassID;

    thought it was strange since it had values in both of the required fields in order to produce a result.

    TotalClassCost: [qryClassTakenCost].[CostPerStudent]*[qrySeatsUsed].[CountOfStudentID]+[qryCourseTotal].[TotalClassCost]
    you say both fields, I count 3 fields

    Also from your naming, you are utilising other queries so I would check those as well - perhaps they are returning a null value. Using left joins will create a null value if there isn't a inking record. Perhaps your employercontribution field is stopping a record being created

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

Similar Threads

  1. Total of checked field
    By AccessThomas in forum Access
    Replies: 3
    Last Post: 05-08-2015, 12:03 PM
  2. Replies: 2
    Last Post: 12-30-2014, 01:32 PM
  3. Replies: 1
    Last Post: 08-25-2014, 01:23 PM
  4. Replies: 4
    Last Post: 03-20-2014, 03:52 PM
  5. Replies: 3
    Last Post: 02-13-2013, 10:15 AM

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