Solved

FedEx Zone Lookup

Posted on 2010-09-16
21
641 Views
Last Modified: 2013-11-28
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
Comment
Question by:ExpressMan1
  • 12
  • 6
  • 3
21 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33698140
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33698151
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33698154

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33698962
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
 

Author Comment

by:ExpressMan1
ID: 33702332
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33702555
Trying to understand here, what would be the point of entering 6 characters then?
The 2nd post I did should handle that?
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33703853
ExpressMan1,

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

Sincerely,

Ed
Sample.mdb
0
 

Author Comment

by:ExpressMan1
ID: 33703954
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33704011
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
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33704112
ExpressMan1,

I am sorry, my error.

Here it is.

Ed
SampleDB.mdb
0
 

Author Comment

by:ExpressMan1
ID: 33704351
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33704810
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33704816
Data in tblPostalCode

Postal Code      Zone
B1B-B1C      DY
B1E-B1H      DX
J1G-J9Z      DF
0
 

Author Comment

by:ExpressMan1
ID: 33705403
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33705910
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33706051
did that work?
0
 

Author Comment

by:ExpressMan1
ID: 33706284
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33706339
What error?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33706347

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
 

Author Closing Comment

by:ExpressMan1
ID: 33706516
Works beautifully!   Thanks you very much.   Now I can build on this lesson.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33706542
No worries :)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question