Solved

FedEx Zone Lookup

Posted on 2010-09-16
21
637 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now