Show a report filtered by text fields of a form

  • Thread starter Thread starter Jose Garcia
  • Start date Start date
J

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
 
Jose said:
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.
Click to expand...


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

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

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 said:
Marshall Barton said:
You need to replace the generic names with the real report,
field, form and control names. The names must be spelled
correctly.
Click to expand...
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.
Click to expand...


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.
 
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.
Click to expand...

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 said:
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.
Click to expand...

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
Click to expand...


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.
 
Back
Top