Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-03-21
4
Medium Priority
?
320 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 1000 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 1000 total points
ID: 39946281
test this sample db
xZipCode.accdb
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

715 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