Q: query asks for parameters multiple times

[Mark]

Hi all,

Using Access 2003. I have a query that's based on one table. I need to
search a few fields for text so I concatenated those fields and have the
criteria as shown below:

Field: [CSFNM1] & [CSLNM1] & [CSADD1] & [CSADD2] & [CSADD3] & [TNFNAM] &
[TNLNAM]
Criteria: Like "*" & [Enter Search Text] & "*"

All other fields in the query are there for viewing, ie. there's no other
criteria. I have added [Enter Search Text] as a query parameter, set to text.
There are no other query parameters.

For some reason, when I run the query, it asks me to fill the [Enter Search
Text] parameter 4 times. When it does run, it returns what I'm looking for,
but why does it have to ask 4 times?

Thanks!
-Mark

 

[John W. Vinson]

Hi all,

Using Access 2003. I have a query that's based on one table. I need to
search a few fields for text so I concatenated those fields and have the
criteria as shown below:

Field: [CSFNM1] & [CSLNM1] & [CSADD1] & [CSADD2] & [CSADD3] & [TNFNAM] &
[TNLNAM]
Criteria: Like "*" & [Enter Search Text] & "*"

All other fields in the query are there for viewing, ie. there's no other
criteria. I have added [Enter Search Text] as a query parameter, set to text.
There are no other query parameters.

For some reason, when I run the query, it asks me to fill the [Enter Search
Text] parameter 4 times. When it does run, it returns what I'm looking for,
but why does it have to ask 4 times?

Thanks!
-Mark

Please open the query in SQL view and post the SQL text here. That's a little
odd unless you have a query calling other queries!

One way to get around it is to user a Form to collect the criteria, and
instead of [Enter search text] use a form reference like
[Forms]![NameOfForm]![NameOfControl].

Do note that searching a concatenation of fields like this will *not* be able
to take advantage of any indexing on the field, and that the wildcard at both
ends of the search term will also defeat indexing. It'll work ok but may be
slow if your table gets large.

John W. Vinson [MVP]

 

[Mark]

Hi all,

Using Access 2003. I have a query that's based on one table. I need to
search a few fields for text so I concatenated those fields and have the
criteria as shown below:

Field: [CSFNM1] & [CSLNM1] & [CSADD1] & [CSADD2] & [CSADD3] & [TNFNAM] &
[TNLNAM]
Criteria: Like "*" & [Enter Search Text] & "*"

All other fields in the query are there for viewing, ie. there's no other
criteria. I have added [Enter Search Text] as a query parameter, set to text.
There are no other query parameters.

For some reason, when I run the query, it asks me to fill the [Enter Search
Text] parameter 4 times. When it does run, it returns what I'm looking for,
but why does it have to ask 4 times?

Thanks!
-Mark

Please open the query in SQL view and post the SQL text here. That's a little
odd unless you have a query calling other queries!

One way to get around it is to user a Form to collect the criteria, and
instead of [Enter search text] use a form reference like
[Forms]![NameOfForm]![NameOfControl].

Do note that searching a concatenation of fields like this will *not* be able
to take advantage of any indexing on the field, and that the wildcard at both
ends of the search term will also defeat indexing. It'll work ok but may be
slow if your table gets large.

John W. Vinson [MVP]

Thanks for the response,

Query is below. I've noticed I had this problem before (I think), and it was
temporarily solved with a compact/repair. Tried it here, but it doesn't work.

PARAMETERS [Enter Search Text] Text ( 255 );
SELECT [LOANTBL].CMLN, [LOANTBL].LIBRARY
FROM [LOANTBL]
WHERE ((([CSFNM1] & [CSLNM1] & [CSADD1] & [CSADD2] & [CSADD3] & [TNFNAM] &
[TNLNAM]) Like "*" & [Enter Search Text] & "*"));

Also, if I could use an index, that would be great, but I don't know if
there's a way to do that doing what I want to do. If I want to search for
"foo", I want records with fields "Foo", "Fool", "a fool", "foolish", "afoot"
to all be returned. If you have any suggestions, i'm all eyes.

Thanks again!

 

[John W. Vinson]

Query is below. I've noticed I had this problem before (I think), and it was
temporarily solved with a compact/repair. Tried it here, but it doesn't work.

PARAMETERS [Enter Search Text] Text ( 255 );
SELECT [LOANTBL].CMLN, [LOANTBL].LIBRARY
FROM [LOANTBL]
WHERE ((([CSFNM1] & [CSLNM1] & [CSADD1] & [CSADD2] & [CSADD3] & [TNFNAM] &
[TNLNAM]) Like "*" & [Enter Search Text] & "*"));

That should certainly NOT give you multiple prompts if you're just opening the
query datasheet, and I've never seen a query do that! Wierd.

A Form reference will solve the multiple prompts but may still search the
table inefficiently - I don't know!

Also, if I could use an index, that would be great, but I don't know if
there's a way to do that doing what I want to do. If I want to search for
"foo", I want records with fields "Foo", "Fool", "a fool", "foolish", "afoot"
to all be returned. If you have any suggestions, i'm all eyes.

If you're searching for text embedded with a text field, no indexing will be
available... period (at least not in Access).

John W. Vinson [MVP]

 

[Mark]

Query is below. I've noticed I had this problem before (I think), and it was
temporarily solved with a compact/repair. Tried it here, but it doesn't work.

PARAMETERS [Enter Search Text] Text ( 255 );
SELECT [LOANTBL].CMLN, [LOANTBL].LIBRARY
FROM [LOANTBL]
WHERE ((([CSFNM1] & [CSLNM1] & [CSADD1] & [CSADD2] & [CSADD3] & [TNFNAM] &
[TNLNAM]) Like "*" & [Enter Search Text] & "*"));

That should certainly NOT give you multiple prompts if you're just opening the
query datasheet, and I've never seen a query do that! Wierd.

A Form reference will solve the multiple prompts but may still search the
table inefficiently - I don't know!

Thanks John. It's happened to me before and I get little bugs like that
every now and then where it doesn't seem to be the fault of anything I'm
doing. Makes me wonder if I'm just doing things wrong somewhere else and it's
"bleeding" into other nonassociated regions.

I'll go the route with the form textbox.

If you're searching for text embedded with a text field, no indexing will be
available... period (at least not in Access).

Didn't think so. Oh well.