Truncating a value then querying based on it

[aphelps]

Hiya--

I have the following fields:

NestNumber (AANNNN), in which the first two are letters abbreviating the county in which the nest occurs, the next two are numbers indicating the year, and the last two are the number of the nest in that county that year. Ex: AC0301 would be the first nest in Accomack County in 2003.

CountyCode (NNN) which is a three digit unique number representing each county. Ex: 001 = Accomack County.

What I would like to do is to take the first two letters of the nest code and automatically fill in CountyCode based on that abbreviation. I have a lookup table that is structured thusly:

County Code | Abbreviation | County Name

So, being new to VBA, I have written the following code that is meant to execute upon exit of the form field "NestNumber":

Private Sub NestNumber_Exit(Cancel As Integer)

Dim c
Dim county

c = NestNumber.Value

county = Left(c, 2)

CountyCode.Value = county

End Sub

This works insofar as CountyCode is filled in with the two letter abbreviation (this is what I thought it would do, a test of the rest of the code). However, I want it to then translate that abbreviation into the three digit county code. I think I could do this easily with a query, but I can't figure out how to call a query in VBA and pass it the variable "county" as its parameter.

I am not necessarily attached to using a query, if a simple VBA resolution is available, I'm open to suggestions.

Thanks--

 

[Jack Cowley]

Me.CountyCode = DLookup("[County Code]", "LookupTableName", "[Abbreviation] = '" & Left(Me.NestNumber, 2) & "'")

I think I have all you fields correct except for the lookup table name. The code above must be all on one line...

hth,
Jack

 

[aphelps]

I was actually just coming back to post that I'd figured it out...once again, wtihout VB (someone somewhere doesn't want me to learn it, I guess).

I based the dropdown in County on a query that looked like this:

Show the county code based on abbreviation with the criteria
Left([Forms]![frmMainForm]![NestNumber],2).

This results in a dropdown list with one value, the proper three-digit code for that county. Not perfect, as the value isn't filled in, buit it prevents errors, which is the main reason I wanted it.

Once I get this all done (has to be ready @ end of the month), I'll go back and put your code in to get it to work using VB, but I want to make sure the whole thing is fully-functional first.

Thanks for your help--

 

[Jack Cowley]

I had thought of suggesting a combo box but I went for the code instead. A combo box is a good solution, but requires the user to use it.

Good luck...

Jack