Parameter Query - Adding a Wild Card 1

[chucky2000]

Hi, I am using MS Access and running a Query on a Text Field using a Parameter. The Text field in question can contain many variants of a name. For example

Republic Brazil
Republica Brazil
Republica Brazil SA

I would like Users to be able to Input into this parameter box; for example 'Rep', and the query show all of these variables without having to input the correct name of the item.
Help would be appreciated.
Chucky

 

[nabiS2K]

Try placing the parameter in the query's criteria inside the Like function.

Example:
Like([MyParameter]) God Bless
Mike ;-)

 

[Quehay]

Use this little jeito as the parametric criterion:

[tt]
[Enter first three letters] & "*"[/tt]

 

[Quehay]

I forgot the [tt]LIKE[/tt]

Mike forgot the [tt]& "*" [/tt] ;-)

 

[chucky2000]

thanks for getting back to me.
but unfortunately neither of these options worked.
thanks

 

[nabiS2K]

Make sure the queries criteria is just like this:

=Like "*" & [MyParameter] & "*"

Surely this would work. God Bless
Mike ;-)

 

[chucky2000]

Sorry Mike it didn't like the Syntax.
Have you any other suggestions?
Chucky

 

[nabiS2K]

I just tested the query field criteria and it worked correctly for me. Can you copy and paste the SQL statement into a reply and maybe I can help better? Maybe I don't understand fully what you are trying to do. God Bless
Mike ;-)

 

[chucky2000]

Mike, thanks for taking a look at this.
The SQL is below

SELECT ICT_ICT_TRANSACTION.SCHEDULED_TERMINATION_DATE, ICT_ICT_TRANSACTION.CHARACTER_4 AS REF_ENT, ICT_ICT_TRANSACTION.BOOK_CODE
FROM ICT_ICT_TRANSACTION
WHERE (((ICT_ICT_TRANSACTION.SCHEDULED_TERMINATION_DATE)>Date()) AND ((ICT_ICT_TRANSACTION.CHARACTER_4)=[MyParameter]))
ORDER BY ICT_ICT_TRANSACTION.BOOK_CODE;

 

[Quehay]

You're asking for ICT_ICT_TRANSACTION.CHARACTER_4 to equal (=) something. If you want to enter less than the full and exact spelling then you have to use [tt]LIKE[/tt] (as Mike pointed out).

Here's something I just tested in my own db:
[tt]
SELECT TBL_CNTRYS.CNTRY_NAME
FROM TBL_CNTRYS
WHERE (((TBL_CNTRYS.CNTRY_NAME) Like [enter country] & "*&quot);
[/tt]

 

[nabiS2K]

Quehay is correct. Here is the same prinicple applied to the query that you sent me.

SELECT ICT_ICT_TRANSACTION.SCHEDULED_TERMINATION_DATE, ICT_ICT_TRANSACTION.CHARACTER_4 AS REF_ENT, ICT_ICT_TRANSACTION.BOOK_CODE
FROM ICT_ICT_TRANSACTION
WHERE (((ICT_ICT_TRANSACTION.SCHEDULED_TERMINATION_DATE)>Date()) AND ((ICT_ICT_TRANSACTION.CHARACTER_4) Like "*" & [MyParameter] & "*"))
ORDER BY ICT_ICT_TRANSACTION.BOOK_CODE;

Try pasting this into a new query and see if it works like you desire.

God Bless
Mike ;-)

 

[chucky2000]

Thanks Guys it worked.
That was excellent.
Much appreciated.
Chucky

 

[chucky2000]

Sorry to bother you guys again but the Query I am running has some problems.
Would the Query not function as well if the table I am looking at is reasonably large, 9000 items?
Or possibly using Upper or Lower case.
I am having problems with bringing back the correct criteria when inputting the information in the Parameter.
Any ideas,
Many thanks,

Chucky

 

[Quehay]

Access is not case-sensitive.

Are you trying to enter the parameter entirely in code?(Based on previous I don't think so.) This requires a different approach than opening the query normally.

Make sure you understand what your criterion is saying, i.e., "Show me anything that's got any char + 'rep' + any char, etc." Originally you said that you wanted to type 'rep' and get any variation of Republica do Brazil, etc. You probably don't want the wildcard (*) in front of the parameter--this is saying "Give me any string that has any number of different characters, or none, in front of 'rep'".

 

[Guest_imported]

Hi everyone, I'm having the same problem with the LIKE command trying this: if i have a paramter with the letter m i want to find all the usernames where m is foud. i have tried all of the above but nothing seems to work. I'm using zope. can somebody help me please?