Show a report filtered by text fields of a form

"Marshall Barton" wrote:


> You need to replace the generic names with the real report,
> field, form and control names. The names must be spelled
> correctly.
>
> --
> Marsh
> MVP [MS Access]
>

Hi Marsh

The suggestion you gave me , it doesn´t run. I have put the correct names
but I think that the problem is that the report is based in a query with
parameters. This query has two parameters that the user put via input, to
filter the result, this two parameters are called 'number1' and 'sheet1'.

This two parameters are used also in the form as field text to obtain the
rows result ina a subform. Then in order to obtain, the same result in a
report, I try to put in WHERE condition :

[Name of the report field1]=[Forms]![FormName]![Name of the form field1] AND
[Name of the report field2]=[Forms]![FormName]![Name of the form field2]

But with no result, because it appears to me 2 text boxs asking about these
two field values. I think it is because the report is based in a query that
has two parameterized variables. And then Access gets confused, but I am sure
that I has to have a correct way to run this.

I would be very grateful if anybody could help me to solve this issue.

Regards.

Jose Garcia wrote:

>"Marshall Barton" wrote:
>> You need to replace the generic names with the real report,
>> field, form and control names. The names must be spelled
>> correctly.
>>
>>
>The suggestion you gave me , it doesn´t run. I have put the correct names
>but I think that the problem is that the report is based in a query with
>parameters. This query has two parameters that the user put via input, to
>filter the result, this two parameters are called 'number1' and 'sheet1'.
>
>This two parameters are used also in the form as field text to obtain the
>rows result ina a subform. Then in order to obtain, the same result in a
>report, I try to put in WHERE condition :
>
>[Name of the report field1]=[Forms]![FormName]![Name of the form field1] AND
>[Name of the report field2]=[Forms]![FormName]![Name of the form field2]
>
>But with no result, because it appears to me 2 text boxs asking about these
>two field values. I think it is because the report is based in a query that
>has two parameterized variables. And then Access gets confused, but I am sure
>that I has to have a correct way to run this.


If the form text boxes [Name of the form field1] and
[Name of the form field2] have the parameter values, then
the query parameters should be:
[Forms]![FormName]![Name of the form field1]
and
[Forms]![FormName]![Name of the form field2]

If you are using the OpenReport method's WhereCondition
argument to filter the report, then post a Copy/Paste of the
code that opens the report so I can see what you are doing.

If you would explain the real form, field and control names,
we could avoid the confusion of all these made up names.

--
Marsh
MVP [MS Access]

>
> If the form text boxes [Name of the form field1] and
> [Name of the form field2] have the parameter values, then
> the query parameters should be:
> [Forms]![FormName]![Name of the form field1]
> and
> [Forms]![FormName]![Name of the form field2]
>
> If you are using the OpenReport method's WhereCondition
> argument to filter the report, then post a Copy/Paste of the
> code that opens the report so I can see what you are doing.
>
> If you would explain the real form, field and control names,
> we could avoid the confusion of all these made up names.
>
> --
> Marsh
> MVP [MS Access]
>

Hi again,


I have a Query (called 'vigente') its SQL code is:

SELECT TOP 1 Plano.Fecha, Plano.Designacion, Plano.Descripcion,
Plano.Numero, Plano.Hoja, Plano.Revision, Plano.Fecha_ult_revision
FROM Plano
WHERE (((Plano.Numero)=[number2]) AND ((Plano.Hoja)=[hoja2]))
ORDER BY Plano.Revision DESC;


I have a Form (called 'buscar') with two field texts to filter the rows, the
result is shown in a subform based in the field texts mentioned before.

In this form there is a button, that by event runs a Macro , this Macro
executes the 'OpenReport' command, the WHERE condition is:

[Numero]=[Formularios]![buscar]![number2] Y
[Hoja]=[Formularios]![buscar]![hoja2]

Where [Numero] and [Hoja] are report text fields of 'InformeVigente' report

But as I have said you it doesn't run properly.

Regards

Jose Garcia wrote:

>>
>> If the form text boxes [Name of the form field1] and
>> [Name of the form field2] have the parameter values, then
>> the query parameters should be:
>> [Forms]![FormName]![Name of the form field1]
>> and
>> [Forms]![FormName]![Name of the form field2]
>>
>> If you are using the OpenReport method's WhereCondition
>> argument to filter the report, then post a Copy/Paste of the
>> code that opens the report so I can see what you are doing.
>>
>> If you would explain the real form, field and control names,
>> we could avoid the confusion of all these made up names.
>>
>> --
>> Marsh
>> MVP [MS Access]
>>
>
>Hi again,
>
>
>I have a Query (called 'vigente') its SQL code is:
>
>SELECT TOP 1 Plano.Fecha, Plano.Designacion, Plano.Descripcion,
>Plano.Numero, Plano.Hoja, Plano.Revision, Plano.Fecha_ult_revision
>FROM Plano
>WHERE (((Plano.Numero)=[number2]) AND ((Plano.Hoja)=[hoja2]))
>ORDER BY Plano.Revision DESC;
>
>
>I have a Form (called 'buscar') with two field texts to filter the rows, the
>result is shown in a subform based in the field texts mentioned before.
>
>In this form there is a button, that by event runs a Macro , this Macro
>executes the 'OpenReport' command, the WHERE condition is:
>
>[Numero]=[Formularios]![buscar]![number2] Y
>[Hoja]=[Formularios]![buscar]![hoja2]
>
>Where [Numero] and [Hoja] are report text fields of 'InformeVigente' report


You want the same fields to use bith a prompt parameter in
the query and the OpenReport's WhereCondition.

I think you should get rid to the criteria in the query and
use just the WhereCondition.

I don't use macros so I can't be sure, but if Numero and
Hoja are Text fields in the table, I think the
WhereCondition should be more like:

Numero=" '" & Formularios!buscar!number2 & "' Y Hoja='" &
Formularios!buscar!hoja2 & "' "

What you had would be the way to do it if both fields in the
table are number types.

--
Marsh
MVP [MS Access]