Hi
I'm a bit new to SQL and am having a problem with a query. The query is being used to search for a string, however the string has spaces in it.
The query needs to search in several fields.
Example:
Table called Jobs
Field called Job Number
Field called Client
Field called Address
Client may contain the name Bob or it may contain a fuller description such as Bob James Ltd. However sometimes the client will be Joe who is subcontracted by Bob, so the client will be James but the address will be Bob or Bob James Ltd instead.
I wish to find all jobs that Bob James was handling whether they appear as a client or indirectly in the address field. I can run this sql in MS Access and it works fine although I'm sure theres something a bit more tidy I could use:
Select Jobnumber, client, address
From Jobs
Where client like '*Bob*' or address like '*Bob*';
However what I now want to do is have a form where the user can type in a name, such as Bob, into a search field and then have the query take that and do its things and spit out the results.
I've created a form called printform and the unbound text box is called txtsearch, so I modified the SQL statement that was working to
Select Jobnumber, client, address
From Jobs
Where client like '*Forms!printform.txtsearch*' or address like '*Forms!printform.txtsearch*';
However if I enter Bob into the text box, I get no results returned - I've tried it with and without the quotes but no luck. I need it to return results whether its in the client or address fields and regardless of whether its in the form of Bob or Bob James ltd
I'm not quite sure of how to proceed from this point and was hoping someone could point me in the right direction
many thanks
steve
I'm a bit new to SQL and am having a problem with a query. The query is being used to search for a string, however the string has spaces in it.
The query needs to search in several fields.
Example:
Table called Jobs
Field called Job Number
Field called Client
Field called Address
Client may contain the name Bob or it may contain a fuller description such as Bob James Ltd. However sometimes the client will be Joe who is subcontracted by Bob, so the client will be James but the address will be Bob or Bob James Ltd instead.
I wish to find all jobs that Bob James was handling whether they appear as a client or indirectly in the address field. I can run this sql in MS Access and it works fine although I'm sure theres something a bit more tidy I could use:
Select Jobnumber, client, address
From Jobs
Where client like '*Bob*' or address like '*Bob*';
However what I now want to do is have a form where the user can type in a name, such as Bob, into a search field and then have the query take that and do its things and spit out the results.
I've created a form called printform and the unbound text box is called txtsearch, so I modified the SQL statement that was working to
Select Jobnumber, client, address
From Jobs
Where client like '*Forms!printform.txtsearch*' or address like '*Forms!printform.txtsearch*';
However if I enter Bob into the text box, I get no results returned - I've tried it with and without the quotes but no luck. I need it to return results whether its in the client or address fields and regardless of whether its in the form of Bob or Bob James ltd
I'm not quite sure of how to proceed from this point and was hoping someone could point me in the right direction
many thanks
steve
