Multi field search-box

[MajP]

Looks like he's trying to search multiple fields. Radio buttons are not the way to go to do that. You need a form with all the fields you wish to search, and the user can put search criteria into multiple fields. Similar to what is described in this video:

No.

 

[Mike Krailo]

That's very interesting MajP. You forced me down another rabbit hole. Didn't know there was an ANSI type database but you are correct. How did you determine this out of curiosity?
Edit: Nevermind, I seen your other post on this.

 

[Uncle Gizmo]

I've gone about it a different way, using an existing product of mine, a "Search Form" Instead of a listbox the "Nifty Search Form" contains a subform.. You could call this a:-

Microsoft Access Multi-Field Search Form​

Search Form Example - Nifty Access

If you're interested in the having the "Nifty Search Form" itself for your own project it's one of my products and all of the setup instructions are here:- https://niftyaccess.gumroad.com/l/NiftySearchForm

 

[Mike Krailo]

That's pretty Nifty, Uncle Gizmo. I like how you made the selections for begin, anywhere, end to ramp it up a notch.

 

[Uncle Gizmo]

You need a form with all the fields you wish to search, and the user can put search criteria into multiple fields.

Yes I agree! See my post number 23 in this thread, took me just a few minutes to set it up, in fact I have added my proprietary "Nifty Search Form" to a database in 4 minutes! See Here:- https://www.niftyaccess.com/nifty-search-form/

 

[smtazulislam]

Great job. YourThanks for help.

Here is the solution

HOWEVER, there is something in here I had no idea about. Either on purpose or accident your database is in ANSI mode. So the filter is not

Like '*Text*'
but
Alike '%Text%'

You need to determine if that is what you really want. This may be a regional setting thing or comparability with SQL server.

your code is work what I wanted. But I afraid for MODE ANSI, If i lost my data.

 

[MajP]

If you did not intend for ANSI 92 then you can change it to 89 by

1. Click File > Options.
   The Access Options dialog box appears.
2. Click Object Designers, and in the Query design section, under SQL Server Compatible Syntax (ANSI 92), do one of the following:
   • Select This database to change the open database to the ANSI-92 standard.
     -or-
     Clear the check box to set the open database to the ANSI-89 standard.
   • Select Default for new databases to set all new databases created with the open instance of Access to the ANSI-92 standard.
     -or-
     Clear the check box to set all new databases to the ANSI-89 standard.
3. Click OK.

Top of Page

Then change the strval line of code.

'If you do not want ANSI 92 wildcards then use below line of code and change settings to 89
  'strVal = "LIKE '*" & Me.txtSearchbox.Text & "*'"
  'This is using ANSI 92 mode
  strVal = "ALIKE '%" & Me.txtSearchbox.Text & "%'"

 

[zeroaccess]

Makes my DoCmd.ApplyFilter search forms look downright prehistoric.

 

[arnelgp]

"ALike" was already discussed partly in 2018, https://www.access-programmers.co.u...ble-not-returning-values.300649/#post-1585012, post #5.

 

[smtazulislam]

If you did not intend for ANSI 92 then you can change it to 89 by


Then change the strval line of code.

'If you do not want ANSI 92 wildcards then use below line of code and change settings to 89
  'strVal = "LIKE '*" & Me.txtSearchbox.Text & "*'"
  'This is using ANSI 92 mode
  strVal = "ALIKE '%" & Me.txtSearchbox.Text & "%'"

YES, i was changed strVal = "LIKE '*" & Me.txtSearchbox.Text & "*'" and change object designers options ....
Its greats! Thank you so much MajP and others......

 

[Uncle Gizmo]

So you probably could be just a little more diplomatic.

You should give Adam lessons on diplomacy MajP ....