Solved

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

Posted on 2014-03-21
4
316 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
  • 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

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

828 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