subquerying in WHERE/Joining 3 tables, 2 for records and one for number, returns no result/fails - MSAccess

Question

My query does return any records. Depending on how I write it, it returns no records or all records, although I don't have the code that just returned everything.

I need to pull data from two sources with actual records, and a third table which has project-wide information not specific to any records. I need to filter out records which are greater than the Miles_Budgeted variable.

This returns no records, although if I replaces param.Miles_Budgeted with a numeric value e.g. 1000, it filters to the desired records.

SELECT
    a.sort_id,
    a.l1l2,
    a.rtot_pct_oftot_miles,
    b.sumofeq,
    b.c_per_mile,
    b.sumofo_total,
    a.cpminrmd,
    a.RunTotMiles,
    param.Miles_Budgeted

FROM
    (SELECT (p.Budget_Cost_Targ / p.Project_Cost_Per_Mi) AS Miles_Budgeted FROM Tbl_Project_Parameters as p) AS param,
    qry_par_l2_by_cpermi AS a 
    INNER JOIN
        qry_l2 AS b 
        ON a.l1l2 = b.l1l2 
WHERE
 ((a.RunTotMiles) <= 
        (Param.Miles_Budgeted
                   )
)

ORDER BY
    a.sort_id;

This variant of the query does not run (Syntax Error in FROM Clause)

SELECT     
           a.sort_id, 
           a.l1l2, 
           a.rtot_pct_oftot_miles, 
           b.sumofeq, 
           b.c_per_mile, 
           b.sumofo_total, 
           a.cpminrmd, 
           a.runtotmiles, 
           param.miles_budgeted 
FROM       ( 
           ( 
                  SELECT (p.budget_cost_targ / p.project_cost_per_mi) AS miles_budgeted 
                  FROM   tbl_project_parameters                       AS p ) AS param 
INNER JOIN qry_par_l2_by_cpermi AS a ) 
INNER JOIN qry_l2 AS b 
ON         a.l1l2 = b.l1l2 
AND        ( 
                      a.runtotmiles) <= ( param.miles_budgeted ) 
ORDER BY   a.sort_id;

This also returns no records:

SELECT
    a.sort_id,
    a.l1l2,
    a.rtot_pct_oftot_miles,
    b.sumofeq,
    b.c_per_mile,
    b.sumofo_total,
    a.RunTotMiles,
    a.cpminrmd 
FROM
    qry_par_l2_by_cmipermi AS a 
    INNER JOIN
        qry_l2 AS b 
        ON a.l1l2 = b.l1l2 
WHERE
    (
((a.RunTotMiles) <= 
        (
            SELECT
(p.Budget_Cost_Targ / p.Project_Cost_Per_Mi) AS Budgeted_Miles 
            FROM
                Tbl_Project_Parameters AS p 
        )
) 
    )
ORDER BY
    a.sort_id;

Again, if

SELECT
(p.Budget_Cost_Targ / p.Project_Cost_Per_Mi) AS Budgeted_Miles 
            FROM
                Tbl_Project_Parameters AS p 

is replaces with a numeric value, the query returns the correct records. I have tried surrounding the subq or field with val() or Format(,"Standard") but this does not see to fix the issue; a separate query with just the relevant code returns the correct Budgeted_Miles as 1000 as it should.

Any thoughts appreciated.

Answer 1

Have you tried limiting that subquery to return only one record? I know some versions of SQL don't like when you try comparing the results of a SELECT to a single value.

I believe the syntax for MS Access would use "TOP":

SELECT TOP 1 
(p.Budget_Cost_Targ / p.Project_Cost_Per_Mi) AS Budgeted_Miles 
FROM
Tbl_Project_Parameters AS p