Search on Partial text (1 Viewer)

Sticky99 · Jan 21, 2020

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 · Jan 21, 2020

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 · Jan 21, 2020

Thanks Galaxiom,

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

sonic8 · Jan 21, 2020

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

It should.

MajP · Jan 21, 2020

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 · Jan 21, 2020

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 · Jan 21, 2020

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 · Jan 21, 2020

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 · Jan 21, 2020

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

Sticky99 · Jan 21, 2020

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 · Jan 22, 2020

MajP said:
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 · Jan 22, 2020

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

Search on Partial text (1 Viewer)

Sticky99

Registered User.

Galaxiom

Super Moderator

Sticky99

Registered User.

sonic8

AWF VIP

MajP

You've got your good things, and you've got mine.

Sticky99

Registered User.

MajP

You've got your good things, and you've got mine.

Sticky99

Registered User.

MajP

You've got your good things, and you've got mine.

Sticky99

Registered User.

Galaxiom

Super Moderator

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread