use query parameter as a criteria in my report

[ced]

Hi,
newbie to access, I have created a report, based on a query: works
fine!
Now, I have added a text box in order to get an input from the enduser,
such as:
Month: ?
The query parameter is well displayed in the report (i.e.: January),
but how can I refer to this one, below in my report, as a criteria?
Such as summing nbr of record having their Invoice creation date =
"January" (the value keyed in by the end user when launching the
report)

Any help would be greatly appreciated.
C,

 

[OfficeDev18 via AccessMonster.com]

Hi, and welcome to Access,

There are many ways to skin the cat.

Add a column to your query, maybe called InvCreationMonth, and instead of a
textbox on a form, do it as a parameter entry on the query, as follows:

You already have an invoice date in your data table, maybe called InvDate, so
it's easy.

A - Add a new field in the query, maybe called InvMonth, as follows:

InvMonth: Month(InvDate)

In the criteria line for the InvMonth field, add the following:

CInt([Please Enter Month of Invoice])

Note the bracketing; it's critical. It will be looking for a numeric entry
between 1 and 12.

B - Add a new column to your query, maybe called InvCreationMonth, as follows:


InvCreationMonth: Format(InvDate, "mmmm")

In your report, then, you can either:

A - Add a new column/textbox and bind it to the new column in the query. This
will display "January" for every row in the report. Or you can

B - Add a textbox anywhere in the Report Heading Section of the report, and
set the Visible property to No. Bind it, as above, to the InvCreationMonth
column in the query. Now change the report heading to a textbox - it defaults
to type "Label" - and change the report heading to read "X Y Z Report for All
Invoices Created in the Month of " & Me.InvCreationMonth

By the way, don't be embarrassed about using the Help file. I've been doing
this for eight years and I consult the Help file liberally. And I'm not alone.


Hope this helps,

Sam

 

[cel]

Thanks for your answer, however I wish to avoid having to add field to
either
my table or queries. Any chance to assign the value of the parameter to

a variable that I can then reuse somewhere else in my report for
testing purposes?
Thanks a lot
Cel


OfficeDev18 via AccessMonster.com a écrit :

Hi, and welcome to Access,

There are many ways to skin the cat.

Add a column to your query, maybe called InvCreationMonth, and instead ofa
textbox on a form, do it as a parameter entry on the query, as follows:

You already have an invoice date in your data table, maybe called InvDate, so
it's easy.

A - Add a new field in the query, maybe called InvMonth, as follows:

InvMonth: Month(InvDate)

In the criteria line for the InvMonth field, add the following:

CInt([Please Enter Month of Invoice])

Note the bracketing; it's critical. It will be looking for a numeric entry
between 1 and 12.

B - Add a new column to your query, maybe called InvCreationMonth, as follows:


InvCreationMonth: Format(InvDate, "mmmm")

In your report, then, you can either:

A - Add a new column/textbox and bind it to the new column in the query. This
will display "January" for every row in the report. Or you can

B - Add a textbox anywhere in the Report Heading Section of the report, and
set the Visible property to No. Bind it, as above, to the InvCreationMonth
column in the query. Now change the report heading to a textbox - it defaults
to type "Label" - and change the report heading to read "X Y Z Report forAll
Invoices Created in the Month of " & Me.InvCreationMonth

By the way, don't be embarrassed about using the Help file. I've been doing
this for eight years and I consult the Help file liberally. And I'm not alone.


Hope this helps,

Sam

Hi,
newbie to access, I have created a report, based on a query: works
fine!
Now, I have added a text box in order to get an input from the enduser,
such as:
Month: ?
The query parameter is well displayed in the report (i.e.: January),
but how can I refer to this one, below in my report, as a criteria?
Such as summing nbr of record having their Invoice creation date =
"January" (the value keyed in by the end user when launching the
report)

Any help would be greatly appreciated.
C,

 

[OfficeDev18 via AccessMonster.com]

Yes, Cel, you can do this. Add a textbox anywhere on your report. Set the
Control Source property to

Forms!YourFormName!YourTextboxName.Value

and try it out.

Sam

Thanks for your answer, however I wish to avoid having to add field to
either
my table or queries. Any chance to assign the value of the parameter to

a variable that I can then reuse somewhere else in my report for
testing purposes?
Thanks a lot
Cel

OfficeDev18 via AccessMonster.com a écrit :

Hi, and welcome to Access,

[quoted text clipped - 56 lines]