Solved Multiple search engine with dropdowns

[zelarra821]

can you post an updated version. The last version fails again when I try to scroll. The query is missing again.

I will wait until you get this working as you like.

3. One thing that would have made this much simpler and more efficient.
You save keys for formato, autor, genero, subgenero, ...
You should be doing the filters on those keys and not the text. The forms rowsource should have both. Display the text and filter on the key. Unfortunately you named all your keys ID. So in the query you need to alias them

select testados.ID as Estados_ID, testados.Estados,... tFormatoID as FormatoID

The comboxes should return ID. You will never have to worry about accent. Only need this for the FAYT and textbox.

However I think you changed it to cmbofilter which you could try frm.cmboFilter
Again this may be centralizing too much. Is the next form going to have cmboFilter
You should be able to do the same in the filter requery

However whenever you do activecontrol, activeform ... It is not very precise. This all depends on focus, so I try to avoid this when I can. Keeping it in the form and using ME ensures you return the correct thing. Again in my opinion you are better off not centralizing it. This code is pretty unique to a certain type of form.

Sorry for not uploading a corrected version. I told you I would and in the end I forgot. If I have taken so long to answer you, it is because I have been since you wrote to me at noon, Spanish time, applying what you have taught me, and I have just finished now.

I have changed the IDs to as you have told me.

The database works as I want, except for 3 problems:

1. I had this error before and it was solved with a FName.ActiveControl.Requery, but it fails again: you start the form, go to the IDEstados drop-down, choose Comprar, and it shows you the Borrado records. No matter how much I think about it, I can't find the solution.

2. In the FBuscadorDeLibros form, in CmdFiltrarPorNumero_Click, it doesn't work well this line:

If Not Screen.PreviousControl.Name = "Lines" Or Screen.PreviousControl.Name = "Pages" Then Exit Sub

It does not open the message when I am in the Pages field. If I reverse the order in the If, the opposite happens to me, it does not open the message when I am in the Lines field.

3. This is more accessory, but hey, since we are, I wonder if I had a solution. When changing the name of the IDs, and therefore the dependent column in the rowsources of the combos, before I established a help text using, for example, @; "Status" in the Format. Of course, this happens by having the third column as a dependent. If it were the first, it would come out. Can that be corrected in any way?

Also, I have applied FAYT and filters on two other forms (FAutores and FSubgeneros), with their own queries to filter the combos and their own functions, so as not to complicate the code. I don't know if it has gone from one extreme to the other. In FSubgeneros everything works fine, except what I told you above in point 1.

Centralize if it is reuseable and generic, but do not if you end up creating more difficulty.

Thank you very much for the lesson. I think I have yet to learn when is better and when not. I don't know where I could have created more difficulty without realizing it.

Thank you so much.

 

[zelarra821]

The database is online again.

 

[zelarra821]

And one last question. These two modules to create the filters do they also work with dates and times?

 

[MajP]

1. I had this error before and it was solved with a FName.ActiveControl.Requery, but it fails again: you start the form, go to the IDEstados drop-down, choose Comprar, and it shows you the Borrado records. No matter how much I think about it, I can't find the solution.

This was a mistake I made in the getFilterFromTextbox

2. In the FBuscadorDeLibros form, in CmdFiltrarPorNumero_Click, it doesn't work well this line:

     If Screen.PreviousControl.Name = "Lineas" Or Screen.PreviousControl.Name = "Paginas" Then
      FieldNumber = Screen.PreviousControl.Name
      DoCmd.OpenForm "FDialogoElegirDataType", , , , , acDialog
      Me.TxtNumber = InputBox("Introduce el valor")
      If Me.TxtNumber = "" Then Exit Sub
      ApplyFilterEstados Me, FAYTform
      UpdateDynamicQuery
    Else
      'Correct my spanish
      MsgBox "Primero debe seleccionar un campo numérico para filtrar por número (por ejemplo, Paginas, Lieas)", vbInformation
    End If

 

[MajP]

Yes. All of those functions call this function to get a properly formatted value.
If the control is bound or if it is formatted, Access can usually figure out the datatype. If you are using a textbox and put in a date, you may have to use the SQL_Datatype.
It also has a between variant to do a Between.

This function can be used whenever you write a sql string. I put the QueryUtilities model in all of my dbs.

