Using a parameter against Expression in Query problem 1

[jjlogan]

I've searched google and this forum without answer.
Problem: I want to create a query to feed report in which one of the three selection criteria is that Absolute Value of an amount field (UCACurrent) is >= a parameter value as provided by report user (parameter is [Enter lowest absolute value of UCA]). When I run the report (or query) its like this absolute value criteria is ignored!
I already tried creating a sub-query that creates the absolute value "AS AbsUCA" then in outer query I use 'WHERE AbsUCA >= [Enter lowest absolute value UCA].
Any suggestions? My full query SQL is as follows:
SELECT Job, Sjob,AbsUCA, UCACurrent
FROM (SELECT JobDetail2007.Job As Job, Sjob, Org, Abs([UCACurrent]) AS AbsUCA, UCACurrent
FROM JobDetail2007 INNER JOIN JobSubDetail2007 ON JobDetail2007.Job = JobSubDetail2007.Job) WHERE AbsUCA>=[enter lowest AbsUCA];
Thanks. Jeff

 

[PHV]

What about this ?
SELECT D.Job, Sjob, Org, Abs(UCACurrent) AS AbsUCA, UCACurrent
FROM JobDetail2007 D INNER JOIN JobSubDetail2007 S ON D.Job = S.Job
WHERE Abs(UCACurrent)>=[enter lowest AbsUCA];


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jjlogan]

PH,
I tried the suggestion, but I'm still getting amounts far less then my parameter input; e.g. I respond to parameter with 2500 and I get records where absolute value is 8, 119, and many more (1029 records). But if I remove the paramter and this criteria, I get 1705 records; so it seems the parameter used in Where clause is affecting some record selection.
Jeff

 

[PHV]

What is the data type of UCACurrent ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jjlogan]

The data type is Double with format Standard, 0 decimals positions.
Jeff

 

[dhookom]

You mentioned [blue]three selection criteria[/blue] but provide a SQL with only one. I'm not sure we can provide full assistance without seeing all of the SQL involved.

[blue]format Standard[/blue] should have no effect since a number is a number unless you convert it to a string with an expression in your SQL.

Duane
Hook'D on Access
MS Access MVP

 

[jjlogan]

Here is the full sql query with all three criteria:
SELECT JobDetail2007.FICSYr, JobDetail2007.Org, JobDetail2007.Job, JobDetail2007.JobDesc, JobDetail2007.JobScope, JobDetail2007.JobCustomers, JobSubDetail2007.Sjob, JobSubDetail2007.SjobReviewed, JobSubDetail2007.SjobDesc, JobSubDetail2007.SjobScope, JobSubDetail2007.SjobCustomers, Abs([UCACurrent]) AS AbsUCA, JobSubDetail2007.SjobReviewedBy, JobSubDetail2007.SjobDateReviewed, Depts.DeptName, Contacts.ContactNew, IIf(IsNull([EX]),[GroupPool],[GroupPool] & " / " & [EX]) AS GroupEX
FROM ((JobDetail2007 INNER JOIN JobSubDetail2007 ON JobDetail2007.Job = JobSubDetail2007.Job) LEFT JOIN Depts ON JobDetail2007.Org = Depts.Dept) LEFT JOIN Contacts ON JobDetail2007.ContactId = Contacts.ContactId
WHERE (((JobDetail2007.Org) Like "" & [Enter Dept code xxx or leading character xx] & "*") AND ((JobSubDetail2007.SjobReviewed)=True) AND ((Abs([UCACurrent]))>=[Enter lowest UCA - Use big Negative for All]));
Thanks for following up.
Jeff

 

[dhookom]

I don't believe in ever using parameter prompts in queries. However this could be an issue with data types. I would wrap your parameter in Val() like:

SELECT JobDetail2007.FICSYr, JobDetail2007.Org, JobDetail2007.Job, JobDetail2007.JobDesc, JobDetail2007.JobScope, JobDetail2007.JobCustomers, JobSubDetail2007.Sjob, JobSubDetail2007.SjobReviewed, JobSubDetail2007.SjobDesc, JobSubDetail2007.SjobScope, JobSubDetail2007.SjobCustomers, Abs([UCACurrent]) AS AbsUCA, JobSubDetail2007.SjobReviewedBy, JobSubDetail2007.SjobDateReviewed, Depts.DeptName, Contacts.ContactNew, IIf(IsNull([EX]),[GroupPool],[GroupPool] & " / " & [EX]) AS GroupEX
FROM ((JobDetail2007 INNER JOIN JobSubDetail2007 ON JobDetail2007.Job = JobSubDetail2007.Job) LEFT JOIN Depts ON JobDetail2007.Org = Depts.Dept) LEFT JOIN Contacts ON JobDetail2007.ContactId = Contacts.ContactId
WHERE (((JobDetail2007.Org) Like "" & [Enter Dept code xxx or leading character xx] & "*") AND ((JobSubDetail2007.SjobReviewed)=True) AND ((Abs([UCACurrent]))>=Val([Enter lowest UCA - Use big Negative for All])));

Duane
Hook'D on Access
MS Access MVP

 

[jjlogan]

dhookom,
Your suggesion solved the problem. I used Val([parameter wording]) and eliminated all records less than my parameter value (which is what I wanted). Thanks much. And thanks to PH for inputs. Good to hear from the minds of others.
Jeff