search criteria in a form fails!

[maxbre]

I have set up a search form to retrieve data on a table by adding some unbound controls as suggested by the following very nice tip http://allenbrowne.com/ser-62.html



All the searching controls I set up are working fine except for the one called “search_structure” which is supposed to look at a specific field called “cod_structure” in the table “register”;

in fact the field “cod_structure” is a combobox searching data into another table called “structures” set in the source row of the searching tab by the following sql:

SELECT structures.id_structure, structures.structure, structures.structure_acronym FROM structures ORDER BY structures.structure;

with column count = 3 and column width = 0 properties set in the corresponding search tab

Now, my problem is that if I set up a search criteria like the following
---------------------------------------------------------------------
If Not IsNull(Me.search_structure) Then
strWhere = strWhere & "([cod_structure] = " & Me.search_structure & ") AND "
End If

….some other code here ….

Me.Filter = strWhere
Me.FilterOn = True
----------------------------------------------------------------------
all is working fine because it is looking at the number field “id_structure” but then I get all mess up when I’m trying to set up a searching criteria looking to the correlated column “structure_acronym” which is supposed to be a text field

I’ve been trying by modifying the above code by
----------------------------------------------------------------------
If Not IsNull(Me.search_structure) Then
strWhere = strWhere & "([cod_structure.column(2)] = """ & Me.search_structure & """) AND "
End If
----------------------------------------------------------------------
but again it returns me an error (3126) because I’m not able to retrieve the data;
I suppose it has something to do with the similar thread already solved
https://www.accessforums.net/programming/storing-content-variable-9333.html

but I’m still not able to figure out what the problem is and a way to sort it out properly

thanks for any support

[maximus]

I hope this example helps you. I have a Form with two unbound text box. I want to filter my form either by EmpID which is a Number Field and EmpName which is a text Field. I have used the code below:

I have used two text Boxes:
Text22=EmpID
Text24=EmpName


Now I have used the code below to set my filter Criteria. varCriteria variable will hold the filter criteria:

Now when:

Text22 is Null but not Text24 Filter is Done only by EmpName
Text24 is Null but not Text22 Filter is Done only by EmpId
Text22 and Text24 both not null then Filter by Both
If both are Null then Msg prompt and Exit Sub

U will notice that EmpName is a text Field and the EmpID is a Number Field nad how I have used this code to generate the filter condition.



Dim varCriteria As Variant
varCriteria = Null
If IsNull(Me.Text22) Then
If IsNull(Me.Text24) Then
MsgBox "Please Type a Criteria"
Exit Sub
End If
End If
If Not IsNull(Me.Text22) Then
varCriteria = "[EmpID]=" & Me.Text22
End If
If Not IsNull(Me.Text24) Then
If IsNull(varCriteria) Then
varCriteria = "[EmpName]='" & Me.Text24 & "'"
Else
varCriteria = varCriteria & " And [EmpName]='" & Me.Text24 & "'"
End If
End If
Me.Filter = varCriteria
Me.FilterOn = True


if this solves your problem mark the thread solved

[maxbre]

dear maximus thank you very much for the reply
as far as I understand your code is working in a way similar to the one I've been using in my db but still not sorting out my problem which is just incidentally related to the treatment of a text field rather than a number
instead my problem is very much connected to the retrivial of data looking at the correlated column to "cod_struttura" (sorry but now I’ve switched to italian names because it’s simpler referring to the db attachment "test.zip")
in that small sample of the db attached please look at the form called “ms_search” and the unbound text box called “struttura; just for example fill in the search box struttura with the words “uia” and the apply the filter button and see resulted error
any help for this, please?
thanks again to anyone having the patience to look at my db sample