ex "WHERE SomeDate = " & csql(me.txtDate)
that will automatically return
Where SomeDate = #11/11/2020#
No need to do
"where SomeDate = #" & format(me.txtDate, "MM/DD/YYYY) & "#"

You may want to modify this to add your function to remove the accents.

Public Function CSql(ByVal Value As Variant, Optional Sql_Type As SQL_DataType = sdt_UseSubType) As String
    'Can be used when the Value is subtyped. For example you pass a declared variable
    Const SqlNull       As String = "Null"
    Dim Sql             As String
    
    'If the Sql_type is not passed then use the data type of the value
    If Trim(Value & " ") = "" Then
      CSql = SqlNull
    Else
         If Sql_Type = sdt_UseSubType Then
           Select Case varType(Value)
             Case vbEmpty, vbNull
               Sql_Type = sdt_Null
             Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte
               Sql_Type = sdt_Numeric
             Case vbDate
               Sql_Type = sdt_date
             Case vbString
               Sql_Type = sdt_text
             Case vbBoolean
               Sql_Type = sdt_Boolean
             Case Else
               Sql_Type = sdt_Null
           End Select
         End If
        
        Select Case Sql_Type
           Case sdt_text
                 Sql = Replace(Trim(Value), "'", "''")
                 If Sql = "" Then
                     Sql = SqlNull
                 Else
                     Sql = " '" & Sql & "'"
                 End If
           Case sdt_Numeric
                 If IsNumeric(Value) Then
                  Sql = CStr(Value)
                 Else
                  MsgBox "Invalid data: " & Value & ". You specified a numeric data type", vbInformation
                  Exit Function
                 End If
           Case sdt_date
                 If IsDate(Value) Then
                     If Int(CDate(Value)) = Value Then
                        Sql = Format$(Value, "\#mm\/dd\/yyyy\#")
                     Else
                        Sql = Format$(Value, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
                     End If
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a date data type", vbInformation
                   Exit Function
                 End If
           Case sdt_Boolean
                 If Value = "True" Or Value = "False" Or Value = -1 Or Value = 0 Or Value = "Yes" Or Value = "No" Then
                   If Value = "True" Or Value = "Yes" Then Value = -1
                   If Value = "False" Or Value = "No" Then Value = 0
                   Sql = str(Value)
                   'debug.print Sql
                 Else
                   MsgBox "Invalid data: " & Value & ". You specified a boolean data type", vbInformation
                   Exit Function
                 End If
            Case sdt_Null
              Sql = SqlNull
        End Select
          CSql = Trim(Sql)
   End If
    
End Function

 

[zelarra821]

Everything works perfectly. I just need to know a little more about what you have done to apply it to its full potential to my databases.

1. Reports.

I have applied your function to create the filter for the reports, and it goes perfect.

2. @; "Estado".

I have found out that you have an optional parameter which is Column. This allows me to put 1 as a dependent column in the properties of the combo, and that explanatory text of the combo that it is appears to me; and in the function for the filter I put what is, for example in my case, 2. In this way, I get both.

3. You may want to modify this to add your function to remove the accents.

Well yes, but I don't know how to do it. Notice that when you have something like this:

“WHERE SomeText =’“ & Me.Text.1 & “’”

Accents must be removed from both SomeText and Me.Text1

If it is added in that function, it could already be removed from the GetFilterFromTextBox function.

4. Other uses.

a. Is it possible to use these functions to create filters that you use on calculated fields in a query? For example, in a query, I have a field whose value I want to find in another table or query, according to some criteria, would it be possible? Is it possible to put a query field instead of a form field?​

​

b. Is it possible to pass a value to it directly, without it being stored in a field?​

5. If the control is bound or if it is formatted, Access can usually figure out the datatype.

a. Bound: with this I think you mean the combos that I have in the header of the FBuscadorDeLibros form, and that do not have an associated table field.​

​

b: Formatted: I have no idea what you mean. If you can give me an example to know exactly what it is, I would appreciate it.​

6. Parameters of the functions.

a. What exactly is TheSQL_DataType for?​

​

b. What exactly is FieldName for?​

​

c. What exactly is NotCondition for?​

7. Functions of the mdlQueryUtilities module, I want to know if some can be used for something or not, or are they intermediate in other functions, or maybe some of them are to delete.

a. I think what CSql does is format the value, based on the type of data you choose.​

​

b. The TestInsertQryValues function I have no idea what it's for.​

​

c. The CreateInsertQueryValues function I have no idea what it's for.​

​

d. The CreateInsertQuery function I have no idea what it's for.​

​

e. The TestUpdateQuery function I have no idea what it's for.​

​

f. The CreateUpdateQuery function I have no idea what it's for.​

​

g. The ParamInsert function I have no idea what it's for.​

​

h. The TestParamInsert function I have no idea what it's for.​

8. Finally, do you want the database with the tweaks that I have done so that everything works well? Quiet, I have not touched the functions, I only needed to add IDEstado, a line of code so that it does not error when you delete all the search bar and little else.

Thanks a lot.

 

[MajP]

2. @; "Estado".

I have found out that you have an optional parameter which is Column. This allows me to put 1 as a dependent column in the properties of the combo, and that explanatory text of the combo that it is appears to me; and in the function for the filter I put what is, for example in my case, 2. In this way, I get both.

I did not know that so that is something good to know.

3. You may want to modify this to add your function to remove the accents.
Well yes, but I don't know how to do it. Notice that when you have something like this:
“WHERE SomeText =’“ & Me.Text.1 & “’”
Accents must be removed from both SomeText and Me.Text1
If it is added in that function, it could already be removed from the GetFilterFromTextBox function.

If you wanted to I would do this in the convert to sql CSql function. I forgot the name of your function put in this part

Select Case Sql_Type
           Case sdt_text
                 Sql = Replace(Trim(Value), "'", "''")
                 If Sql = "" Then
                     Sql = SqlNull
                 Else
                     Sql = " '" & Sql & "'"
                 End If

this already handles a ' in the text

You could change this to

Select Case Sql_Type
Case sdt_text
Sql = Replace(Trim(Value), "'", "''")
Sql = RemoveAccents(Sql) 'call your function here
If Sql = "" Then
Sql = SqlNull
Else
Sql = " '" & Sql & "'"
End If

In the field to filter on the in query you would want a calculated control
EstadoNoAccent:RemoveAccent([Estado])


Now filter on estado no accent is simply
getFilterFromTextBox(me.SomeTextBox,"EstadoNoAccent")

However I am not certain when you can search for accents and when you cannot. If the field has accents and the user searches with accents I think it finds it OK. The problem is more when the data has accents and non Spanish users search they do not have a keyboard to use the accents. So if you have the ability to type with accents do you need to do this conversion? I think you would have to do this if you have users without the ability to type the proper accents.

5. If the control is bound or if it is formatted, Access can usually figure out the datatype.
a. Bound: with this I think you mean the combos that I have in the header of the FBuscadorDeLibros form, and that do not have an associated table field.
b: Formatted: I have no idea what you mean. If you can give me an example to know exactly what it is, I would appreciate it.

If you have an ubound text box and your format it as some kind of date (ex shortdate), or a numeric format, or a text format, it can figure out the sub datatype. Rarely do you have to specify the subdata type. But you can always specify what you want for the datatype. Ex. If you type in Yes it likely will think it is "Yes". If you specify it is boolean it will convert to -1.

Parameters of the functions.

a. What exactly is TheSQL_DataType for?
b. What exactly is FieldName for?
c. What exactly is NotCondition for?

TheSql_DataType is what I just described. If you type in a textbox it will likely figure it out. But in some cases "Yes" to be a boolean you need to specify. With the combo the default is that it can figure out the datatype from the bound column. There are cases where you do not want to use the bound column but a different column.

The fieldName is required for an unbound textbox. You need to specify what field to search. For the combo it can find the correct field name from the bound column. That is why this makes it real easy.
The not you can speciy how it compares and to use a Not
The not condition would do
"where Not Some Field >= 1" instead of "Where somefield >=1"

7. Functions of the mdlQueryUtilities module, I want to know if some can be used for something or not, or are they intermediate in other functions, or maybe some of them are to delete.

a. I think what CSql does is format the value, based on the type of data you choose.

b. The TestInsertQryValues function I have no idea what it's for.

c. The CreateInsertQueryValues function I have no idea what it's for.

d. The CreateInsertQuery function I have no idea what it's for.

e. The TestUpdateQuery function I have no idea what it's for.

f. The CreateUpdateQuery function I have no idea what it's for.

g. The ParamInsert function I have no idea what it's for.

h. The TestParamInsert function I have no idea what it's for.

All the "Test" are test for me to verify the functions

The create insert query values and create insert query values would allow you to do an insert query if you pass a lot of fields and vaules

Insert into sometable (Field1, Field2, Field2....) values ("ABC", 1, Null, #1/1/2020)
It makes those complicated values and fields with all delimiters
create update allows you to create and update query in a similar way
ParamInsert is a better way to do update queries using the parameters collection

I do not need the database. As I said I will be off line for a while.

 

[zelarra821]

However I am not certain when you can search for accents and when you cannot. If the field has accents and the user searches with accents I think it finds it OK. The problem is more when the data has accents and non Spanish users search they do not have a keyboard to use the accents. So if you have the ability to type with accents do you need to do this conversion? I think you would have to do this if you have users without the ability to type the proper accents.

In English you don't have the problem of accents. In Spanish, the problem is that people are very inconsistent with accents: sometimes they write them, other times not, and go to your place to find how accents discriminate against you. Even for me, I'm very freaky with the subject of tildes and I like to be very restrictive with that, some of them escapes me, and hence I want to remove the tildes both in the text I am looking for and in the field in which I search.

In the field to filter on the in query you would want a calculated control
EstadoNoAccent:RemoveAccent([Estado])


Now filter on estado no accent is simply
getFilterFromTextBox(me.SomeTextBox,"EstadoNoAccent")

That is what I wanted to avoid. If I do it by the source query of the form, and there are 1000 records, when I debug the code step by step using F8, it goes through the function to remove accents 1000 times. So sometimes I have this:

Dim strSearch As String

strSearch = fncRemoveAcents (Me.Estado)
Me.Filter = "fncRemoveAcents (Estado) = '" & strSearch & "'"
[/CODE

I don't know if I can convey what I mean.

That is, I have removed the accents from those 1000 records by VBA. So I don't know if it can be done with your functions.

[QUOTE="MajP, post: 1738114, member: 146445"]
All the "Test" are test for me to verify the functions
[/QUOTE]
If they're just tests, I'll delete them then.

[QUOTE="MajP, post: 1738114, member: 146445"]
As I said I will be off line for a while.
[/QUOTE]
Nothing happens. Thank you very much for your assistance. I wish you a Merry Christmas and a Happy New Year (¡Feliz Navidad y Feliz Año Nuevo!)

And nothing else, just tell you that you have this, as they say in some areas of Andalusia, "flame". Don't try to translate it.

 

[MajP]

In English you don't have the problem of accents. In Spanish, the problem is that people are very inconsistent with accents: sometimes they write them, other times not, and go to your place to find how accents discriminate against you. Even for me, I'm very freaky with the subject of tildes and I like to be very restrictive with that, some of them escapes me, and hence I want to remove the tildes both in the text I am looking for and in the field in which I search.

That makes sense so a user would like to do both. Include them or not include them.
The FAYT takes care of that because it does a like comparison always and replaces an accent with both.
The combo box does whatever you tell it and defaults to =, but you can tell it >=, >, like ....

You could do this without modify the function, but to make it easier I would give you a special
getFilterFromControlNoAccents
instead of returning
SomeField = "Ninos"
it will return a like
SomeField like "* with the replacemtns"

This way you do not have to modify the underlying query.

If they're just tests, I'll delete them then.

I would move them to the end of the code. These will help you understand you to use the functions. I put these in to help me remember how to use them
'--------------------------------------------------------------------- Demonstration Tests -------------------------------------------------
put the functions here.

 

[zelarra821]

You could do this without modify the function, but to make it easier I would give you a special
getFilterFromControlNoAccents
instead of returning
SomeField = "Ninos"
it will return a like
SomeField like "* with the replacemtns"

How can I do it?

One question: does a like comparison discriminate accents without having to go through a function to remove them?

 

[MajP]

Copy this into mdlControlFilters

'
'----------------------------------------------------------------- Special for Zelarra -------------------------------------------------------
Public Function GetFilterFromTextBoxNoAccents(ctrl As Access.TextBox, FieldName As String, Optional TheSQL_DataType As SQL_DataType = sdt_text, _
           Optional TheFilterType As FilterType = flt_LikeFromBeginning, Optional NotCondition As Boolean = False) As String
           Dim fltr As String
           Dim val As Variant
           If Not Trim(ctrl & " ") = "" Then
              val = ctrl.Value
             
             If TheSQL_DataType = sdt_text Then
                val = InternationalCharacters(val)
             End If
            
             fltr = GetSQL_Filter(TheFilterType, CSql(val, TheSQL_DataType))
             GetFilterFromTextBoxNoAccents = FieldName & " " & fltr
           End If
End Function

Private Function InternationalCharacters(ByVal strText As String) As String
   InternationalCharacters = strText
   'If you type international turn first to english
    'Type international and get english
    InternationalCharacters = Replace(InternationalCharacters, "á", "a")
    InternationalCharacters = Replace(InternationalCharacters, "é", "e")
    InternationalCharacters = Replace(InternationalCharacters, "í", "i")
    InternationalCharacters = Replace(InternationalCharacters, "ó", "o")
    InternationalCharacters = Replace(InternationalCharacters, "ú", "u")
    InternationalCharacters = Replace(InternationalCharacters, "ü", "u")
    InternationalCharacters = Replace(InternationalCharacters, "ñ", "n")
    'Add others as necessary á, é, í, ó, ú, ü, ñ
   

   'Type english and get international
    InternationalCharacters = Replace(InternationalCharacters, "A", "[AÁaá]")
    InternationalCharacters = Replace(InternationalCharacters, "E", "[EÉeé]")
    InternationalCharacters = Replace(InternationalCharacters, "I", "[IÍií]")
    InternationalCharacters = Replace(InternationalCharacters, "O", "[OÓoó]")
    InternationalCharacters = Replace(InternationalCharacters, "U", "[UuÚúÜü]")
    InternationalCharacters = Replace(InternationalCharacters, "N", "[NnñÑ]")

End Function

This is called like
MsgBox GetFilterFromTextBoxNoAccents(Me.Text2, "FieldX")

This makes a string that looks like
FieldX Like '*t[EÉeé]st'
so it will find either
FieldX Like '*tést'
or
FieldX Like '*test'

To answer you questions the like currently does not. The above function will.
A like with [...] says to match anything in the string. So as you can see it replaces both.



So this function is tailored to a like comparison of a textbox. You could have done this using the arguments, but this will be easier to use since the optional arguments are defaulted to what you want.

 

[zelarra821]

Thanks a lot, @MajP

 

[MajP]

discriminate accents without having to go through a function to remove them

I do not think you want to remove the accents because your data may or may not have accents. You want to do a like and check if it has or does not have accents for each character that could have accents. That is what the above and the FAYT does.

 

[zelarra821]

I do not think you want to remove the accents because your data may or may not have accents. You want to do a like and check if it has or does not have accents for each character that could have accents. That is what the above and the FAYT does.

I agree with you. That's what I wanted to say.

 

[MajP]

With your current setup all the comboboxes return ID which solves a lot of this issue.
In the future you could have a combobos with text such as a lookup. This is less likely where you would have to search for text. If this happens I would make a special function for that case. Modify the current function would put a lot of complexity for something not used that much. If necessary come back, or follow this example and copy and modify the combo box function.

Also if you pass in a multi select listbox it will build a string for each selection
Where someField in (1,15,23,67)
If you wanted to get more complex you could change your combos to multiselect listboxes. Then a user could pick both
Comprar and Borrado and...
or several Genero or several formats

 

[zelarra821]

I understand. Thanks a lot.

 

[zelarra821]

I am putting together everything I have learned and proposed in the database, and the more I try it, the more I like the result. I couldn't be more grateful to you for your selfless help. Not in a thousand years will I be able to give it back to you. Thank you very much with all my heart.

I hope you have a very good Christmas, and a very prosperous 2021. I wish you the best, and that we continue to meet in this virtual corner.

 

[MajP]

No problem. Now that I understand the database, it will be a lot easier to help you add and improve. I think the code can get streamlined. Also always try to do as much as you can in pure sql. Functions and expressions in code are very slow. I think my fix on repetidos helped. I think there are some other places like that.

After the Holidays. I will see if I can add this capability to your database. You may like it. Look at some of the images

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

It is a nice way to move around a big list with lots of levels.

 

[MajP]

See if this is better.

 

[zelarra821]

Still unfiltered category and subcategory. Look, follow these steps:

Estado -> Reproducido
TipoMultimedia -> Tipo5

If you display Categoria, you will see that if you add CountOfID, it comes out much more than 31. In fact, more categories appear.

Then, what I tell you about files and folders. What I want is the following to appear:

In Estado, when displaying a column appears with the number of TipoMultimedia that would exist for that Estado, and they appear broken down in the CodigoTipoMultimedia drop-down.
In TipoMultimedia, the same but for Categoria.
And in Categoria, the same but with Subcategoria.

Call it files / folders, or whatever you want. I do it to verify that I actually have that in the Windows file explorer. I don't know what will be the best way to show it.

Thanks!