Using IN with a parameter and field

[vclink]

I am trying (unsuccessfully so far) to an IN with a field and parameter in my WHERE of my query. Here's the story. My database is information about international business trips. It keeps track of who is travelling, what flights, hotels, etc. I have a tables for the individuals' "profile" info, trips, flights, hotels, and then trip info specific to a trip tied to an individual and their various flights/hotels/etc for that trip. The trips are often to multiple countries but sometimes travelers only participate in one portion of the trip (to one country). We currently have a field called Participation in which we enter the initial of the country(s) in which the traveler will participate. When we produce listings of trip participants I'd like the option of using that field to create a list of those who will visit that country. Adding to the detail, I also want to be able to leave that parameter empty and get all travelers for the trip regardless of the countries in which they are participating. Example:

Trip to Korea and China. In the participation field (a short text field) traveler A has K (indicating they are travelling to Korea only on this trip); traveler B has K, C; traveler C has K; traveler D has C. I want to be able to run the query (which asks for the trip name already) and have a parameter for country initial. If I enter nothing, I get travelers A, B, C and D. If I enter C, I get only travelers B and D. If I enter K, I get only travelers A, B and C.

I have tried the following in my query, and while it runs and seems to correctly ask me for input, I get no results unless I don't enter anything in the parameter, then I get all for that trip.

WHERE ([TripInfo.Participation])=Nz((('*' + [Enter Country Initial:]+'*') IN ([TripInfo.Participation])), [TripInfo.Participation]))

I think I'm saying give me all the travelers where the initial I enter when prompted is in the field Participation or, if I don't enter anything, give me all travelers.



Any helpful hints? Is there a different way I should/could be querying to get the results I want?

[lfpm062010]

Look like you got you syntax mixed up.

When using "*" (wildcard), you need to use key word "LIKE".
When using "IN", you need to supply with a list.
'+' is adding, '&' is concatenate.

It would be something like this.

WHERE ([TripInfo.Participation]) LIKE '*' & TRIM([Enter Country Initial:]) & '*'
AND [TripInfo.Participation] IN ([Participation1], [Participation2], ... etc )

[vclink]

THANK YOU! That works perfectly!