Comparing values within a range

[Guest]

I'm trying to compare a value in an array and assign the value a numeric
score. For some reason, my query is returning no records, but I can use
similar code in other places and it works. See the SQL below. Any help
would be appreciated.

SELECT qry_Rating5.SubProcess, qry_Rating5.SORT,
IIf(qry_Rating5!SORT>=tbl_sort!Low And
qry_Rating5!SORT<=tbl_sort!High,tbl_sort!Sort_Score,0) AS Assigned_Sort
FROM qry_Rating5, tbl_Sort
WHERE (((IIf([qry_Rating5]![SORT]>=[tbl_sort]![Low] And
[qry_Rating5]![SORT]<=[tbl_sort]![High],[tbl_sort]![Sort_Score],0))>0));

Thanks,
Melanie

 

[Guest]

I can't see the data you have in your table, but run the query without the
where condition, to see what value returned with the Assigned_Sort field, if
all the data returned = 0, then that is the reason why no records are
returned when you add the filter

 

[Guest]

Ofer,

I took the where clause out of my query. Six records were returned, one for
each row in tbl_sort, but with the correct values, which is moving in the
right direction. How do I now get it to group by SubProcess (or anything)?

Thanks,
Melanie

I can't see the data you have in your table, but run the query without the
where condition, to see what value returned with the Assigned_Sort field, if
all the data returned = 0, then that is the reason why no records are
returned when you add the filter
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I'm trying to compare a value in an array and assign the value a numeric
score. For some reason, my query is returning no records, but I can use
similar code in other places and it works. See the SQL below. Any help
would be appreciated.

SELECT qry_Rating5.SubProcess, qry_Rating5.SORT,
IIf(qry_Rating5!SORT>=tbl_sort!Low And
qry_Rating5!SORT<=tbl_sort!High,tbl_sort!Sort_Score,0) AS Assigned_Sort
FROM qry_Rating5, tbl_Sort
WHERE (((IIf([qry_Rating5]![SORT]>=[tbl_sort]![Low] And
[qry_Rating5]![SORT]<=[tbl_sort]![High],[tbl_sort]![Sort_Score],0))>0));

Thanks,
Melanie

 

[Guest]

Try this

SELECT qry_Rating5.SubProcess, qry_Rating5.SORT,
IIf(qry_Rating5!SORT>=tbl_sort!Low And
qry_Rating5!SORT<=tbl_sort!High,tbl_sort!Sort_Score,0) AS Assigned_Sort
FROM qry_Rating5, tbl_Sort
WHERE IIf([qry_Rating5]![SORT]>=[tbl_sort]![Low] And
[qry_Rating5]![SORT]<=[tbl_sort]![High],[tbl_sort]![Sort_Score],0)>0
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Ofer,

I took the where clause out of my query. Six records were returned, one for
each row in tbl_sort, but with the correct values, which is moving in the
right direction. How do I now get it to group by SubProcess (or anything)?

Thanks,
Melanie

I can't see the data you have in your table, but run the query without the
where condition, to see what value returned with the Assigned_Sort field, if
all the data returned = 0, then that is the reason why no records are
returned when you add the filter
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I'm trying to compare a value in an array and assign the value a numeric
score. For some reason, my query is returning no records, but I can use
similar code in other places and it works. See the SQL below. Any help
would be appreciated.

SELECT qry_Rating5.SubProcess, qry_Rating5.SORT,
IIf(qry_Rating5!SORT>=tbl_sort!Low And
qry_Rating5!SORT<=tbl_sort!High,tbl_sort!Sort_Score,0) AS Assigned_Sort
FROM qry_Rating5, tbl_Sort
WHERE (((IIf([qry_Rating5]![SORT]>=[tbl_sort]![Low] And
[qry_Rating5]![SORT]<=[tbl_sort]![High],[tbl_sort]![Sort_Score],0))>0));

Thanks,
Melanie

 

[Guest]

Ofer,

Thanks for the quick response. I tried your suggestion, and it didn't seem
to work. What I did notice after playing with the query is that it appears
whatever 'else' string you put in the 'IIf' statement appears as the
Assigned_Rating. So when I used a -1 as the 'else,' I got a -1 as the
Assigned_Rating. If you have any other ideas, I gladly welcome them.

Thanks,
Melanie

Try this

SELECT qry_Rating5.SubProcess, qry_Rating5.SORT,
IIf(qry_Rating5!SORT>=tbl_sort!Low And
qry_Rating5!SORT<=tbl_sort!High,tbl_sort!Sort_Score,0) AS Assigned_Sort
FROM qry_Rating5, tbl_Sort
WHERE IIf([qry_Rating5]![SORT]>=[tbl_sort]![Low] And
[qry_Rating5]![SORT]<=[tbl_sort]![High],[tbl_sort]![Sort_Score],0)>0
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Ofer,

I took the where clause out of my query. Six records were returned, one for
each row in tbl_sort, but with the correct values, which is moving in the
right direction. How do I now get it to group by SubProcess (or anything)?

Thanks,
Melanie

I can't see the data you have in your table, but run the query without the
where condition, to see what value returned with the Assigned_Sort field, if
all the data returned = 0, then that is the reason why no records are
returned when you add the filter
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I'm trying to compare a value in an array and assign the value a numeric
score. For some reason, my query is returning no records, but I can use
similar code in other places and it works. See the SQL below. Any help
would be appreciated.

SELECT qry_Rating5.SubProcess, qry_Rating5.SORT,
IIf(qry_Rating5!SORT>=tbl_sort!Low And
qry_Rating5!SORT<=tbl_sort!High,tbl_sort!Sort_Score,0) AS Assigned_Sort
FROM qry_Rating5, tbl_Sort
WHERE (((IIf([qry_Rating5]![SORT]>=[tbl_sort]![Low] And
[qry_Rating5]![SORT]<=[tbl_sort]![High],[tbl_sort]![Sort_Score],0))>0));

Thanks,
Melanie