WHERE clause in select statement

[keithgi]

I'm trying to use 'MyRecordSource = MySQL & MyCriteria' to search for data in a set of linked tables based upon a name typed into a search box by the user.

I can do the SELECT statement bit, but don't know how to write the MyCriteria part for the WHERE clause. In effect, I want to find all clients where the client name matches the name typed into the search box by the user. e.g. WHERE Clients.ClientName = * <text entered in box> *

Can anyone help please?

 

[LarryDeLaruelle]

Keith:

The where statement should look something like this:

WHERE Clients.ClientName = '&quot; & txtFieldName & &quot;';&quot;

Where txtFieldName is the name of the field where the user is to enter the name to be searched on. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[keithgi]

Such a speedy response!

Thanks for your help Larry, I'll try it tomorrow.

Cheers, Keith

 

[LarryDeLaruelle]

Keith:

Just make sure you include the single quotes within the string so that the value of the user entered data is within those quotes since it is (I assume) a text string. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[keithgi]

Thanks Larry.

Got it to work today - to my surprise I must have got the rest of the code right as well.

Thanks.

 

[keithgi]

Thanks Larry.

Got it to work today - to my surprise I must have got the rest of the code right as well.

Next step is to get the code to automatically add an * at either end of the users entry. Do I just do this by typing &quot;*&quot; & at the start and end?

Thanks.

 

[LarryDeLaruelle]

Keith:

Try this:

WHERE Clients.ClientName Like '&quot; & txtFieldName & &quot;*';&quot;

or if you want an instring search:

WHERE Clients.ClientName Like '*&quot; & &quot;txtFieldName & &quot;';&quot;

Let me know if it works (Should). Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[keithgi]

Thanks Larry, it works a treat.


Keith