In Operator syntax

[rjtetley]

Access 2003 syntax

Can anyone help with the following problem concerning Operator syntax please. Access Help and Alison Balti's Mastering Access 2003 do not enlighten.

Problem
I have a field in a query for MemberType in a membership database.
Member Types are designated in a single field by single characters separated by a space.
A member may have more than one member type (e.g. Gold member G and Founder member F).
I can use the Access Like Operator to set criteria e.g:
Like [Enter Member Type] & "*"
to open a window, then enter the single character, e.g. G, to display the selection of Gold members, but this only displays the members where G is the first character in the field. If the character is in the field is not the first character the member is not displayed.

I believe I need to deploy the In Operator to display the Member Type wnen the relevant character isin any part of the field. I have tried all sorts of syntax e.g. In [Enter Member Type] & "*", but get messages such as In operator needs parentheses. But if I add parentheses - (In) - I get a message Too many parentheses.
And many other variations on the same theme.

Can anybody put me right please?

Richard Tetley

 

[pbaldy]

The data isn't normalized, but try

Like "*" & [Enter Member Type] & "*"

The In operator is used for multiple test values:

WHERE FieldName IN(1, 2, 3, 7)

which is the equivalent of

WHERE FieldName = 1 OR FieldName = 2 OR FieldName = 3 OR FieldName = 7

 

[plog]

A field should hold one discrete piece of information, you should not shoe horn in multiple values into one field. The correct way is to create a new table to hold all your MemberTypes.

It would have 2 fields, the ID field of your Members table and the member type:

MemberID, MemberType
1, G
1, F
2, G
3, F

If a person has multiple memberships they would be in your member types table multiple times (i.e. MemberID=1).

Structure your database correctly and you will avoid having to hack together solutions to get the data you want from it.

 

[rjtetley]

I am just getting the hang of this website. Thanks for this response. I take your point

A field should hold one discrete piece of information, you should not shoe horn in multiple values into one field. The correct way is to create a new table to hold all your MemberTypes.

It would have 2 fields, the ID field of your Members table and the member type:

MemberID, MemberType
1, G
1, F
2, G
3, F

If a person has multiple memberships they would be in your member types table multiple times (i.e. MemberID=1).

Structure your database correctly and you will avoid having to hack together solutions to get the data you want from it.

 

[rjtetley]

Thanks for this. I will have a go.Another helpful member reminds me that I could have a separate table for Member Types to avoid the need for code. I have enough to get me going again now.

The data isn't normalized, but try

Like "*" & [Enter Member Type] & "*"

The In operator is used for multiple test values:

WHERE FieldName IN(1, 2, 3, 7)

which is the equivalent of

WHERE FieldName = 1 OR FieldName = 2 OR FieldName = 3 OR FieldName = 7

 

[jdraw]

For your reference:

There is a lot of info concerning MS Access functions and some SQL functions/operators at
http://www.techonthenet.com

eg
http://www.techonthenet.com/sql/in.php