I'm working on a report when enter a zipcode it will show a specifice area. But there are some zipcode with 10 numbers in the zipcode field. How do I make it that when the user just enter the first 3 number of the zipcode it show most of the zipcode in that area which includes the one with 10 numbers? Please help!. Thanks.
Zipcode problem (1 Viewer)
- Thread starter mtp
- Start date
Fornatian
Dim Person
- Local time
- Today, 14:25
- Joined
- Sep 1, 2000
- Messages
- 1,396
Is your report based on a table or a query.
To perform this you need to base it on a query.
Let's assume your reports zip code field is called 'zipcode'
1.Add all the fields you want in the report into the query grid.
2.Add an expression in a new column like this
First3OfZip: Left([zipcode],3)
'this will provide a new column with only the first three letters in it.
'test it now to see it works OK.
3.Add criteria to First3OfZip like this:
[Enter First Three Letters]
4.Run the query and enter a known zip codes first three letters.
5.Build a new report based on the query.
6.Sit back and bask in the glory
Instead of inputting the criteria in a pop up criteria box you might like to consider pointing the query at a textbox on a form like this:
Forms!MyFormName!MyControlName
That way, when the button is clicked to run the query you can also test that they have entered three letters like:
If Len(Me.MyControlName)<>3 then
Msgbox "Please enter three characters in MyControlName to run the report"
Else
Docmd.OpenReport "reportName,acPreview
End If
You could additionally prevent the user from entering more than three characters by limiting the text box's length.
Another idea might be to use a like operator.
Instead of having an extra column in the query use this criteria under the zip code:
Like [Enter Zip Criteria] & "*"
This will allow the user to enter as many letters as desired, not necessarily three.
Hope that halfway sorts the problem
Ian
To perform this you need to base it on a query.
Let's assume your reports zip code field is called 'zipcode'
1.Add all the fields you want in the report into the query grid.
2.Add an expression in a new column like this
First3OfZip: Left([zipcode],3)
'this will provide a new column with only the first three letters in it.
'test it now to see it works OK.
3.Add criteria to First3OfZip like this:
[Enter First Three Letters]
4.Run the query and enter a known zip codes first three letters.
5.Build a new report based on the query.
6.Sit back and bask in the glory
Instead of inputting the criteria in a pop up criteria box you might like to consider pointing the query at a textbox on a form like this:
Forms!MyFormName!MyControlName
That way, when the button is clicked to run the query you can also test that they have entered three letters like:
If Len(Me.MyControlName)<>3 then
Msgbox "Please enter three characters in MyControlName to run the report"
Else
Docmd.OpenReport "reportName,acPreview
End If
You could additionally prevent the user from entering more than three characters by limiting the text box's length.
Another idea might be to use a like operator.
Instead of having an extra column in the query use this criteria under the zip code:
Like [Enter Zip Criteria] & "*"
This will allow the user to enter as many letters as desired, not necessarily three.
Hope that halfway sorts the problem
Ian
