Using Like Statement with Prompt

[crhodus]

I have a report that uses a query to pull data based on the user's name. Currently the report will prompt the user to enter in a name and then run the report based on that name.

I would like the user to be able to run a report with wild cards. Instead of typing "John Doe" to view all his records, I would like the user to be able to just type "John" and view all records with this name in it.

I tried using the following code, but it will not pull in any records unless the name is exact. DO I have my syntax wrong? I thing it is incorrect where it states "Like [Enter User Name]".

Thanks in advance!

SELECT Contacts.FieldOffice, Contacts.UserName, Calls.CallType, Calls.CallDate, Calls.CallTime, Calls.Subject, Calls.Notes, Company_T.COMPANY_NAME, Contacts.ContactID
FROM Contacts INNER JOIN (Calls LEFT JOIN Company_T ON Calls.ContactID = Company_T.COMPANY_NUMBER) ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.UserName) Like [Enter User Name]));

 

[DJBummy]

I think you may just need to add "*"& after the LIKE in your code.

D.J.

[This message has been edited by DJBummy (edited 08-31-2001).]

[This message has been edited by DJBummy (edited 08-31-2001).]

 

[R. Hicks]

You could change your SQL to:

SELECT Contacts.FieldOffice, Contacts.UserName, Calls.CallType, Calls.CallDate, Calls.CallTime, Calls.Subject, Calls.Notes, Company_T.COMPANY_NAME, Contacts.ContactID
FROM Contacts INNER JOIN (Calls LEFT JOIN Company_T ON Calls.ContactID = Company_T.COMPANY_NUMBER) ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.UserName) Like [Enter User Name] & "*"));

But be forewarned that if you enter "John" as the parameter, this will return all entries who's name is John and also records that contain "John" in their name such as "Johnathan" or "Johnny". So you may need to think about this.

HTH
RDH

[This message has been edited by R. Hicks (edited 08-31-2001).]

 

[crhodus]

Thanks for your help. I added the & "*" to the end of the statement. There are only 8 different users and none have a similar name so I should be ok. Thanks again!