Solved Multiple search engine with dropdowns

[MajP]

I can see from the image that I forgot to add a space before the AND. Not sure why it was working for me. It should have failed. Add a space before AND in this line in the filterME
change to
If Not strFAYT = "" Then strFltr = strFltr & " AND (" & strFAYT & ")"

It now says
If Not strFAYT = "" Then strFltr = strFltr & "AND (" & strFAYT & ")"

Wouldn't it be better to reduce international characters to just String?

What do you mean by this?

 

[MajP]

one more thing. If you add two textboxes you can use this function to get a filter string
GetBetweenFilter(control1,control2)

 

[zelarra821]

What do you mean by this?

I tell you about international characters because it only applies to text, not numbers. You can't put a tilde in a number, can I explain? For example, número, nación, zarigüeya...

one more thing. If you add two textboxes you can use this function to get a filter string
GetBetweenFilter(control1,control2)

I don't understand what I can use it for. If you tell me an example, please.

 

[zelarra821]

I can see from the image that I forgot to add a space before the AND. Not sure why it was working for me. It should have failed. Add a space before AND in this line in the filterME
change to
If Not strFAYT = "" Then strFltr = strFltr & " AND (" & strFAYT & ")"

It now says
If Not strFAYT = "" Then strFltr = strFltr & "AND (" & strFAYT & ")"

It keeps giving error. I think you have to remove AND

 

[zelarra821]

I am attaching the database in case you were not doing something and that is why you did not get the error. What I do is type something in the search bar, and go through the three states of the checkbox. When I get to null, that's when it gives the error.

 

[MajP]

i don't understand what I can use it for. If you tell me an example, please.

If you have two textboxes with a Start Date and End Date and you want to get the values between those dates. I thought that was something you planned to incorporate.

I tell you about international characters because it only applies to text, not numbers. You can't put a tilde in a number, can I explain? For example, número, nación, zarigüeya...

The FAYT only works on text fields. Those fields could have numbers formatted as text. To check if the field is a number formatted as text would be longer than just running the replace.

 

[zelarra821]

If you have two textboxes with a Start Date and End Date and you want to get the values between those dates. I thought that was something you planned to incorporate.

Yes of course. For that I have to pass a text the two dates in the source query of the form, right?

The FAYT only works on text fields. Those fields could have numbers formatted as text. To check if the field is a number formatted as text would be longer than just running the replace.

Ok, perfect then.

 

[MajP]

When I get to null, that's when it gives the error.

I see the problem. I have to go out, but will fix when I get back.

 

[zelarra821]

I see the problem. I have to go out, but will fix when I get back.

The error is here when you activate the triple state:

  If IsNull(Me.EsSerie1) Then
    strSerie = ""
  Else
    strSerie = GetFilterFromControl(Me.EsSerie1, , sdt_Boolean, , "EsSerie")
  End If

It cannot take the value "", because then it is "empty" and that is when it gives the error, because the string that is passed to the filter begins with AND for FAYT has value. Nothing happens when the triple state is not active, because there are only two values. I have tried to put Null, and it gives error. And with 0 it doesn't filter well. I think the solution is to reverse the order:

If Not strFAYT = "" Then strFltr = strFltr & " AND (" & strFAYT & ")"

If Not strFAYT = "" Then if not strFltr = "" Then strFltr = strFltr & " AND (" & strFAYT & ")"

 

[zelarra821]

If Not strFAYT = "" Then if not strFltr = "" Then strFltr = strFltr & " AND (" & strFAYT & ")"

In this way it works, but I don't know if it is the best way. I have tested it with two checkboxes, and it works fine.

 

[zelarra821]

If you have two textboxes with a Start Date and End Date and you want to get the values between those dates. I thought that was something you planned to incorporate.

Could you confirm if I would have to do it through the source query of the form, using CStr at the two dates, or the numbers if I wanted to do it with numeric fields?

 

[MajP]

Could you confirm if I would have to do it through the source query of the form, using CStr at the two dates, or the numbers if I wanted to do it with numeric fields?

Only if you want it to work with the FAYT. If you are going to have controls to check the range then no. I think I fixed the other issue with AND and demoed a range control.

 

[zelarra821]

Only if you want it to work with the FAYT. If you are going to have controls to check the range then no.

Ok.


And what about the solution what I propose for the problem with checkboxes. Is it right?

 

[MajP]

And what about the solution what I propose for the problem with checkboxes. Is it right?

It is close. I did it a little different.

 

[zelarra821]

And how did you do it? Only for learning

 

[MajP]

Similar idea

  If strFltr <> "" And strFAYT <> "" Then
    strFltr = strFltr & " AND (" & strFAYT & ")"
  ElseIf strFltr = "" And strFAYT <> "" Then
    strFltr = strFAYT
  End If

 

[zelarra821]

I'm seeing, and, in the FilterMe function, strFAYT = FAYTform.Filter doesn't get the value of what you are typing in the search bar. It may be that is why it failed too.

 

[zelarra821]

The problem I think is in this part of the class module, which have no value:

Public Property Get Filter() As String
    Filter = mFilter
End Property

Public Property Let Filter(ByVal sNewValue As String)
    mFilter = sNewValue
End Property

 

[zelarra821]

Analyzing what I tell you, I realize that it works in this sense: Combos / CheckBoxes -> FAYT. If I use FAYT, that is, I enter text in the search bar, and then use Combos / Checkboxes, the filter does not include FAYT. It is logical: you have set FAYT in the FilterMe function, but I think it would be necessary to include FilterMe in FAYT. I don't know if this is what you were referring to that is complicated.

The only thing I can think of would be to put an optional parameter in FAYT to add the filter created with FilterMe.

 

[MajP]

The FAYT was never designed to work with forms that already had filters so I added this capability to the FAYT. Searching all fields could easily make the query to large for access to handle. You know have to pick a field to FAYT. This should work.
I had to get rid of the default message in the FAYT. If not this causes a loop.