Returning Only Odd or Even Address Ranges

[arcturus19 via AccessMonster.com]

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.

Thanks in advance.

 

[ghetto_banjo]

you could put an "invisible" field on your form, that stores the Mod 2
for the starting Address# entered:

= [StartingAddress] Mod 2



And then in your query, add a field:

requiredMod: [Address#] Mod 2


With Criteria referencing that new text box on the form.

=Forms![FormName].txtMod



You might want to have a quick check to make sure that both the Start
and End Address #s entered are both even or both odd.

 

[Marshall Barton]

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

 

[arcturus19 via AccessMonster.com]

Thanks to both of you. It worked great! Now, if only I had posted here
sooner...

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

[quoted text clipped - 18 lines]

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