Using Code to test if a value is in a list

[Amy]

I have a form I'm building where the user enters a booth number to be added
to the booth inventory. The booth number must begin with a letter, so I am
testing to make sure that it does by using the line
If Left(Me.Booth_Number, 1) < "A" Or Left(Me.Booth_Number, 1) > "Z" Then

However, what I really want to do is limit the possibilities to a set of
letters that change from show to show depending on the facility we are in.
For instance, in 2010, the values are N, C, S, and P for North, Central,
South, and Parking; but in 2011, we move to a different city, so the values
would change to W, S, and P for West, South, and Parking.

Basically, what I want it to do is run a query that pulls the first
character of each hall from my floor plans table and compares the first
character from the booth number against that subset. If they don't match,
then a dialog box opens to tell the user that he needs to include the initial
of the hall in the booth number.

Is there any way to do that using VBA code, or am I way off here?

 

[Marshall Barton]

I have a form I'm building where the user enters a booth number to be added
to the booth inventory. The booth number must begin with a letter, so I am
testing to make sure that it does by using the line
If Left(Me.Booth_Number, 1) < "A" Or Left(Me.Booth_Number, 1) > "Z" Then

However, what I really want to do is limit the possibilities to a set of
letters that change from show to show depending on the facility we are in.
For instance, in 2010, the values are N, C, S, and P for North, Central,
South, and Parking; but in 2011, we move to a different city, so the values
would change to W, S, and P for West, South, and Parking.

Basically, what I want it to do is run a query that pulls the first
character of each hall from my floor plans table and compares the first
character from the booth number against that subset. If they don't match,
then a dialog box opens to tell the user that he needs to include the initial
of the hall in the booth number.

I think I would use the Like operator for this. Should be
something like

strLetters = DLookup("letters", "FloorPlans") ' or whatever
If Me.Booth_Number Like "[" & strLetters & "]*" Then
MsgBox "building letter missing or wrong"
End If

 

[Amy]

I can see where that would work if I knew ahead of time which letter it
should be, but there are three to four possible letters it could be, and it
varies from city to city. As I said, the show this year is in Vegas with
three halls and a parking lot, while next year we are in Orlando with two
halls and a parking lot. I'm trying to code the test as a variable using the
current show ID so I don't have to go in and hard code it every year.
--
Amy

A dog accepts you as the boss. A cat wants to see your resume

I have a form I'm building where the user enters a booth number to be added
to the booth inventory. The booth number must begin with a letter, so I am
testing to make sure that it does by using the line
If Left(Me.Booth_Number, 1) < "A" Or Left(Me.Booth_Number, 1) > "Z" Then

However, what I really want to do is limit the possibilities to a set of
letters that change from show to show depending on the facility we are in.
For instance, in 2010, the values are N, C, S, and P for North, Central,
South, and Parking; but in 2011, we move to a different city, so the values
would change to W, S, and P for West, South, and Parking.

Basically, what I want it to do is run a query that pulls the first
character of each hall from my floor plans table and compares the first
character from the booth number against that subset. If they don't match,
then a dialog box opens to tell the user that he needs to include the initial
of the hall in the booth number.

I think I would use the Like operator for this. Should be
something like

strLetters = DLookup("letters", "FloorPlans") ' or whatever
If Me.Booth_Number Like "[" & strLetters & "]*" Then
MsgBox "building letter missing or wrong"
End If

 

[Marshall Barton]

I can see where that would work if I knew ahead of time which letter it
should be, but there are three to four possible letters it could be, and it
varies from city to city. As I said, the show this year is in Vegas with
three halls and a parking lot, while next year we are in Orlando with two
halls and a parking lot. I'm trying to code the test as a variable using the
current show ID so I don't have to go in and hard code it every year.

You need to explain the table and fields that can be used to
determine the building letters for a city. The code I
posted had a DLookup as a place holder, but I'm sure it's
more complicated than that. It could be as simple as a
Cities table with a field containing the allowable letters
or some other arrangement involving multiple tables and
fields.

 

[Amy]

Actually, it turned out to be even simpler than that. I decided to flowchart
the process to focus my mind, and it hit me out of the blue. My boss who
designed the layout and then turned it over to me to code had placed the hall
field at the end to be filled automatically if the booth existed. I just put
the hall field first, so now I just have to compare Left(Booth, 1) to
Left(Hall, 1) for my test.