Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Feed criteria to query from a form?

1 view
Skip to first unread message

mlh, eit

unread,
Jan 29, 2002, 6:07:26 PM1/29/02
to
SELECT DISTINCTROW Employees.LastName, Employees.EmployeeID
FROM Employees
WHERE
((Employees.EmployeeID=[Forms]![frmPayrollAnalyses]![WhichEmployee]));


I use the above SQL as the basis for a query that feeds data to a
report. The query gets a criteria (long integer) from a textbox
control on frmPayrollAnalyses. If I do NOT want to limit the chosen
records to a particular employee ID, what value should I put in the
form's textbox control?

I've tried leaving it empty. I've tried Like "*". Nothing I've tried
lets the query return all the records in Employees table.
Any suggs???

Jim Allensworth

unread,
Jan 29, 2002, 6:39:10 PM1/29/02
to
On Tue, 29 Jan 2002 23:07:26 GMT, "mlh, eit" <Shak...@Triad.rr.com>
wrote:

How about dropping the Where clause entirely from the query and use
the Where Condition of the OpenReport method to limit or not limit the
records returned?

--
Jim Allensworth
jballens...@attbi.net
(drop _not)


mlh, eit

unread,
Jan 30, 2002, 12:07:17 AM1/30/02
to
I am using Access 2.0. It has an OpenReport action, but
no OpenReport method. HELP describes its syntax for use
as DoCmd OpenReport reportname [, view] [, filtername] [,
wherecondition].

I'll try something like:
If the value of textbox on form is this,
then DoCmd OpenReport,
"MyReport",,,"Employees.EmployeeID=[Forms]![frmPayrollAnalyses]![WhichEmployee]"
Else
DoCmd OpenReport,"MyReport"
End If

... and see what happens. I assume that's what you mean.
Hopefully, it'll work. But, I'd still like to know how to do it with
the query. It seems there should be a way. I hate being stumped.
Thx 4 sugg.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


On Tue, 29 Jan 2002 23:39:10 GMT, "Jim Allensworth" <n...@here.com>
wrote:

Jim Allensworth

unread,
Jan 30, 2002, 12:35:12 PM1/30/02
to
No, construct the WhereCondition like...

DoCmd.OpenReport, "MyReport", , , _
"Employees.EmployeeID=" & Me.WhichEmployee

Note, if EmployeeID is a text value you would need to wrap the value
with single quotes.

On Wed, 30 Jan 2002 05:07:17 GMT, "mlh, eit" <Shak...@Triad.rr.com>
wrote:

--

Richard Bernstein

unread,
Jan 30, 2002, 7:00:27 PM1/30/02
to
> Hopefully, it'll work. But, I'd still like to know how to do it with
> the query. It seems there should be a way. I hate being stumped.

Concatenate a where clause if the text box has a value:

strSQL = "SELECT LastName, EmployeeID FROM Employees"
strWHERE = ""
If len(txtEmployeeID & "") > 0 then
strWHERE = " WHERE EmployeeID=[Forms]![frmPayrollAnalyses]![WhichEmployee]"
End If

strSQL = strSQL & strWHERE

Richard Bernstein

Martha

unread,
Jan 30, 2002, 7:42:17 PM1/30/02
to

To do this in the query itself, you can capitalize on the fact that
EmployeeID is an integer (and presumably greater than zero):

...WHERE (IIF([Forms]![frmPayrollAnalyses]![WhichEmployee] Is Null,
Employees.EmployeeID>=0,


Employees.EmployeeID=[Forms]![frmPayrollAnalyses]![WhichEmployee]));

Or something like that. (You'll have to deal with the case where the
user selects a name, then changes his mind and deletes it--the control
will look blank, but unless you refresh, the value will still be the
previously chosen EmployeeID.)

HTH,

Martha

Larry Linson

unread,
Jan 30, 2002, 10:43:03 PM1/30/02
to
"mlh, eit" <Shak...@Triad.rr.com> wrote

