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???
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)
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:
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:
--
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
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
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.
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
Thx again.
ShakeMan.
On Wed, 30 Jan 2002 17:35:12 GMT, "Jim Allensworth" <n...@here.com>
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.
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
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>...
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
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
On 1 Feb 2002 07:41:35 -0800, richardb...@bernstein.net (Richard
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...