Query - data type mismatch in criteria expression

[Mihail]

Hi all !
I have trying to provide some help to this thread

http://bytes.com/topic/access/answer...ue#post3706190

but I have fall myself in trouble :) .

I design a query where I have trying to use a Public Function in the criteria row.
This function seems to return exactly the same expression as I see in the criteria row if I use certain numbers as criteria. Unfortunately this not work.
Can someone check that and teach me what data type must be used for my function ?

I have attache this very small database, because I can't explain very well what happen. And not because my poor English. Because I have no idea.

[MikeTheBike]

Hi

What you are doing will not work (comaring a string to a numic field).
I would do it this way.
This code goes in the frmStaffAbilitieas form module

Option Compare Database
Option Explicit
 
Dim SQL As String
Dim strCriteria As String
 
Sub BuildSQL(ByVal Criteria As String)
    SQL = "SELECT DISTINCT tblStaff.Name_Surname, tblAssignedAbilities.Abilitie_ID "
    SQL = SQL & "FROM tblStaff INNER JOIN "
    SQL = SQL & "tblAssignedAbilities ON "
    SQL = SQL & "tblStaff.Staff_ID = tblAssignedAbilities.Staff_ID "
    SQL = SQL & Criteria
End Sub
 
Private Sub cmdRequery_Click()
    Dim i As Integer
 
    strCriteria = ""
 
    With Me.lstAbilities
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                strCriteria = strCriteria & " OR Abilitie_ID = " & .ItemData(i)
            End If
        Next i
    End With
 
    If strCriteria <> "" Then
        strCriteria = "WHERE " & Mid(strCriteria, 5)
    End If
    BuildSQL (strCriteria)
    Me.RecordSource = SQL
    Me.Requery
 
End Sub
 
Private Sub Form_Open(Cancel As Integer)
    BuildSQL ("")
    Me.RecordSource = SQL
    Me.Requery
 
End Sub

HTH

MTB

[Mihail]

Thank you for reply, Mike.
I already solve that in this way (using SQL in VBA).

Look here:
http://bytes.com/topic/access/answer...ue#post3706190

But SQL is not my best friend.
So I like to solve that by passing parameter to query (as I try in the attached database).

[NeoPa]

You need to post at least the code of the function Mihail. Downloading databases is a PitA, and should only be asked when necessary. This doesn't seem to be such a case.

[Mihail]

I post the database in order to preserve time for every one which wish to help me in finding a solution. Not to preserve my time.
In attached pics are the definitions for tables and the "in trouble" query.

The SQL for this query is:

SELECT tblStaff.Name_Surname, tblAssignedAbilities.Abilitie_ID
FROM tblStaff INNER JOIN tblAssignedAbilities ON tblStaff.Staff_ID = tblAssignedAbilities.Staff_ID
WHERE (((tblStaff.Staff_ID)=WhereClause()));

Also I have a form, frmStaffAbilities (for VBA it's name is Form_frmStaffAbilities) which has as record source the query qStaffAbilities.

Also, in this form, I have a list box named lstAbilities. It is a Multi Select list box.
The row source for this list box is:

SELECT [tblAbilities].[Abbilitie_ID], [tblAbilities].[Abilitie] FROM tblAbilities ORDER BY [Abilitie];

Finally the function WhereClause()

Public Function WhereClause()
Dim WhereSQL As String 'Store the Where clause
 
Dim FirstItem As Boolean 'True until first selected item is find in lstAbilities
    FirstItem = True
 
Dim i As Integer, IDabil As Integer 'Work variables
 
On Error GoTo ErrorHandler
    For i = 0 To Form_frmStaffAbilities.lstAbilities.ListCount - 1 'Scan lstAbilities
        If Form_frmStaffAbilities.lstAbilities.Selected(i) Then
            IDabil = Form_frmStaffAbilities.lstAbilities.Column(0, i) 'Store ID_Abilitie
            If FirstItem Then
                WhereSQL = IDabil
                FirstItem = False
            Else
                WhereSQL = WhereSQL & " Or " & IDabil
            End If
        End If
    Next i
 
    If FirstItem Then 'Nothing is selected
        WhereClause = "Like ""*""" 'Not working
    Else 'There are selected items
        WhereClause = WhereSQL
    End If
 
Ex:
    MsgBox (WhereClause)
'    Debug.Print WhereClause
Exit Function
 
ErrorHandler:
    Select Case Err.Number
        Case 2427 'You entered an expression that has no value - When the form is first loading
            WhereClause = "Like ""*""" 'Not working
        Case Else
            MsgBox ("MyMesge   " & Err.Number & " " & Err.Description)
    End Select
 
    Resume Ex
End Function

Also I try a very simplified function:

'Simplified function:
Public Function WhereClauseSimple()
    WhereClause = 1 'Working
    WhereClause = 2 'Working
    WhereClause = 3 'Working
 
    WhereClause = "Like ""*""" 'Not working
    WhereClause = "1 Or 2" 'Not working
    MsgBox (WhereClause)
End Function

[Mihail]

Well, this approach do the job even if steel is not an answer to the main question.

The key is to create an extra field in query:

SELECT DISTINCT tblStaff.Name_Surname, IsSelected([Abilitie_ID]) AS SelectedID
FROM tblStaff INNER JOIN tblAssignedAbilities ON tblStaff.Staff_ID = tblAssignedAbilities.Staff_ID
WHERE (((IsSelected([Abilitie_ID]))=True))
ORDER BY tblStaff.Name_Surname;

The code for IsSelected() function is:

Public Function IsSelected(ID As Long) As Boolean
On Error GoTo ErrorHandler
Dim i As Long, SomethingIsSelected As Boolean
 
'    This are false by default but to be sure
    SomethingIsSelected = False
    IsSelected = False
 
    For i = 0 To Form_frmStaffAbilities.lstAbilities.ListCount - 1
        If Form_frmStaffAbilities.lstAbilities.Selected(i) Then
            SomethingIsSelected = True
            If Form_frmStaffAbilities.lstAbilities.Column(0, i) = ID Then
                IsSelected = True
Exit Function
            End If
        End If
    Next i
'    No items muched criteria
    IsSelected = Not SomethingIsSelected 'If nothing is selected then show all items
Exit Function
 
ErrorHandler:
    Select Case Err.Number
        Case Else
            MsgBox (Err.Number & " " & Err.Description)
    End Select
    IsSelected = False
End Function