same Criteria for several Fields within a Table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a table that has 7 fields all of which may contain a given value. I
need to add criteria that selects records where *any* of these fields equal a
given vale. It's complicated by the fact that this value could actually be
one be of a list of 5

All of the the 7 fields effectively need the follwing criteria:

Like "c71*" Or Like "c72*" Or Like "c73*" Or Like "c74*" Or Like "c75*"

I know I can enter the same criteria seven times, to get the "OR", that
works, but is there an easier way?

The question has been listed here, but when using a parameter in the
criteria, where as mine is the same list each time.

Any advice appreciated, I'm happy to hack the SQl if that's the way to go

Cheers

Matt
 
I *think* the table has to be done this way. I certainly wouldn't be able to
redesign it as too many process's already use it. It's medical data - the
codes describe a process. Each series of treatment can be coded up to 7
times, but it's done on a priority basis so a C101 procedure maybe one
persons primary treatment code, but someones elses secondary code so it would
appear in the second field for them.

If it can't be done no problem, but it's always woth looking for the quick
way!

Cheers

Matt
 
True, I'd probably have gone that route with a "primary procedure" checkbox
or something, but as I say the table's already in place and used my many
people/process's so I can't change it - plus we'd have to migrate the
existing data. Cheers for the response though, excellent service, I'll be
back I'm sure!

Matt
 
IF your values are always
Like "c71*" Or Like "c72*" Or Like "c73*" Or Like "c74*" Or Like "c75*"
You could shorten this a bit and use one value under each column you are searching

Like "c7[1-5]*"
That finds all matches starting with c7 followed by the digit 1 to 5 followed by anything.
 
Much neater, thank-you!

Matt

John Spencer (MVP) said:
IF your values are always
Like "c71*" Or Like "c72*" Or Like "c73*" Or Like "c74*" Or Like "c75*"
You could shorten this a bit and use one value under each column you are searching

Like "c7[1-5]*"
That finds all matches starting with c7 followed by the digit 1 to 5 followed by anything.

True, I'd probably have gone that route with a "primary procedure" checkbox
or something, but as I say the table's already in place and used my many
people/process's so I can't change it - plus we'd have to migrate the
existing data. Cheers for the response though, excellent service, I'll be
back I'm sure!

Matt
Click to expand...
Click to expand...
 
Back
Top