Problem testing for sinmilar values in two fields?

[Steve]

Hi,

I'm running a query in Access 2003 to select all records that have one of
two values: "Pre-Games" or "Pre and Post Games" in two different fields in a
table. The fields are called Other Roles and Other Roles 2 and exist right
next to each other in the table. The problem I'm having is when I use Like
"Pr*" or list the entire constant in just the Other Roles field I get the
correct results; however, when I specify the same exact criteria in the
Other Role 2 field, I get blank results. I have tried using Like in one
field and testing for constants in the secon and have even moved the fields
in my query so they're separated - but I always get the same results. Test
in one field, ok - test in both fields - get nothing. Any suggestions? I'm
at a loss to explain why this is happening. It's a very simple test and
either I'm missing something very simple or there's a bug that I need a
workaround for.

HELP!

Thx!

 

[pietlinden]

Hi,

I'm running a query in Access 2003 to select all records that have one of
two values: "Pre-Games" or "Pre and Post Games" in two different fields in a
table. The fields are called Other Roles and Other Roles 2 and exist right
next to each other in the table. The problem I'm having is when I use Like
"Pr*" or list the entire constant in just the Other Roles field I get the
correct results; however, when I specify the same exact criteria in the
Other Role 2 field, I get blank results. I have tried using Like in one
field and testing for constants in the secon and have even moved the fields
in my query so they're separated -  but I always get the same results. Test
in one field, ok - test in both fields - get nothing. Any suggestions? I'm
at a loss to explain why this is happening. It's a very simple test and
either I'm missing something very simple or there's a bug that I need a
workaround for.

HELP!

Thx!

Why not just use OR?

part of your problem sounds like a normalization issue. Looks like
you should have child records instead of OtherRoleN kinds of fields.
That's a dead giveaway.