Les Isaacs
Registered User.
- Local time
- Today, 03:30
- Joined
- May 6, 2008
- Messages
- 184
Hi All
I have written the funftion below, which works fine and gives the expected results when used in a query ... until I add a criteria to the field that's using the function: doing this causes the query to hang, with the very helpful "too complex" message on debugging The criteria I added to the query was 'False: I tried substituting '0', and I have tried amending the function's output-type from boolean to integer, and to variant, but all to no affect (or is it effect).
Anyone any ideas?
Many thanks
Les
My function:
Public Function NHSPsubmission(employee As String, submissiontype As String) As Boolean
Select Case submissiontype
Case "SD55", "SD55(T)", "SS10"
Dim rsSubmissions As DAO.Recordset
Set rsSubmissions = CurrentDb.OpenRecordset("SELECT employee_submissions.employee, employee_submissions.submission_type, employee_submissions.submission_entered" & _
" from employee_submissions" & _
" WHERE employee= """ & employee & """ " & _
" AND submission_type like ""*"" & '" & submissiontype & "' & ""*"" " & _
" AND submission_type Not Like ""*"" & ""test"" & ""*"";")
If rsSubmissions.RecordCount = 0 Then
NHSPsubmission = False
Else
NHSPsubmission = True
End If
Case Else
MsgBox ("Invalid submissiontype: you must enter SD55, SD55(T) or SS10!")
End Select
End Function
I have written the funftion below, which works fine and gives the expected results when used in a query ... until I add a criteria to the field that's using the function: doing this causes the query to hang, with the very helpful "too complex" message on debugging The criteria I added to the query was 'False: I tried substituting '0', and I have tried amending the function's output-type from boolean to integer, and to variant, but all to no affect (or is it effect).
Anyone any ideas?
Many thanks
Les
My function:
Public Function NHSPsubmission(employee As String, submissiontype As String) As Boolean
Select Case submissiontype
Case "SD55", "SD55(T)", "SS10"
Dim rsSubmissions As DAO.Recordset
Set rsSubmissions = CurrentDb.OpenRecordset("SELECT employee_submissions.employee, employee_submissions.submission_type, employee_submissions.submission_entered" & _
" from employee_submissions" & _
" WHERE employee= """ & employee & """ " & _
" AND submission_type like ""*"" & '" & submissiontype & "' & ""*"" " & _
" AND submission_type Not Like ""*"" & ""test"" & ""*"";")
If rsSubmissions.RecordCount = 0 Then
NHSPsubmission = False
Else
NHSPsubmission = True
End If
Case Else
MsgBox ("Invalid submissiontype: you must enter SD55, SD55(T) or SS10!")
End Select
End Function