"In" Operator

[Guest]

If I have a text box named [States] on my [Workstation] form, and this text
box contains simple two-letter state codes with comma separators, for example
"AZ", "CO", "WA", etc, should I be able to reference this text box in the
criteria grid under a Project_State field with code along the lines of,
In(Forms![Workstation]![States])? I seem to be able to type only one state
reference into the text box with no surrounding quotes and receive a correct
return of data.

Thank you.

 

[Guest]

If your table field is a 2 character field containing one code, then the
answer is no. The query builder is not smart enough to know which of the
codes you want to use. Based on your post, I think you need to reconsider
your design. Can you tell me why you are using comma separatorated codes in
a text box when you need to look up only one?

 

[Guest]

Sorry if the post wasn't clear enough. The "two-letter" code reference means
that the states are not spelled out in their entirety. They are dropped into
this hidden text box by way of a subform selection. For example, a person
may need to look up projects for only three states, so they select "CA",
which populates the unbound [States] text box. Then they select "AL" which
moves alongside the "CA" appearing as "CA", "AL", then "CO", showing as "CA",
"AL", "CO".

In other words, the text field is intended to be utilized as nothing but a
holder for the query criteria.

If your table field is a 2 character field containing one code, then the
answer is no. The query builder is not smart enough to know which of the
codes you want to use. Based on your post, I think you need to reconsider
your design. Can you tell me why you are using comma separatorated codes in
a text box when you need to look up only one?

If I have a text box named [States] on my [Workstation] form, and this text
box contains simple two-letter state codes with comma separators, for example
"AZ", "CO", "WA", etc, should I be able to reference this text box in the
criteria grid under a Project_State field with code along the lines of,
In(Forms![Workstation]![States])? I seem to be able to type only one state
reference into the text box with no surrounding quotes and receive a correct
return of data.

Thank you.

 

[Guest]

Okay, I did not get that. In this case, you will have to have another
textbox or combo box to give it just one state to look up at a time. The
other option would be to put something like [Enter State Code] in you query
and it will pop up a box that wil allow the user to enter a state.

Sorry if the post wasn't clear enough. The "two-letter" code reference means
that the states are not spelled out in their entirety. They are dropped into
this hidden text box by way of a subform selection. For example, a person
may need to look up projects for only three states, so they select "CA",
which populates the unbound [States] text box. Then they select "AL" which
moves alongside the "CA" appearing as "CA", "AL", then "CO", showing as "CA",
"AL", "CO".

In other words, the text field is intended to be utilized as nothing but a
holder for the query criteria.

If your table field is a 2 character field containing one code, then the
answer is no. The query builder is not smart enough to know which of the
codes you want to use. Based on your post, I think you need to reconsider
your design. Can you tell me why you are using comma separatorated codes in
a text box when you need to look up only one?

If I have a text box named [States] on my [Workstation] form, and this text
box contains simple two-letter state codes with comma separators, for example
"AZ", "CO", "WA", etc, should I be able to reference this text box in the
criteria grid under a Project_State field with code along the lines of,
In(Forms![Workstation]![States])? I seem to be able to type only one state
reference into the text box with no surrounding quotes and receive a correct
return of data.

Thank you.