Query help 1

[jd20005]

I have built a database for my company. It contains tables storing information on courses and who they are run by. I have built a query which allows the user to type in the name of the course and then the query will display information about that course.

I wish to modify the query so that the user can enter a word such as "customer" and the query will then display all courses that contain that word in the course title, for example "customer service" "customer relations" etc. At the moment the use has to type in "customer service" to find customer service and "customer relations" to find customer relations.

the SQL code for my query is like this:

SELECT Courses.[Ref no], Courses.[Client name], Courses.[Course title], Courses.Description, Courses.[Project manager]
FROM Courses
WHERE (((Courses.[Course title])=[Please type in title]));


Can anyone help?

 

[GHolden]

Hi,

Someting like this should work...

PARAMETERS FindTitle String;
SELECT Courses.[Ref no], Courses.[Client name], Courses.[Course title], Courses.Description, Courses.[Project manager]
FROM Courses
WHERE (((Courses.[Course title]) LIKE '*' & FindTitle & '*'));

You can change the parameter name to what you want, just make sure you change it in both places.


There are two ways to write error-free programs; only the third one works.

 

[MakeItSo]

On the form where the user inputs the query criterion, add this to the Click() event of your Search button, or whatever shall fire the query:

Dim sSQL as String

sSQL="SELECT Courses.[Ref no], Courses.[Client name], Courses.[Course title], Courses.Description, Courses.[Project manager] " _
& "FROM Courses " _
& "WHERE (((Courses.[Course title]) LIKE '" & ME![YourTextBox on the form] & "*'));"

DoCmd.OpenForm "Your ResultForm", sSQL

Or you can create a query:
SELECT Courses.[Ref no], Courses.[Client name], Courses.[Course title], Courses.Description, Courses.[Project manager]
FROM Courses
WHERE (((Courses.[Course title]) LIKE Forms![YourSearchForm]![YourTextBox]));

and use it as data source for the result form.

Does that help you?

MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[jd20005]

Thank you that worked a charm.
What programming language is that?
is it known as SQL or JET SQL?

 

[MakeItSo]

Actually this is Visual Basic + Jet SQL (1st code) resp. Jet SQL straight (2nd code).

You can easily find the difference by looking at the wildcard char:
Standard SQL has percent sign % as wildcard, Jet SQL has the asterisk *

So You should also be able to use this in standard SQL by exchanging the * in the Visual Basic code (1st version) by the percent sign.

;-)
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos