Find an exact match (1 Viewer)

[THREE05]

Hello World:
I'm currently having hard time trying to configure my form to do the search..

I have this .. On a form I have ID which is automatically generated. that's what I want to find as exact match.. If in ID 234 I have a Specific case, I want to Pull that one not LIKE numbers. thanks the problem Im having..

I am using macro builder under the Apply filter.. and I got it configured to pull Like "*" & .......

Any suggestions or anyone that wants to help me>???? I can send you my form for you to code it and send it to me, But I'll prefer to practice and learn.

As always THANK YOU my good Friends.....

 

[Ranman256]

Don't use LIKE.
Use exact match...
Where [field]= forms!frmFind!txtWord

 

[THREE05]

Don't use LIKE.
Use exact match...
Where [field]= forms!frmFind!txtWord

so If I need to pull ID from my query1 from SearchF?

Id= SearchF!txt1?
or
[SearchF]![ID]![txt1]

Just like that? becase with the like I have like this

[ID]Like "*" & [Forms]![SearchF]![txt1] & "*"


SearchF is the form that Im on
and ID is the one that Acces automatically creates.:banghead:

 

[sneuberg]

Just like that? becase with the like I have like this

[ID]Like "*" & [Forms]![SearchF]![txt1] & "*"

I'm not sure where you have that but, if that's what you have then try:

[ID] = [Forms]![SearchF]![txt1]

 

[THREE05]

I'm not sure where you have that but, if that's what you have then try:

[ID] = [Forms]![SearchF]![txt1]

Thanks buddy that worked Flawless....
Any Idea why I cant use this...ID] = [Forms]![SearchF]![txt1]
then use "Or "and a "Like " criteria in the same Apply Filter macro?

ID] = [Forms]![SearchF]![txt1] or [CustName] Like "*"& [Forms]![SearchF]![txt1] & "*"

I Tried this but seems that doesn't work? any way around ??

 

[sneuberg]

ID] = [Forms]![SearchF]![txt1] or [CustName] Like "*"& [Forms]![SearchF]![txt1] & "*"

I Tried this but seems that doesn't work? any way around ??

ID] = [Forms]![SearchF]![txt1]  or [CustName] Like "*"&  [Forms]![SearchF]![txt1] & "*"

is missing a left bracket just before ID and should be

[ID] = [Forms]![SearchF]![txt1]  or [CustName] Like "*" &  [Forms]![SearchF]![txt1] & "*"

but otherwise I' getting some crap about it being to complex. It's that what you are getting? I'll keep poking at it. I'm sure this is something simple that will make me feel stupid.

As a aside if ID is an autonumber, then it really doesn't have any meaning in the real world. Are you user's really going to remember what ID a record had?

 

[sneuberg]

Ok I figured it out. Ironically the solution is to add more complexity. The problem is that [ID] is a numeric field and when you put something nonnumeric in the textbox it complains about the expression being to complex. This can be solve with the iif function as follows. This should work for you

[ID] = IIf(IsNumeric([forms]![SearchF]![txt1]),[forms]![SearchF]![txt1],Null) OR [CustName] Like "*" & [forms]![SearchF]![txt1] & "*"

 

[THREE05]

ID] = [Forms]![SearchF]![txt1]  or [CustName] Like "*"&  [Forms]![SearchF]![txt1] & "*"

is missing a left bracket just before ID and should be

[ID] = [Forms]![SearchF]![txt1]  or [CustName] Like "*" &  [Forms]![SearchF]![txt1] & "*"

but otherwise I' getting some crap about it being to complex. It's that what you are getting? I'll keep poking at it. I'm sure this is something simple that will make me feel stupid.

As a aside if ID is an autonumber, then it really doesn't have any meaning in the real world. Are you user's really going to remember what ID a record had?

THanks a lot for your help buddy really means much to me.. And yes we have a record for each work tiket we get. every single one is different so we entere the record and write the ID on the top of the tiket . and before we file it, we need to estate when the tiket was completed.. so to go back to that specific record I need to find it by the ID otherwise it shows like records.

 

[THREE05]

Ok I figured it out. Ironically the solution is to add more complexity. The problem is that [ID] is a numeric field and when you put something nonnumeric in the textbox it complains about the expression being to complex. This can be solve with the iif function as follows. This should work for you

[ID] = IIf(IsNumeric([forms]![SearchF]![txt1]),[forms]![SearchF]![txt1],Null) OR [CustName] Like "*" & [forms]![SearchF]![txt1] & "*"

Hey buddy thanks for taking your time on helping me..
that's exactly what it shows about complexity ..

can you guide me trough where I put that code? can I add it on the Macrobuilder?

 

[sneuberg]

Where ever you had [ID] = [Forms]![SearchF]![txt1] just put it there.

This takes the place of both [ID] = [Forms]![SearchF]![txt1] and [ID]Like "*" & [Forms]![SearchF]![txt1] & "*"

I tested this in an Apply filter of a macro.

 

[THREE05]

Where ever you had [ID] = [Forms]![SearchF]![txt1] just put it there.

This takes the place of both [ID] = [Forms]![SearchF]![txt1] and [ID]Like "*" & [Forms]![SearchF]![txt1] & "*"

I tested this in an Apply filter of a macro.

so if I put this in the condition of a macro [ID] = IIf(IsNumeric([forms]![SearchF]![txt1]),[forms]![SearchF]![txt1],Null) OR [CustName] Like "*" & [forms]![SearchF]![txt1] & "*"

it should search for id or text correct?

Also I noticed that my macro condition field is not long enough for much code, what other option do I have? I would like to search ID, CustName, DateEntered, DateDeliveried. criteria but the one macro doesnt let me put so much in there.. ? thanks a Lot for your Help my Friend It means a Lot to me...

 

[sneuberg]

it should search for id or text correct?

Yes, see the attached screen shot that's from the attached database. You can see this work in the database albeit the text field is FirstName instead of CustName

Also I noticed that my macro condition field is not long enough for much code, what other option do I have? I would like to search ID, CustName, DateEntered, DateDeliveried. criteria but the one macro doesnt let me put so much in there.. ? thanks a Lot for your Help my Friend It means a Lot to me...

The where condition field of the apply filter is limited to 255 characters so you could probably do it. If you put dates in the condition you will have to do the same type of thing I did with the iif function for numbers. You could use the IsDate function in place of the IsNumeric. I'd simplify things by using more text boxes. I also suggest looking into using combo boxes for some of this. You could for example populate a combo box with the delivery dates in the table with a row source query that gets the distinct date. The user could then just pick a data in the down drop.

One more thing to try is to add a button to the form, and choose record operation and find record. You could have that search across all of the fields which appear to be what you want.

 

[THREE05]

Yes, see the attached screen shot that's from the attached database. You can see this work in the database albeit the text field is FirstName instead of CustName



The where condition field of the apply filter is limited to 255 characters so you could probably do it. If you put dates in the condition you will have to do the same type of thing I did with the iif function for numbers. You could use the IsDate function in place of the IsNumeric. I'd simplify things by using more text boxes. I also suggest looking into using combo boxes for some of this. You could for example populate a combo box with the delivery dates in the table with a row source query that gets the distinct date. The user could then just pick a data in the down drop.

One more thing to try is to add a button to the form, and choose record operation and find record. You could have that search across all of the fields which appear to be what you want.

I have a button that's were that code that You gave me is in.

I have an Unbound text box and a Search button next to it..

If you don't mind you think you can Help me out on this?

by the way, thanks for the help finding out Iif fuction it worked up good.. I went to the office and coded it in, and perfect worked Flawless.. A million of thanks buddy...

 

[THREE05]

Thanks to Everyone who had help me
God bless you all...

 

[sneuberg]

I have a button that's were that code that You gave me is in.

I have an Unbound text box and a Search button next to it..

If you don't mind you think you can Help me out on this?

by the way, thanks for the help finding out Iif fuction it worked up good.. I went to the office and coded it in, and perfect worked Flawless.. A million of thanks buddy...

If you have the search working with the where condition I gave you I don't understand what you want me to help you with now. I'd be glad to help once I understand what you want to do.

 

[THREE05]

what I don't understand is how to get around the limit on the were condition. it's just 255 characters limit, and to search ID, CustName, EntryDate, Deliveried Date, it exedes the limit on there....????

 

[sneuberg]

I don't thing you need to get around the 255 character limit. That would be one huge where condition. Can you show me a 255 character where condition?

 

[sneuberg]

Did you try the find record method I described?

 

[THREE05]

[ID] = IIf(IsNumeric([forms]![SearchF]![txt1]),[forms]![SearchF]![txt1],Null) OR [CustName] Like "*" & [forms]![SearchF]![txt1] & "*"

plus DeliveryDate
Plus EntryDate
Plus like one more criteria that I would like to search.. I have thousands of records That's why I would like to set all these search options... Thanks for your help...

 

[sneuberg]

Ok, maybe that wouldn't be enough for all that. The way to get around the limit is to put the condition in VBA code where according to https://msdn.microsoft.com/en-us/library/office/ff197651.aspx the limit is 32,768 characters. In that case you would change the button to run VBA. The on click event code of the button to apply the filter you have so far the code would be

DoCmd.ApplyFilter , "[ID] = IIf(IsNumeric([Forms]![SearchF]![txt1]), [Forms]![SearchF]![txt1], Null) Or [CustName] Like ""*"" & [Forms]![SearchF]![txt1] & ""*"""

Note that the where condition here is in double quotes so now wherever there was a double quote before they have been replaced by two double quotes.

What's wrong with the find record method I told you about? Just add a button to the form, in the Command Button Wizard, choose Category Record Navigation and Action: FindRecord. When you click the button you get a pop up (See attached Screen Shot) that does what I think you want.