Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

FedEx Zone Lookup

I have a FedEx Canada Zone chart with Postal Codes prefix's such as
Postal Code            Zone
B1B-B1C                  DY
B1E-B1H                   DX
 I need to enter a Postal code ex: B1F2G1 in a form and return the right zone. How do I do that?
Thanks
0
ExpressMan1
Asked:
ExpressMan1
  • 12
  • 6
  • 3
1 Solution
 
rockiroadsCommented:
so when u say a chart, do u mean a table?

so say you got a table called tblFedEx and it has 2 columns, one called Postal Code, other called Zone

User enters a Postal Code on a form
say you had two fields txtPostalCode and txtZone

try this


Dim sZone
Dim sPostalCode As String

'If correct length
If Len(Me.txtPostalCode) = 6 Then
    'Add dash
    sPostalCode = left$(Me.txtPostalCode, 3) & "-" & Mid$(Me.txtPostalCode, 4)
    'Assign Zone
    Me.txtZone = DLookup("Zone", "tblFexEx", "[Postal Code] = '" & sPostalCode & "'")
Else
    MsgBox "Invalid Length"
End If

Open in new window

0
 
rockiroadsCommented:
This is a modified version to handle inputs with a dash in with very basic validation

Code will go on the AfterUpdate event of the postal code entry or on the click event of a search button, however you designed it
0
 
rockiroadsCommented:

Dim sPostalCode As String

'If len 6
If Len(Me.txtPostalCode) = 6 Then
    'Add dash
    sPostalCode = left$(Me.txtPostalCode, 3) & "-" & Mid$(Me.txtPostalCode, 4)

'If len 7 with dash in
ElseIf Len(Me.txtPostalCode) = 7 And Mid$(Me.txtPostalCode, 4) = "-" Then
    sPostalCode = Me.txtPostalCode

'Else gotta be dodgy
Else
    MsgBox "Invalid Length"
    sPostalCode = ""
End If

'If postcode entered
If sPostalCode <> "" Then
    'Assign Zone
    Me.txtZone = DLookup("Zone", "tblFexEx", "[Postal Code] = '" & sPostalCode & "'")
End If

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MINDSUPERBCommented:
ExpressMan1,

I attached a sample db for you to look at. This might be the one you want to happen.
If you want to look the Zone value without "-" (B1F2G1), use the code suggested by rockiroad on the AfterUpdate event of the  PostalCode textbox in Form1. Just do change the field names.

Sincerely,

Ed
SampleDB.mdb
0
 
ExpressMan1Author Commented:
Thanks both so far. My mistake, I need to find the zone for the first 3 characters of a entered postal code.  In the table the Postal Code format is with a dash but I can change that.   The Postal Code in the table represents a range of the first 3 characters of the postal codes.   Ex: Range B1E-B1H, so if the user enters B1F1G3 the zone would be DX since B1F is between the range.
I can change the table design to have 2 columns for the range if that would make it easier, one for each of the 3 character prefix's and no dash. Ex:
StartRange      EndRange         Zone
B1E                     B1H                  DX
J1G                     J9Z                  DF
0
 
rockiroadsCommented:
Trying to understand here, what would be the point of entering 6 characters then?
The 2nd post I did should handle that?
0
 
MINDSUPERBCommented:
ExpressMan1,

I did a minor modification on the sample database. You may look at it in the attachment.

Sincerely,

Ed
Sample.mdb
0
 
ExpressMan1Author Commented:
MINDSUPERB I think you may have sent me a different sample mdb than what you intended.

rockiroads  I could ask the user to enter just the first 3 characters of the postal code. I will see if it works.
0
 
rockiroadsCommented:
Ok, here is a simple scenario

if u had a postal code of AAABBB zone XX
and postal code AAADDD zone YY

if user enters AAA which zone would you get back?

I thought you wanted to match postal code on all 6 characters?

so if u entered AAADDD you get YY back

what if u entered AAACCC, I assume no zone found?
0
 
MINDSUPERBCommented:
ExpressMan1,

I am sorry, my error.

Here it is.

Ed
SampleDB.mdb
0
 
ExpressMan1Author Commented:
rockiroads.  Your code works but it's not quite what I need.  A user would enter a postal code with a length of 6 or 7 depending on if they enterred a space in the middle of the postal code or not.

Ex: B1F1Y2 or
B1F 1Y2.

If the postal code entered is B1F 1Y2 the zone returned would be DX, as B1F is between the range of
B1E and B1H


StartRange      EndRange         Zone
B1E                     B1H                  DX
J1G                     J9Z                   DF
0
 
rockiroadsCommented:
Ok, this is what I did

based on a table tblPostalCode
Postal Code  Text
Zone  Text




On a form with one textbox txtPostalCode and one button cmdFind

if 3 chars entered do search on first 3 then return first match
if 6/7 chars entered then search on all 6

Private Sub cmdFindZone_Click()

    Dim sZone As String
    Dim sWhere As String
    Dim sPC1 As String
    Dim sPC2 As String
    Dim sPCode As String
    
    'Remove any leading/trailing spaces
    sPCode = Trim$(Me.txtPostalCode)
    
    'Min 3 characters required - throw error if not
    If Len(sPCode) < 3 Then
        MsgBox "Invalid Postal Code"
        Exit Sub
    End If
        
    'Set first part of postcode
    sPC1 = Left$(sPCode, 3)
    
    'If more than 3 chars entered, set 2nd part of postal code
    If Len(sPCode) > 3 Then
        'If - or space entered
        If Mid$(sPCode, 4, 1) = "-" Or Mid$(sPCode, 4, 1) = " " Then
            sPC2 = Mid$(sPCode, 5)
        Else
            sPC2 = Mid$(sPCode, 4)
        End If
    End If
    
    'Build the critera
    sWhere = "[Postal Code] LIKE '" & sPC1 & "-"
    If Len(sPC2) < 4 Then
        sWhere = sWhere & sPC2 & "*"
    End If
    sWhere = sWhere & "'"
    
    'Find record
    sZone = Nz(DLookup("Zone", "tblPostalCode", sWhere))
    
    MsgBox "PC1 = " & sPC1 & vbCrLf & "PC2 = " & sPC2 & "Zone = " & sZone
End Sub

Open in new window

0
 
rockiroadsCommented:
Data in tblPostalCode

Postal Code      Zone
B1B-B1C      DY
B1E-B1H      DX
J1G-J9Z      DF
0
 
ExpressMan1Author Commented:
rockiroads.
It works if I enter a Postal Code such as "B1E"  or "B1B",  but if I enter "B1F" which is between the B1E-B1H
range then no result is returned.  I need a way to check it the Postal Code entered is "Between" or the same as the range.
0
 
rockiroadsCommented:
Righty ho
after the first dlookup add the following. Assumption is made that search done on first 3 characters only


    'Check for range if 3 characters entered
    If sZone = "" Then
        sWhere = "'" & sPC1 & "' >= [Postal Code]"
        sZone = Nz(DLast("Zone", "tblPostalCode", sWhere))
    End If



0
 
rockiroadsCommented:
did that work?
0
 
ExpressMan1Author Commented:
Do you mean place the code here?  Getting an error like this.
 'Find record
    sZone = Nz(DLookup("Zone", "tblPostalCode", sWhere))
   
      If sZone = "" Then
        sWhere = "'" & sPC1 & "' >= [Postal Code]"
        sZone = Nz(DLast("Zone", "tblPostalCode", sWhere))
    End If
0
 
rockiroadsCommented:
What error?
0
 
rockiroadsCommented:

Private Sub cmdFindZone_Click()

    Dim sZone As String
    Dim sWhere As String
    Dim sPC1 As String
    Dim sPC2 As String
    Dim sPCode As String
    
    'Remove any leading/trailing spaces
    sPCode = Trim$(Me.txtPostalCode)
    
    'Min 3 characters required - throw error if not
    If Len(sPCode) < 3 Then
        MsgBox "Invalid Postal Code"
        Exit Sub
    End If
        
    'Set first part of postcode
    sPC1 = Left$(sPCode, 3)
    
    'If more than 3 chars entered, set 2nd part of postal code
    If Len(sPCode) > 3 Then
        'If - or space entered
        If Mid$(sPCode, 4, 1) = "-" Or Mid$(sPCode, 4, 1) = " " Then
            sPC2 = Mid$(sPCode, 5)
        Else
            sPC2 = Mid$(sPCode, 4)
        End If
    End If
    
    'Build the critera
    sWhere = "[Postal Code] LIKE '" & sPC1 & "-"
    If Len(sPC2) < 4 Then
        sWhere = sWhere & sPC2 & "*"
    End If
    sWhere = sWhere & "'"
    
    'Find record
    sZone = Nz(DLookup("Zone", "tblPostalCode", sWhere))
    
    'Check for range if 3 characters entered
    If sZone = "" Then
        sWhere = "'" & sPC1 & "' >= [Postal Code]"
        sZone = Nz(DLast("Zone", "tblPostalCode", sWhere))
    End If
    
    MsgBox "PC1 = " & sPC1 & vbCrLf & "PC2 = " & sPC2 & "Zone = " & sZone
    
    'IF ZONE FOUND IT WILL BE HELD IN sZone
End Sub

Open in new window

0
 
ExpressMan1Author Commented:
Works beautifully!   Thanks you very much.   Now I can build on this lesson.
0
 
rockiroadsCommented:
No worries :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now