Find between values in 2 separate fields?

[Dack]

I'm kind of a rookie. I have a FM table with the following fields. Street - Address Range To - Address Range From - School.

I want people to be able to enter the street name and number and locate the school that address should attend. I don't know how to construct the find to search between values in the 2 Address range fields. Thanks.

Dack

[Søren Dyhr]

Ah! I found you forum message... Take a look at the attatched upload....

--sd

Dack.zip

[Ender]

If your school ditrict is in the US, you might also consider tapping into the census data. I think School District was one of the thinks they track in their Tiger/Line files. If your interested, the files are located here:

http://www.census.gov/geo/www/tiger/tiger2k/tgr2000.html

It may not be easy to extract the data you need but it could save a lot of data entry time, depending on how big your school district is. I showed how to use parts of this census data to get the latitude/longitude for an address here:

http://www.fmforums.com/threads/showflat.php?Cat=0&Board=articles&Number=116935

[Rob 7 Collins]

I'm kind of a rookie. I have a FM table with the following fields. Street - Address Range To - Address Range From - School.

I want people to be able to enter the street name and number and locate the school that address should attend. I don't know how to construct the find to search between values in the 2 Address range fields. Thanks.

Wow, so you're going to build a table with all the streets and per each street all the address ranges? that's a tough job.

i'm volunteering at a school system too, so I'll try to give the best advise I can...

1 you have two number fields, one is "greater than" and the other is "less than". Put some text so the user knows to type the street number into the "greater than" field.

2 Put a search button on the layout.

3 this layout has a specific table name, I'm assuming it is called "schoolsByAddress". Create a script "search_school_by_address":

#This script uses the script parameter to set fields and perform a search.

#Another method would be to use Insert From Last Visited script step.

#

#The goal is to find the appropriate school for a family/child, based on address.

##

##

  If [ IsEmpty ( schoolsByAddress::greater_than ) ]

#First be sure we have something worth searching for.

    Show Custom Dialog [ "Malformed Search" ; "The street number is required." ]

  Else If [ not IsEmpty ( Get ( ScriptParameter ) ) ]

#Last, set up the search properly and perform the search.

    Set Field [ Select ; SchoolsByAddress::greater_than ; "<" & Get ( ScriptParameter ) ]

    Set Field [ Select ; SchoolsByAddress::less_than ; ">" & Get ( ScriptParameter ) ]

#Note that the comparison operators are reversed.

    Perform Search [no dialog]

  Else

#to set up the search properly the script has to have the text in the search 

#field in the script parameter ... actually that's not true but that's how 

#I do it.

    Enter Find Mode []

    Perform Script [ "search_schools_by_address" ; GetAsText ( schoolsByAddress::greater_than ) ]

  End If

4 Make the button you created run the script you just wrote