Function as Query Parameter Not Working

[Dave]

Tried all day to fix this. I want to output some of a continuous
form's fields as an Excel file in an email. A filter is applied to
this form (as a string variable) when it opens, so I moved that
variable into a module which contains the ' getDeptList ' function and
made the variable global:
Global strDept As String.

The function is:
Public Function getDeptList() As String
getDeptList = strDept
End Function

The Dept field criteria in the query design is 'getDeptList()'. I
click on a button and invoke the query with:
DoCmd.SendObject acSendQuery, "qryTaskPartsDetail", acFormatXLS.

If I step through the code, the function is called (I assume by the
query). Just before the email opens, I can see the value of getDeptList
() in the immediate window is exactly what I expect:
?getDeptList
'1CCM'
When I open the attachment in the email, there are no records in it.
I've tried everything I can think of to fix this, to no avail. If I
substitute the literal '1CCM' in the query grid, I get records if I
run it. The WHERE part of the sql is:
WHERE (((tblRawData.DEPT_CD) In (getDeptList())) AND...

While I would like to know what's wrong, I have decided I can't use
this if I cannot use the In operator/function in the sql, as: In
(getDeptList())
Why is this query ignoring the function's returned value?
Is there a better way to send part of a continuous form output where a
record filter has been employed? I confess I don't know much about
creating recordsets and have thought about that. Thanks in advance.

 

[Jeff Boyce]

Dave

I may be missing something...

Which query gets that function?

Can you run the query independently (and get the right records)?

You mention trying to output "some of a ... form's fields..." -- remember
that the form doesn't hold the data, just displays it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

The IN operator can only be used with a literal value list, not a parameter.
However, you'll find a couple of ways of replicating its use with a parameter
at:


http://support.microsoft.com/kb/100131/en-us


If you use the InParam and GetToken functions the WHERE clause for the query
would be:

WHERE InParam(tblRawData.DEPT_CD, getDeptList()) = TRUE AND ……

The value list returned by the function must be a comma separated list, but
you do not need to delimit text values in it with quotes.

Another approach, rather than using a global variable would be to assign the
value list to a hidden text box control on the form when it opens and
reference this control in the query:

WHERE InParam(tblRawData.DEPT_CD, Forms!YourForm!txtHidden) = TRUE AND ……

Passing values via global variables is best avoided wherever possible as the
variable's value will be retained until a new value is assigned to it and
consequently might be exposed inappropriately, whereas the value in the
hidden text box dies with the form.

Ken Sheridan
Stafford, England

 

[Dave]

If you mean it's name, it's "qryTaskPartsDetail" as spec'd in the
openquery line of the docmd statement below. When I substituted a
literal value, I did get records (see orig. post - literal value was
1CCM); but not if the query was manually run with the function as the
criteria. In that case, it opened with only the query field headings.

You are correct about the form's behaviour; that's the main reason I
created a query that has less fields than the one the form is based
on. I only stated what I'm trying to achieve in case someone knows of
a better way, and inevitably, I figured someone might ask if I didn't
say as much. Does that help?
Thanks.

 

[Dave]

Ken; I'll try the second example posted there. What I don't want
though, is any prompting for parameter values. Hopefully I can avoid
that.

My global variable is in fact a delimited list with comma separated
values and single quotes, so I'm hopeful at the moment (e.g.
'1CCM,'EREP','GREP','WLDC'). For the form opening, it works along with
the IN statement, but that's because the form's recordset is a sql
string. BTW, I already tried assigning getDeptList() and strDept to a
form text box with no success. In both cases, I could see valid data
in the control, yet the query couldn't handle it.

I thought it would be a simple matter to make a function equal one or
more values from a delimited and quoted list and call the function via
a query. Arrgghh! I will post back after trying the example you noted.

 

[Ken Sheridan]

It should work fine with your function.

You won't be prompted for a value. Only a simple parameter such as [Enter
department:] does that. Parameters in developed applications are more
usually references to a control on a form or, as in your case, a function
which returns the value.

Ken Sheridan
Stafford, England

 

[Dave]

It should work fine with yourfunction.

You won't be prompted for a value.  Only a simple parameter such as [Enter
department:] does that.  Parameters in developed applications are more
usually references to a control on a form or, as in your case, afunction
which returns the value.

Ken Sheridan
Stafford, England

Ken; I'll try the second example posted there. What I don't want
though, is any prompting for parameter values. Hopefully I can avoid
that.

My global variable is in fact a delimited list with comma separated
values and single quotes, so I'm hopeful at the moment (e.g.
'1CCM,'EREP','GREP','WLDC'). For the form opening, it works along with
the IN statement, but that's because the form's recordset is a sql
string. BTW, I already tried assigning getDeptList() and strDept to a
form text box with no success. In both cases, I could see valid data
in the control, yet thequerycouldn't handle it.

I thought it would be a simple matter to make afunctionequal one or
more values from a delimited and quoted list and call thefunctionvia
aquery. Arrgghh! I will post back after trying the example you noted.

- Show quoted text -

I must confess that I reviewed the code and the supporting
documentation and was convinced the example would cause an input
prompt, so I elected to create a query and output that instead. This
was before I read your last note. I felt I had spent too much time
already to justify further efforts to defeat the problem. Maybe when I
put this to bed I can play around some more.
Thanks for your help.