In need of a basic solution to a parameter value

[mattpearcey]

I have set various criteria so to ask the user to select what they want to view in reports. now the records i am filtering on are banks. now they are from all over the world and some have some very challenging names.

how do i set the criteria so as to work for say the first part of the name?

For example,

a banks is called Ceska Sporitelna. At present they have to enter all the name, correctly in order for that to come up. is there a way to set the query to work for say 'Ceska' or geting it to show you the nearest name, and you choose form that?

Thank you for your help already.

Matt Pearcey

 

[link9]

SELECT * FROM tableName WHERE bankName LIKE 'Ceska%'

The % is the wildcard character, and you can put it before or after a string preceded with the LIKE operator to find strings that start (or end, depending on where you put it) with 'Ceska' (or whatever)

good luck!
Paul Prewett

 

[mattpearcey]

So - heres my code below. DO i put the % after the bank name in the WHERE value? so .[Bank Name]%

or do i have to add like somewhere?


SELECT [Bank Details].Region, [Bank Details].Country, [Bank Details].[Bank Name], [Bank Details].[Software used], [Bank Details].[Software Purchased from], [Bank Details].[Software comments]
FROM [Bank Details]
WHERE ((([Bank Details].Region)=[What Region would you like to view?]) AND (([Bank Details].[Bank Name])=[What is the name of the bank you would like to view?])); Thank you for your help already.

Matt Pearcey

 

[link9]

WHERE ((([Bank Details].Region)=[What Region would you like to view?]) AND (([Bank Details].[Bank Name])=[What is the name of the bank you would like to view?]));

---------

WHERE ((([Bank Details].Region)=[What Region would you like to view?]) AND (([Bank Details].[Bank Name]) LIKE [What is the name of the bank you would like to view?]%));

I'm hoping this is also the solution for Access, as it is for SQL Server, but this would be my stab at the solution.


Paul Prewett

 

[link9]

Oh, and you're going to need your single tick marks in there, too, to denote the string -- the % needs to be on the inside of those marks.

 

[MichaelRed]

Matt,

while the wild card can probably be made to work, I think an easier (and more complete) soloution would be to have the Parameters selected from combo boxes on a form. the cmbo boxes could have the autocomplete feature on, so as a user types the name the box shows the first name matching the characters type by the user. when the desired name is reached, the user has typed the mininum number od chars to uniquely identify the specific/required entry. of course, you need to populate the possable entries with the list from the table.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[mattpearcey]

Thanks both of you. I see how both methods work. and will make use of both.

one quick question regarding the % value.

where do i have to add the tikc marks? like below?

WHERE ((([Bank Details].Region)=[What Region would you like to view?]) AND (([Bank Details].[Bank Name%]) LIKE [What is the name of the bank you would like to view?]%));

not too sure what you mean by the tick marks Thank you for your help already.

Matt Pearcey

 

[link9]

LIKE '[What is the name of the bank you would like to view?]%'

is what I meant, but since I don't use Access, and don't have alot of experience with forms (and those brackets, which I see alot, but don't use), I'm not sure how the code will react to your putting them around like that.

I think that MichealRed's solution does, in fact, sound like not only a better solution, but more user friendly, as well.


Paul Prewett

 

[mattpearcey]

i agree, but for the bank name i have set it as a normal text field, not a combo box. plus, the database holds only one record of each bank (so its set to no duplicates). So the query i am making is just to make some reports that can be filtered by the user to show detialed info on the bank.

if you get what i mean. I know wherei can use Michael's ideas though, and that will help a lot. But in this case, im sure it wont work, without me having to do a lot of work on the field strucutre and table. Thank you for your help already.

Matt Pearcey