Solved

How populate 2nd field using 1st three charaters from 1st field

Posted on 2014-03-21
4
317 Views
Last Modified: 2014-03-21
I have a form which contains two fields.  The 1st one is a textbox which the user uses to enter a zip code.  For example, they may enter 61107-1123.

The 2nd field is a text box also.  I want it to populate when the afterupdate event fires from the first field.

But the way it has to work is the 1st field has to do a lookup to a table of UPS zones (see attached file)

So if the user did enter 61107-1123, the lookup uses the first three characters or "611" to enter "005" into the 2nd field.  If you refer to the file I attached you will see how this works.  We want it to always default to the ground "number".

Note: all of the fields in the UPS Zones table are text now.  I assume they should be.

--Steve
UPS-Zone-Chart.xls
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39946058
I don't think the lookup table  should be all text fields.  You are wanting to do a math comparison, e.g. 611 is greater than 600 but less than 639.  I'd create two number columns in the table, ZipMin & ZipMax.  For the zip range in question ZipMin = 600 & ZipMax = 639

Now your AfterUpdate event could be something like

strZipFirst3 = Left("TheFirstFormField", 3)
intZipFirst3 = CInt(strZipFirst3)
varGround = DLookup("Ground", "tblZipCodes", "ZipMin = " & intZipFirst3 & " Or (ZipMin <= " & intZipFirst3 & " AND ZipMax >= " & intZipFirst3 & ")")

OM Gang
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 250 total points
ID: 39946098
Tested and it does work.
OM Gang


Private Sub Text0_AfterUpdate()
On Error GoTo Err_Text0_AfterUpdate

    Dim intZipFirst3 As Integer
    Dim varGround As Variant
   
    intZipFirst3 = CInt(Left(Me.Text0, 3))
   
    varGround = DLookup("Ground", "tblZipCodes", "ZipMin = " & intZipFirst3 & " OR (ZipMin <= " & intZipFirst3 & " AND ZipMax >= " & intZipFirst3 & ")")
   
    Me.Text2 = Nz(varGround, "000")
   

Exit_Text0_AfterUpdate:
    Exit Sub
   
Err_Text0_AfterUpdate:
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_Text0_AfterUpdate
   
End Sub
0
 
LVL 28

Expert Comment

by:omgang
ID: 39946114
This is what I put in for sample data in tblZipCodes

ZipMin      ZipMax      Ground
594                599      008
600                639      005
640                649      006
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 39946281
test this sample db
xZipCode.accdb
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

710 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