Search on Partial text

[Sticky99]

Hi Guys, can I Ask for some advice. I'm fairly new to Access and have an issue regarding a search on a form. I have a combo box to select a table heading and a text box where I can input search criteria. The problem is that my search does not return some records based on a partial text search, for Instance, If I search for "DER" the search returns no records although I know there is a record containing "DERBY". The Macro I am using contains the following:
Where ="[" & [cboSearchHeading] & "] like '" & [txtCriteria] & "*'"
I would appreciate any advice please.

TIA
Dave

 

[Galaxiom]

The record would need to start with DER for that to work. Put an asterisk at the beginning too.

BTW It won't find in long text fields past the 255th character.

 

[Sticky99]

Thanks Galaxiom,

Is there any way that I can search on partial text within the whole field?

 

[sonic8]

It won't find in long text fields past the 255th character.

It should.

 

[MajP]

Is there any way that I can search on partial text within the whole field?

You may have missed what Glaxiom suggested.

*DER ' would find a string that ends in DER "I sent back my order"
*DER* 'Finds a string with DER anywhere "I sent back my order to the kitchen"
DER* ' Finds a string that starts with DER "Derby Winner"

 

[Sticky99]

Thanks MajP, I understood the post by Galaxiom, however, I am trying to make this "Idiot" proof for the user, and just let them input a string without the *, is this possible?

Thanks
Dave

 

[MajP]

Thanks MajP, I understood the post by Galaxiom, however, I am trying to make this "Idiot" proof for the user, and just let them input a string without the *, is this possible?

He was not suggesting the user enter, but you would.
"[" & [cboSearchHeading] & "] like '*" & [txtCriteria] & "*'"

 

[Sticky99]

Hi MajP,

I added the * to the expression ="[" & [cboSearchHeading] & "] like *'" & [txtCriteria] & "*'" and am now getting the following error
Syntax Error (Missing Operator) in Query Expression '[Received From] Like *'Der*".

Any ideas?

Thanks

 

[MajP]

The single quote is wrong location. Should be '*text*'

 

[Sticky99]

Hi MajP,

I think I had a typo and all seems to be working now. Many thanks to all who took the time to reply to my post, great help.

Cheers.

Dave

 

[Galaxiom]

The single quote is wrong location. Should be '*text*'

Yes your are correct. I was thinking Like was similar to joins being limited to the first 255 characters in a field.

I recalled there was another situation beyond joins but I think now it is the Order By clause that only uses 255 characters.

I might be wrong about these too of course.

 

[The_Doc_Man]

G., you are correct. ORDER BY is limited to a total of 255 characters in any combination of fields.

http://www.avantixlearning.ca/micro...ns-microsoft-access-training-course-resource/

Sort limit: 255 characters in one or more fields