Compar/find value

[Irmgard Wesselb]

Dear Access Gurus,
This is my very first post, but I have searched the Access Forum
for a few months and found many valuable tips and solutions.

Perhaps someone can help me with the following problem/challenge:

In a MEMBERSHIP database, I have a table named POSTCODES, which has the following fields:
ID (AutoNumber....linked to the Members table, One-to-Many)
AREA Description (Text)
FROM (Number, 4 digits for Australian Postcodes)
TO (Number, 4 digits for Australian Postcodes)
POSTGROUP (Number, 3 digits for the Postcode Group Number)

What I want to achieve is that the POSTGROUP number is automatically inserted as soon as the POSTCODE is entered. Access would have to check what is entered in the POSTCODE field (on the Members form), look up the correct value in the POSTCODES table and put it into the POSTGROUP field (also on the Members form).

For example: all Post Codes within the range 3100 to 3499 belong to the Postcode Group 335,
Therefore, if the Postcode 3249 is entered, the function (Macro?, VB? Query?) should find and insert 335 into the POSTGROUP field. Is this possible?

I have spent many hours trying to figure it out, but keep getting error messages. (I do not have VB programming knowledge but have worked with databases for a few years).
Any help would be much appreciated.
Regards….Irma

 

[Shadez]

Hmm best i can think of is to use the LostFocus event of the postcode textbox.

eg

Private Sub Postcode_LostFocus()
      Select Case postcode
             Case 1 to 1000
                    Postgroup = 100
             Case 1001 to 2000
                    Postgroup = 200
             Case 2001 to N
                    postgroup = 300
             Case else
                    msgbox "ERROR"
     End Select

End Sub

Note Postgroup is a textbox on the form bound to the field(you can hide this if you want)


Hope you can understand this.

Good luck


ShadeZ

 

[Irmgard Wesselb]

Compare/find value

Thank you very much, Shadez,

The problem has meanwhile been solved in a different way, because I found just the right list on the Australia Post website. This list (approx. 13000 locations) includes the corresponding Postgroup numbers (approx. 70). So now I have a new Postcodes table which includes the correct Postgroup number for each location.
A Combo Box on the Members Form allows looking up each location and automatically filling in some fields (e.g. State, Postcode, Postgroup).

However, I would still love to know if some coding in Access would have been possible to look up numbers that fall within a range (the "From" and "To" columns in the old Postcodes table, see my original post) and find the corresponding Postgroup number.
I was thinking along the lines: If [Postcode] is between table![Postcodes]![To] and table![Postcodes]![[From] Then....insert the Postgroup number into the Postgroup field on the Members Form.
Not being VB-literate, I have no clou what the syntax is, but the logic may be right.

Would some wise programmers like to look into this and let me know whether an IF statement could be used? The suggested SELECT CASE statement would be very long because of the number of possibilities.
Thanks and regards,

 

[WayneRyan]

Irma,

You could use the DLookup function (On the BeforeInsert event)
to retrieve the value. There are plenty of examples here and
in help.

Wayne