We help IT Professionals succeed at work.

FedEx Zone Lookup

ExpressMan1
ExpressMan1 asked
on
813 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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2006

Commented:
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

CERTIFIED EXPERT
Top Expert 2006

Commented:
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
CERTIFIED EXPERT
Top Expert 2006

Commented:

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

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

Author

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
CERTIFIED EXPERT
Top Expert 2006

Commented:
Trying to understand here, what would be the point of entering 6 characters then?
The 2nd post I did should handle that?
ExpressMan1,

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

Sincerely,

Ed
Sample.mdb

Author

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.
CERTIFIED EXPERT
Top Expert 2006

Commented:
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?
ExpressMan1,

I am sorry, my error.

Here it is.

Ed
SampleDB.mdb

Author

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
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

CERTIFIED EXPERT
Top Expert 2006

Commented:
Data in tblPostalCode

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

Author

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.
CERTIFIED EXPERT
Top Expert 2006

Commented:
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



CERTIFIED EXPERT
Top Expert 2006

Commented:
did that work?

Author

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
CERTIFIED EXPERT
Top Expert 2006

Commented:
What error?
CERTIFIED EXPERT
Top Expert 2006
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Works beautifully!   Thanks you very much.   Now I can build on this lesson.
CERTIFIED EXPERT
Top Expert 2006

Commented:
No worries :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.