Select Case help

[reel knotty]

Ok guys here i am again with probably a simple problem...

I have a form "manafest" and 2 subforms with detailed load information one pick up and one delivery. within the subforms the pick up and deliver locations are designated by zip code. In a seperate table I have latitude and Longitude for every zip code in the US. I then break down the zip code into zones based on Lat and Lon. here is where i am having a problem... I created a select case sub in VBA as follows:
Sub Case_select(zone)
Select Case [Lat] & [Lon]
Case [Lat] > (41.97) < (45.08) And [Lon] > (75.83) < (79.72)
zone = 1
Case [Lat] > (41.97) < (45.08) And [Lon] > (73.22) < (75.82)
zone = 2
Case [Lat] > (42.67) < (42.98) And [Lon] > (70.72) < (73.22)
zone = 3
Case [Lat] > (43.05) < (47.34) And [Lon] > (66.89) < (71.12)
zone = 4
Case [Lat] > (41.01) < (42.67) And [Lon] > (69.8) < (72.5)
zone = 5
Case [Lat] > (41.01) < (42.67) And [Lon] > (72.5) < (73.22)
zone = 6
Case [Lat] > (40.51) < (40.94) And [Lon] > (73.23) < (73.73)
zone = 7
Case [Lat] > (40.94) < (41.96) And [Lon] > (73.52) < (75.32)
zone = 8
Case [Lat] > (39.75) < (40.94) And [Lon] > (73.73) < (75.31)
zone = 8
Case [Lat] > (39.94) < (41.96) And [Lon] > (75.32) < (77.32)
zone = 9
Case [Lat] > (39.75) < (41.96) And [Lon] > (77.32) < (80.49)
zone = 10
Case [Lat] > (36.95) < (39.75) And [Lon] > (74.93) < (76.8)
zone = 11
Case [Lat] > (38.36) < (39.94) And [Lon] > (76.8) < (82.67)
zone = 12
Case [Lat] > (38.36) < (41.96) And [Lon] > (80.49) < (82.67)
zone = 13
Case [Lat] > (38.36) < (41.96) And [Lon] > (82.67) < (84.79)
zone = 14
Case [Lat] > (38.36) < (41.96) And [Lon] > (84.79) < (87.45)
zone = 15
Case Else
zone = 0
End Select
End Sub

After updating the zip code I have a pop up form with the zip from the master form and the city, state, lat and lon from my zip table. When I ask it to perform the sub it returns #name?

I have the following entered in the control source for the zone field. Case_select(zone)

Thanks in advance,
Nate

 

[WayneRyan]

Nate,

Rather than use code, I'd put all that data into a table:

tblZIPs
=====
ZipCode - pk
LatMin
LatMax
LongMin
LongMax


Then you can use a query or the DLookup function to retrieve
the ZIP code.

btw,
Your sub is passing in zone as an argument and then assigning
a value to it.

Wayne

 

[reel knotty]

Wayne,
I do have a table with the zip, lat and lon. I am pulling that data and from there I want to attach the zone based on the lat and lon rectangulation. I am quite the rookie at code. I was going to use iif statements in a query but it was too complex.

 

[WayneRyan]

Nate,

Use the Between operator in the Criteria section of your query.

Lat between (38.36) and (41.96) And
[Lon] between (82.67) and (84.79)

The row returned will have your Zip Code.

Wayne