A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Returning Only Odd or Even Address Ranges



 
 
Thread Tools Display Modes
  #1  
Old May 6th, 2010, 06:07 AM posted to microsoft.public.access.reports
arcturus19 via AccessMonster.com
external usenet poster
 
Posts: 2
Default 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  
Old May 6th, 2010, 01:52 PM posted to microsoft.public.access.reports
ghetto_banjo
external usenet poster
 
Posts: 325
Default 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  
Old May 6th, 2010, 01:58 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old May 7th, 2010, 03:43 AM posted to microsoft.public.access.reports
arcturus19 via AccessMonster.com
external usenet poster
 
Posts: 2
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.