arcturus19 via AccessMonster.com wrote:

I am new to Access Monster. I have been searching for days to find the
answer to my query question.

I have an Access database of street address ranges for the entire city used
for planning meter reading routes used by my company. (There are fields for
the beginning and ending numbers in the range.) I've built a simple search
form with text fields for Address#, Street Name and Street type. This works
fine using the Between operator. I receive the correct steet address range
and route number in my subform after entering a single address in the main
form.

I occasionally receive two records because there is sometimes a route number
for the odd numbered side of the street, while the other is for the even
numbered side.

Since there is only one route number for any address, I want the user's
search to simply return one record (either odd or even range) based on the
number entered in the address number text box of the main form. (An odd
address number should only return the odd address range, and so on.)

I believe I need to write some variation of an IIF statement with the Mod 2
function in it somewhere, but I never get the syntax right.


Right. Add another condition to the query's Where clause
WHERE (whatever you have now) AND [Address#] Mod 2 =
Forms!searchform.[txtAddress#] Mod 2

--
Marsh
MVP [MS Access]