Forum Discussion

JasonCanady's avatar
JasonCanady
Copper Contributor
Jul 27, 2026

Access Query Problem

Good afternoon. I have been trying to track down a very strange error that my database has thrown for the first time ever... the query performs a complex math equation based on temperature, chlorine residual and pH time to compute the amount of time required to meet disinfection. This month the temerpature of the water has been historically high and the time required historically low. See the screenshot below.  

The math is evaluating the time required is greater than the time achieved (which it is not). The query is pasted below. I have recreated the math in excel and cannot  duplicate this error. It seems exclusive to Access. 

When the formula evaluates as 10.0 the results are “YES” if the formula evaluates as 9.9 the result becomes “NO” but should be yes… Any advice would be greatly appreciated.

Also, this post keeps getting flagged as SPAM... This is attempt number 5.. Fingers crossed.

 

 

access
Query

2 Replies

  • GustavBrock's avatar
    GustavBrock
    MVP
    Jul 28, 2026

    You are comparing strings, not numeric values, and all your field values seem to be text, thus should be converted. Try this:

    SELECT
    CT_ALL.CTDate,
    CT_ALL.[12AM],
    CT_ALL.[4AM],
    CT_ALL.[8AM],
    CT_ALL.NOON,
    CT_ALL.[4PM],
    CT_ALL.[8PM],
    CT_ALL.High,
    CT_ALL.CT1_Time,
    CT_ALL.Peak,
    CT_ALL.CT2_Time,
    CT_ALL.C,
    CT_ALL.T,    
    Val(Format(CDbl(Nz([C], 1)) * CDbl([T]), '#.0')) AS CxT,
    CT_ALL.Sed_Cl2,
    CT_ALL.Sed_T,
    CT_ALL.Temp,
    CT_ALL.pH,
    CT_ALL.[3 Log],
    CT_ALL.Log,
    Val(Format(CDbl(Nz([C2], 0)) * CDbl([T2]), '#.0')) AS CxT2,
    CT_ALL.Sed_Cl2 AS C2,
    CT_ALL.Sed_T AS T2,
    IIf(
        [Temp_1] = '--',
        Null,
        IIf(
            Val([temp]) < 12.5,
            (0.353 * 0.5) * (12.006 + 2.71828183 ^ (2.46 - 0.073 * Val([Temp]) + 0.125 * Val([C]) + 0.389 * Val([pH]))),
            (0.361 * 0.5) * (-2.261 + 2.71828183 ^ (2.69 - 0.065 * Val([Temp]) + 0.111 * Val([C]) + 0.361 * Val([pH])))
        )
    ) AS CT_Required,
    Nz([Temp], '') AS Temp_1,
    CT_ALL.C AS C1,
    IIf([CxT] > [Ct_Required], 'YES', 'NO') AS ClearwellMet,
    IIf([CxT_Sum] > [CT_Required], 'YES', 'NO') AS Met_Overall,
    IIf(
        [temp_1] = '--',
        Null,
        IIf(
            Val([temp]) < 12.5,
            (0.353 * 3) * (12.006 + 2.71828183 ^ (2.46 - 0.073 * Val([Temp]) + 0.125 * Val([C]) + 0.389 * Val([pH]))),
            (0.361 * 3) * (-2.261 + 2.71828183 ^ (2.69 - 0.065 * Val([Temp]) + 0.111 * Val([C]) + 0.361 * Val([pH])))
        )
    ) AS 3Log
FROM CT_ALL
WHERE CT_ALL.CTDate Between [forms].[FiltrationPlant].[StartDate]
    And DateAdd('m', 1, ([Forms].[FiltrationPlant].[StartDate])) - 1;

     

  • Tom_van_Stiphout's avatar
    Tom_van_Stiphout
    Steel Contributor
    Jul 28, 2026

    Interesting problem.
    To debug it, I would rewrite the 3Log expression in VBA, so it is much easier to debug. I'm assuming that is the column you are referring to with "the formula".

    Also, can you paste the text of the SQL statement into a reply?
    Uploading the Excel file will be helpful as well.