Function works - except with a criteria! (1 Viewer)

[Les Isaacs]

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

 

[spikepl]

What is an error message doing in there? If you use this function in a query, and the function gets its input from the query in which it resides, then this is too late - it means you have stored data that you don't want. And in this case the function returns .... what exactly ?

Why don't you check your data on input?

 

[Les Isaacs]

Hi Spikepl

Thanks for your reply. I should perhaps have said that this function is still 'in embryo' - ultimately it will be used as part of a checking procedure to establish whether or not certain records exist in a certain table (and there will be many more 'cases' to add), with runtime unbound input from the user (hence the need to validate the input); but it will also be used in a particular query (where I agree there is no need to validate the input).

Hope that answers your question: any ideas on mine

Thanks again
Les

 

[spikepl]

Many : why this function. SInce you are opening and closing a recordset, what's wrong with Dcount?.

Besides , in Acces SQL there is a condition called EXISTS that you can use in a subquery - bye bye slow function

 

[Les Isaacs]

Hi Spikepl
I have found in the past that creating a function is simpler than repeating a Dcount on each occasion. In this case my function (once it works!) will also be expanded in various ways to allow alternative output to be generated. Finally, I have also found that opening a recordset is often much quicker than using Dcount, Dsum etc.
I'm reasonably certain that for my purpose a function is the way to go: but why can't I add a criteria for its output when used in a query?
Thanks again
Les

 

[spikepl]

I don't know. Someone might chime in, if you'd show the SQL in question.

D-functions open a recordset. so you are just doing the same thing in code. That's why I do not see the point, but then have fun with that

 

[CraigDolphin]

Occasionally I find that wrapping a problematic parameter query criteria in the Eval() function solves some problems. But I can't say whether it would work in your case or not. It's an easy thing to try and test though.

You might also need to add a NHSPsubmission = False line into your code where your message box gets triggered. The function must return a true or false whatever happens since you've declared it as returning a boolean.

 

[Les Isaacs]

Hi CraigDolphin

Many thanks for your reply.

I have amended the function to ensure that it returns 'False' when it doesn't return 'True', and I have tried using Eval in the query when I introduce the criteria ... but still no joy!

The query that works perfectly is:
SELECT Eval(NHSPsubmission([staff_name],"SD55(T)")) AS [SD55(T)submitted?], [qry x all staff].[NI number]
FROM [qry x all staff];

and the query that hangs is:
SELECT Eval(NHSPsubmission([staff_name],"SD55(T)")) AS [SD55(T)submitted?], [qry x all staff].[NI number]
FROM [qry x all staff]
WHERE (((Eval(NHSPsubmission([staff_name],"SD55(T)")))=0));

The amended function is below.

Any ideas would be most gratefully received!!
Thanks again
Les

The amended function:
Public Function NHSPsubmission(employee As String, submissiontype As String) As Boolean
NHSPsubmission = False

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 = True
End If

Case Else
MsgBox ("Invalid submissiontype: you must enter SD55, SD55(T) or SS10!")
End Select
End Function

 

[jdraw]

It isn't clear what exactly you are trying to do. As spike said, and it appears you may be working with the same table in the query and the function.
You haven't shown us what [qry x all staff] is, and that may help.
I don't see why you couldn't have criteria such as

In ( "SD55", "SD55(T)", "SS10")
and get rid of the function. But then again you haven't shown all the pieces.

On a different topic, I'd recommend you adopt a naming convention that does NOT have embedded spaces or special characters in field and object names.

 

[Les Isaacs]

Hi Jdraw

Thanks for your further reply.

[qry x all staff] is a separate query that is used - successfully - for various things, and is handy as a 'pre-filter' for the new query that I now need.

The function will generally be used for 'on-the-fly' testing of the data in table [employee_submissions], and I had thought it would also be sensible to use the same function in the new query - so that I am entirely consistent. There is also another query where I will use the function (if/when it works!!). As I mentioned earlier, the function will in fact need more cases adding, plus a couple of other options (there will be a further parameter so that it can be used to obtain the value of a particular field / particular record), and again I'm keen to retain the consistency in the various 'on-the-fly' testing and the queries.

Hope that explains things a little better. I appreciate your suggestion about using an 'In' statement, but still think that for my current purpose the function would be a good solution.

I also appreciate your comments about my naming convention (or lack of!): it's not all my fault - I inherited some of this!

Thanks for any further help (surely there must be a simple fix?).
Les

 