You said that you would try:

> then DoCmd OpenReport,"MyReport", , , "Employees.EmployeeID=


> [Forms]![frmPayrollAnalyses]![WhichEmployee]"

It is unlikely that will work because it will result in invalid
syntax.

If the EmployeeID is a numeric field, use instead

DoCmd OpenReport,"MyReport", , , "Employees.EmployeeID= " &


[Forms]![frmPayrollAnalyses]![WhichEmployee]

If the Employee ID is a text field, use instead

DoCmd OpenReport,"MyReport", , , "Employees.EmployeeID= """ &
[Forms]![frmPayrollAnalyses]![WhichEmployee] & """"

The extra/moved quotation marks are needed so you construct the
wherecondition with the content of the form field rather than text
describing its name.

mlh, eit

unread,
Jan 30, 2002, 10:34:28 PM1/30/02
to
That's a good suggestion if the question was
"How do I construct SQL from within code to
return all records or just those for a specific
employee?"

However, the question I'm asking is: What could
I put in the referenced form's textbox control that
might 'fool' the query object into thinking/acting
the way it would if it had NO criteria.

The fact is, the query DOES have criteria. I was
hoping that something in the textbox might make
the query act as if it did NOT. However, that's
seeming less 'n less likely to be the case. It just
struck me as being so natural to assume that,
based on the contents of a textbox, a QBF object
could be made to return ALL or SOME of the
table's records. Maybe not.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


On 30 Jan 2002 16:00:27 -0800, richardb...@bernstein.net (Richard

mlh, eit

unread,
Jan 30, 2002, 10:03:32 PM1/30/02
to
Thx for sugg. I'll try that. The syntax I tried last nite
that turned out to work was:
DoCmd OpenReport "rptPayrollAnalysesReport", A_NORMAL, , "[EmployeeID]
= Forms!frmPayrollAnalyses!WhichEmployee"
It looks pretty much like what you recommended.

Thx again.
ShakeMan.

On Wed, 30 Jan 2002 17:35:12 GMT, "Jim Allensworth" <n...@here.com>

mlh, eit

unread,
Jan 30, 2002, 10:57:54 PM1/30/02
to
On 30 Jan 2002 19:43:03 -0800, larry....@ntpcug.org (Larry Linson)
wrote:

Here's what I actually ended up using...

DoCmd OpenReport "rptPayrollAnalysesReport", A_NORMAL, , "[EmployeeID]
= Forms!frmPayrollAnalyses!WhichEmployee"

It works, filtering out all other records, passing only those records
for a single employee.

mlh, eit

unread,
Jan 30, 2002, 10:49:07 PM1/30/02
to

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thx, Martha, for suggestion. Tried several variations
of your technique. Couldn't get any of 'em to work. I
think its a pipe-dream - one of those rare instances
of something that seems so natural on the surface
turns out to be totally unrealistic.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Richard Bernstein

unread,
Jan 31, 2002, 3:48:50 PM1/31/02
to
Not a pipedream. Try your choice of the following, depending on
whether you want to use 0 or null as the indicator to return all
records.

WHERE IIF(IsNull([Forms]![frmPayrollAnalyses]![WhichEmployee]),
EmployeeID>0,EmployeeID=[Forms]![frmPayrollAnalyses]![WhichEmployee]).

WHERE IIF([Forms]![frmPayrollAnalyses]![WhichEmployee]>0,
EmployeeID=[Forms]![frmPayrollAnalyses]![WhichEmployee],EmployeeID>0,).

To build this using QBE, construct your basic query without criteria.
Then either 1) enter the IIF statement into a blank Field with <>False
in the criteria box, or 2) enter the entire WHERE clause directly in
SQL view.


Richard Bernstein

"mlh, eit" <Shak...@Triad.rr.com> wrote in message news:<l7fh5u4sb5mrfgd6q...@4ax.com>...

mlh, eit

unread,
Jan 31, 2002, 11:38:09 PM1/31/02
to
Doggone-it-all! I really want this to work. I know you guys are
trying to help me out. I'm trying all the suggs - to no avail!
Richard, when I try the 2nd of the two SQL snippets below
into the QBE grid, here's what Access does to my entry:

IIf([Forms]![frmPayrollAnalyses]![WhichEmployee]>0,"EmployeeID"=[Forms]![frmPayrollAnalyses]![WhichEmployee],"EmployeeID">0)

Notice the quotation marks it inserted around "EmployeeID". I have
to use [Employees].[EmployeeID] to avoid this, but regardless of
whether I have a zero in the control or a valid employeeID in the
control, neither case returns any records. Am I missing something?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On 31 Jan 2002 12:48:50 -0800, richardb...@bernstein.net (Richard

Richard Bernstein

unread,
Feb 1, 2002, 10:41:35 AM2/1/02
to
"mlh, eit" <Shak...@Triad.rr.com> wrote in message news:<ho5k5ukpodv0dg2t9...@4ax.com>...

> Doggone-it-all! I really want this to work. I know you guys are
> trying to help me out. I'm trying all the suggs - to no avail!
> Richard, when I try the 2nd of the two SQL snippets below
> into the QBE grid, here's what Access does to my entry:
>
> IIf([Forms]![frmPayrollAnalyses]![WhichEmployee]>0,"EmployeeID"=[Forms]!
>[frmPayrollAnalyses]![WhichEmployee],"EmployeeID">0)
>


My sincere apologies, I obviously failed to include the brackets
around the fieldname, thus it should be:

IIf([Forms]![frmPayrollAnalyses]![WhichEmployee]>0,[EmployeeID]=[Forms]!
[frmPayrollAnalyses]![WhichEmployee],[EmployeeID]>0)

I really, really hope this works for you. Similar statements work for
me in Access97.

Richard Bernstein

mlh, eit

unread,
Feb 2, 2002, 8:49:07 AM2/2/02
to
I'll try it in Access 97. Can say for certain that
Access 2.0 pukes on it. Thx for assistance.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On 1 Feb 2002 07:41:35 -0800, richardb...@bernstein.net (Richard

Clive Bolton

unread,
Feb 2, 2002, 3:18:16 PM2/2/02
to
There was a change in how Access handled form references between A2 and A97
(?A95). In A2 it was necessary to specify a property even if it was the
default property, whereas A97 would use the default.

So:
IIf(nz(Forms!frmPayrollAnalyses .Form !WhichEmployee)>0,EmployeeID=Forms!
frmPayrollAnalyses .Form !WhichEmployee,EmployeeID>0)

But although A2 should not choke on it, I do not think that it will do what
you want. I would create the SQL string in VBA, and write it back to the
stored querydef, or as a recordsource with the = sign. Something like :

lngEmpID = NZ(Forms!frmPayrollAnalyses .Form !WhichEmployee)
strSql = "SELECT Employees.LastName, Employees.EmployeeID" & _
" FROM Employees"
If lngEmpID >0 Then
strSql = strSql & "WHERE (Employees.EmployeeID=" & lngEmpID & ")
End If
strSql = strSql & " ORDER BY Employees.EmployeeID;"

Clive


"mlh, eit" <Shak...@Triad.rr.com> wrote in message

news:8ern5u8h161nran9d...@4ax.com...

mlh, eit

unread,
Feb 2, 2002, 6:17:09 PM2/2/02
to
You're right, Clive. What I ended up doing was to
allow the OpenReport Action to limit the records
returned utilizing the Report Filter part of the DoCmd
OpenReport "My Report", A_NORMAL, "Report Filter"
syntax. I was just trying to make Access 2 do something
I "just knew it would do" - but, you know how that goes.
Thx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
0 new messages