Solved

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

Posted on 2014-03-21
4
311 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 119

Accepted Solution

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

746 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

13 Experts available now in Live!

Get 1:1 Help Now