[spikepl]

To find out what upsets the system, gut your function. Comment all contents out, see what happens. If that works, then uncomment some.

 

[CraigDolphin]

SELECT Eval(NHSPsubmission([staff_name],"SD55(T)")) AS [SD55(T)submitted?], [qry x all staff].[NI number]
FROM [qry x all staff]
WHERE (((Eval(NHSPsubmission([staff_name],"SD55(T)")))=0));

That where part looks wrong to me. When I referred to using the Eval function I thought you were using a value from a form as your parameter. But if you are using a simple value of 0 as your criteria, then the eval function won't help.

Although I'm not a great native reader of SQL, to my eye the SQL should look more like:

SELECT NHSPsubmission([staff_name],"SD55(T)") AS [SD55(T)submitted?], [qry x all staff].[NI number]
FROM [qry x all staff]
WHERE ((NHSPsubmission([staff_name],"SD55(T)")=0));

It would be helpful if you posted a copy of the db so we can get hands-on with the query and function together with the source data and try to figure out what's going on.

 

[Les Isaacs]

Ho Spikpl
Thanks for coming back on this.
I have done as you suggested and have established that the problem lies with the Set rsSubmissions statement. If I remove the two criteria from this that use the 'like' operator, everything's fine: if I include either one or the other of the two criteria that use the 'like' operator, I can run the subsequent query, with a criteria on the field that uses this new function, and I get the expected results - but it's very slow!

So clearly having two criteria in the Set rsSubmissions statement which use the 'like' operator is causing the problem. Perhaps it's all those quotes.

But now I'm stuck - do I have to abandon this?
Thanks as ever
Les

 

[Les Isaacs]

Hi CraigDolphin

I think your last post and mine crossed in the ether!

I agree the Eval shouldn't be relevant here, and have removed it.

Unfortunately I can't take you up on your kind offer of looing at the query and function together, as the accdb is massive and complex and it would take me ages to create a cutdown version ... that said, perhaps I could do it - I'll have a try: as Arnie once famously said "I'll be back"!!

Thanks again
Les

 

[CraigDolphin]

Quotes do look iffy. But I'm surprised the function works at all, with or without a parameter, if that's the problem. I try to use single quotes nested within the double quotes to try to keep things straight.

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 & "' & '*'));")

 

[spikepl]

Apparently "test" can be married into submission_type, which really screws things up, because a LIKE does not take advantage of indexing, and you are stuck with it. Cannot that "test" thing be kicked out into a separate column? Then you could get rid of "like", and take advantage of indexing.

In any case, I would not expect this thing to be fast, anyway. But to actually fail just becasue of some "like"? . BTW, your last bit can be simplified into " AND submission_type Not Like ""*test*"";")

Why do you extract employee_submissions.submission_entered if this is not used? Nothing is for free.

AS to why a combination of two "likes" fails, I have no clue :-(

 

[spikepl]

On more reflection , why is that criterion there at all? What else is there in the submission_type?

If you just say

submission_type= '" & whateveryouarelookingfor & "'" then that excludes the bits with "test" in them.

If there is more stuff in there, then perhaps the design should be revisited. Lumping different type of information together in one field has very evidently a cost.

 

[Les Isaacs]

Hi CraigDolphin

Thanks for your further reply.

I tried amending the quotes as you suggested, but then on compiling got an error message about 'test' not being recognised, so I then added extra quotes around that, but all to no avail.

As the problem seems to be due to using the 'like' operator I think I'll cut that out by expanding the Case statement to include all the exact values that will be sought. There will be quite a lot (~15) but I'm assuming this will avoid the problem I'm having?

If you have any other thoughts I would of course be interested, otherwise I'll proceed as above: probably should have done that in the first place - and was just being lazy!!

Thanks again
Les

 

[CraigDolphin]

My mistake...I thought test was a global variable or something...on reflection i see it is more likely to be string within your value. In which case:

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' & '*'));")

should hopefully resolve the issue.

Of course, this is just 'air-code'. As I said earlier, an example database makes it a lot easier to provide answers.

 

[Les Isaacs]

Hi Craig
I'm pretty certain that I replied to your last post last week, but seemingly something went wrong because my reply is now not there! So apologies for that: what I had said was that I had decided to get rid of the 'like' operator altogether by adding more 'cases' so that I can cover all the possible values of 'submissiontype'. Probably should have done this in the first place - just being lazy I guess!!
Thanks again for your help.
Les