Access Query Expression "Too Complex" when trying to filter a calculated field

Question

As the title states, I am having an issue with a query giving me an error stating that my expression is too complex when trying to filter calculated fields. I have searched around and have not found anything applicable to my specific use case and was hoping someone here could help me out.

Parameters:

“Jobsite Lat” – Data from Table

“Jobsite Long” – Data from Table

“SearchLat” – TempVar

“SearchLong” – TempVar

“SearchRad” – TempVar

“Distance” – Calculated Value

“projwcoord” – Source Table

Calculated Expression: (Put into “Field” field in query)

Distance: Sqr(((([Jobsite Lat]-[TempVars]![SearchLat])/0.011451)*(([Jobsite Lat]-[TempVars]![SearchLat])/0.011451))+((([Jobsite Long]-[TempVars]![SearchLong])/0.011451)*(([Jobsite Long]-[TempVars]![SearchLong])/0.011451)))

Desired Criteria (for Distance): Filter out Distance values above SearchRad

Syntax: “< [TempVars]![SearchRad]”

My issue is that I can run the query just fine when there is no criteria for “Distance”, however, as soon as I include the criteria, it gives me an error saying that the expression is too complex.

Additionally, I have tried to use a check variable to spit out “1” if the Distance value meets the criteria, and “0” if it does not, which works fine with no criteria, but again, if I try to use criteria to filter out any records with a check value of “0” (Syntax “Not Like “0””) I get a message asking me to declare the value of “Distance”

I am pretty stumped on this and would appreciate any help. I have included my SQL code below if that helps. I have not changed it as I am inexperienced with SQL and have used the design view up to this point. However, if it is required to get my desired outcome then I will be happy to learn. Thank you!

Using Check Value:

SELECT projwcoord.*, Sqr(((([Jobsite Lat]-[TempVars]![SearchLat])/0.011451)*(([Jobsite Lat]-[TempVars]![SearchLat])/0.011451))+((([Jobsite Long]-[TempVars]![SearchLong])/0.011451)*(([Jobsite Long]-[TempVars]![SearchLong])/0.011451))) AS Distance, IIf([Distance]<=[TempVars]![SearchRad],"1","0") AS [Check]

FROM projwcoord;

WHERE (((IIf([Distance]<=[TempVars]![SearchRad],"1","0")) Not Like "0"));

Filtering by Distance:

SELECT projwcoord.*, Sqr(((([Jobsite Lat]-[TempVars]![SearchLat])/0.011451)*(([Jobsite Lat]-[TempVars]![SearchLat])/0.011451))+((([Jobsite Long]-[TempVars]![SearchLong])/0.011451)*(([Jobsite Long]-[TempVars]![SearchLong])/0.011451))) AS Distance

FROM projwcoord

WHERE (((Sqr(((([Jobsite Lat]-[TempVars]![SearchLat])/0.011451)*(([Jobsite Lat]-[TempVars]![SearchLat])/0.011451))+((([Jobsite Long]-[TempVars]![SearchLong])/0.011451)*(([Jobsite Long]-[TempVars]![SearchLong])/0.011451))))<[TempVars]![SearchRad]));

Answer 1

Are you aware that you can use the ^ character as the exponent operator? e.g.

x = 3

y = 4

? Sqr(x ^ 2 + y ^ 2)

 5

So you should be able to simplify the expression when returning a number squared.

Note that the ^ character must be preceded by a space.  For neatness I've also followed it by a space, though that isn't actually necessary.

Answer 2

The left-hand-side of your expression is a number (returned by Sqr function), so it should be:

< 50

Access/VBA will do the right thing and convert "50" to 50, but you don't need this overhead.

Same with your Check Value expression. Simplify it to:

WHERE (((IIf([Distance]<=[TempVars]![SearchRad],1,0)) = 1));

Answer 3

Thank you for the response! I will see what I can about a dummy database to post, but will have to get approval from my employer before I do so. what really throws me for a loop is the majority of the calculations work fine. when not filtering the distance data it returns an accurate calculated value with no issues for all records, but as soon as I try to add any criteria, even as simple as "<"50"" it throws an error.

Answer 4

Can you create a new blank database, and copy in only the table(s) and query(s) that reproduce this issue? Then post it in a public place like a free OneDrive account.
At first glance, this query does not seem very complex, so I am surprised Access has an issue with it.

I'm assuming the tempvars have suitable values. You may want to tell us what test values to use with the repro database.