If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Returning Only Odd or Even Address Ranges
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201005/1 |
#2
|
|||
|
|||
Returning Only Odd or Even Address Ranges
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. |
#3
|
|||
|
|||
Returning Only Odd or Even Address Ranges
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] |
#4
|
|||
|
|||
Returning Only Odd or Even Address Ranges
Thanks to both of you. It worked great! Now, if only I had posted here
sooner... Marshall Barton wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/201005/1 |
Thread Tools | |
Display Modes | |
|
|