Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-21
4
Medium Priority
?
321 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

926 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