Show a report filtered by text fields of a form

[Jose Garcia]

Hi,

I have a report which I want to filter depending of the values of two field
text of a form.

For that reason I have created a macro using the command OpenReport, where
in the filter condition field I have put:

[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 It doesnt run because, when I try to execute; it appears to me 2 text
boxs asking about these two field values.

I would be grateful if somebody could help me to solve this problem

Regards

Jose

 

[Marshall Barton]

I have a report which I want to filter depending of the values of two field
text of a form.

For that reason I have created a macro using the command OpenReport, where
in the filter condition field I have put:

[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 It doesnt run because, when I try to execute; it appears to me 2 text
boxs asking about these two field values.

You need to replace the generic names with the real report,
field, form and control names. The names must be spelled
correctly.

 

[Jose Garcia]

:

You need to replace the generic names with the real report,
field, form and control names. The names must be spelled
correctly.

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.

 

[Marshall Barton]

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.

 

[Jose Garcia]

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.

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

 

[Marshall Barton]

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.

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.