Frustrated as Dave!

[Garth]

I have researched all the threads in the Feb 3 posting
by "Dave" and the answers by "John".

I too need to find a record based on multiple columns.

I have created a query and concatenated the two keys, but
now find I can't refer to the expression created. The
find method can locate the "ordinal" or "field" referred
to in the find.

I need to find in the Finance Department the Vice
President. or in the Finance Department maybe the
Accountant. So I need to search the Finance Department
for "Finance" and "Vice President" or "Finance"
and "Accountant".

Is there no way to search on multiple columns?
Can't I search on a concatenated expression in a query?

If so how?

 

[John Vinson]

I have researched all the threads in the Feb 3 posting
by "Dave" and the answers by "John".

I too need to find a record based on multiple columns.

I have created a query and concatenated the two keys, but
now find I can't refer to the expression created. The
find method can locate the "ordinal" or "field" referred
to in the find.

I need to find in the Finance Department the Vice
President. or in the Finance Department maybe the
Accountant. So I need to search the Finance Department
for "Finance" and "Vice President" or "Finance"
and "Accountant".
Is there no way to search on multiple columns?

Certainly! Access wouldn't be much of a database if you couldn't!

You do not need to - and SHOULD NOT - concatenate the fields. It's
easier to search them separately.

In the Query Grid you can type

"Finance" "Vice President"
"Finance" "Accountant"

under the Department and Position fields. Each line in the query grid
is an OR clause. The SQL would be

WHERE [Department] = "Finance" AND ([Position] = "Vice President" OR
[Position] = "Accountant])

Can't I search on a concatenated expression in a query?

Well, yes... if you just concatenate the two fields you could search
that concatenation for

"FinanceVice President" OR "FinanceAccountant"

(or however you have chosen to concatenate them). But why jam the two
fields together if you want to search them independently!?

 

[daave]

Hey John!

I wanted to follow up on this thread a bit. Just to thank
you for what you did for me on the multiple
column "find". While I couldn't get it to work through
the "find method" I did work with your suggestions and
definately learned a lot from you and from the research it
made me do. It was a great posting and response and I
benefited from it immeasurably. I'm going to try this
current response as well.

You did a great job for me! Thanks again. Dave

-----Original Message-----

I have researched all the threads in the Feb 3 posting
by "Dave" and the answers by "John".

I too need to find a record based on multiple columns.

I have created a query and concatenated the two keys, but
now find I can't refer to the expression created. The
find method can locate the "ordinal" or "field" referred
to in the find.

I need to find in the Finance Department the Vice
President. or in the Finance Department maybe the
Accountant. So I need to search the Finance Department
for "Finance" and "Vice President" or "Finance"
and "Accountant".
Is there no way to search on multiple columns?

Certainly! Access wouldn't be much of a database if you couldn't!

You do not need to - and SHOULD NOT - concatenate the fields. It's
easier to search them separately.

In the Query Grid you can type

"Finance" "Vice President"
"Finance" "Accountant"

under the Department and Position fields. Each line in the query grid
is an OR clause. The SQL would be

WHERE [Department] = "Finance" AND ([Position] = "Vice President" OR
[Position] = "Accountant])

Can't I search on a concatenated expression in a query?

Well, yes... if you just concatenate the two fields you could search
that concatenation for

"FinanceVice President" OR "FinanceAccountant"

(or however you have chosen to concatenate them). But why jam the two
fields together if you want to search them independently!?


.

 

[Guest]

You can also make it up to the user to enter exactly what
they are looking for by putting [type the department name]
and under the roles field [type the role] This way when
the query is run the user controls what fields come back
and you don't have to make multiple queries.

-----Original Message-----
Hey John!

I wanted to follow up on this thread a bit. Just to thank
you for what you did for me on the multiple
column "find". While I couldn't get it to work through
the "find method" I did work with your suggestions and
definately learned a lot from you and from the research it
made me do. It was a great posting and response and I
benefited from it immeasurably. I'm going to try this
current response as well.

You did a great job for me! Thanks again. Dave

-----Original Message-----

I have researched all the threads in the Feb 3 posting
by "Dave" and the answers by "John".

I too need to find a record based on multiple columns.

I have created a query and concatenated the two keys, but
now find I can't refer to the expression created. The
find method can locate the "ordinal" or "field" referred
to in the find.

I need to find in the Finance Department the Vice
President. or in the Finance Department maybe the
Accountant. So I need to search the Finance Department
for "Finance" and "Vice President" or "Finance"
and "Accountant".
Is there no way to search on multiple columns?

Certainly! Access wouldn't be much of a database if you couldn't!

You do not need to - and SHOULD NOT - concatenate the fields. It's
easier to search them separately.

In the Query Grid you can type

"Finance" "Vice President"
"Finance" "Accountant"

under the Department and Position fields. Each line in the query grid
is an OR clause. The SQL would be

WHERE [Department] = "Finance" AND ([Position] = "Vice President" OR
[Position] = "Accountant])

Can't I search on a concatenated expression in a query?

Well, yes... if you just concatenate the two fields you could search
that concatenation for

"FinanceVice President" OR "FinanceAccountant"

(or however you have chosen to concatenate them). But

why
jam the two

fields together if you want to search them independently!?


.

.