Criteria using an unbound box

[Guest]

I have a query to select records for printing a report. Sometimes I want all
the records, and sometimes I want a particular one. To choose the particular
one, I enter the ID in an unbound box on the form. When that box is null, I
want all of them. I put the following criteria in the query:

nz([Forms]![Main]![Print_form].[Form]![PrintIDtoPrint],">1")

This works when I enter the ID in the box, but when the box is null, the
">1" returns no records. If I put >1 by itself in the Criteria it returns
all the records, and if I type a valid record number in the nz function in
place of the ">1" it returns that record. Is there any way to make this
work? Thanks.

David

 

[John W. Vinson]

I have a query to select records for printing a report. Sometimes I want all
the records, and sometimes I want a particular one. To choose the particular
one, I enter the ID in an unbound box on the form. When that box is null, I
want all of them. I put the following criteria in the query:

nz([Forms]![Main]![Print_form].[Form]![PrintIDtoPrint],">1")

This works when I enter the ID in the box, but when the box is null, the
">1" returns no records. If I put >1 by itself in the Criteria it returns
all the records, and if I type a valid record number in the nz function in
place of the ">1" it returns that record. Is there any way to make this
work? Thanks.

You can pass a VALUE from an expression like this - but you cannot
pass an operator such as >.

Try

=[Forms]![Main]![Print_form].[Form]![PrintIDtoPrint] OR
[Forms]![Main]![Print_form].[Form]![PrintIDtoPrint IS NULL

John W. Vinson [MVP]

 

[Marshall Barton]

I have a query to select records for printing a report. Sometimes I want all
the records, and sometimes I want a particular one. To choose the particular
one, I enter the ID in an unbound box on the form. When that box is null, I
want all of them. I put the following criteria in the query:

nz([Forms]![Main]![Print_form].[Form]![PrintIDtoPrint],">1")

This works when I enter the ID in the box, but when the box is null, the
">1" returns no records. If I put >1 by itself in the Criteria it returns
all the records, and if I type a valid record number in the nz function in
place of the ">1" it returns that record.

Query parameters can only provide values. They can not an
operator.

One way to do what you want:

Forms!Main!Print_form.Form!PrintIDtoPrint OR
Forms!Main!Print_form.Form!PrintIDtoPrint Is Null

A better way is to use a button on the form to open the
report. This way, the report's record source query would
not have this criteria at all. Instead, use the OpenReport
method's WhereCondition argument to filter the report. The
code behind the button would be something like:

Dim stWhere As String
With Me.Print_form.Form!PrintIDtoPrint
If Not IsNull(.Value) Then
'if [your field] is a numeric type
stWhere = "[your field] = " & .Value
'if [your field] is a Text field
' stWhere = "[your field] = """ & .Value & """"
End If
End With
DoCmd.OpenReport "your report", , , stWhere