criteria data in report

[pht1991]

I pull a report from a query. When I do so, I am prompted for date
parameters (part of the query). I would like the values that I enter for
these parameters to show up on the report. Any suggestions?

Thanks

 

[Dirk Goldgar]

I pull a report from a query. When I do so, I am prompted for date
parameters (part of the query). I would like the values that I enter for
these parameters to show up on the report. Any suggestions?

You can get the parameters into the query as calculated fields. Here's an
example:

SELECT
MyTable.*,
[Enter Start Date] As FromDate,
[Enter End Date] As ToDate,
FROM MyTable
WHERE
MyTable.DateField Between [Enter Start Date] And [Enter End Date]

Having set up the query that way, the parameter values will be available as
fields FromDate and ToDate, and you can include those fields on your report.

 

[pht1991]

Sorry -I'm a novice. So you are saying that I need to write a formula in the
Criteria section of the query?

I don't understand the formula. The table name I want to use is "transcript
orders". The field I want to criteria on is simply "Date"

Are SELECT, FROM and WHERE commands that I need in the formula?

What is "MyTable.*,"?

The more you can break it down for me, the better - me knowledge is very
limmited.

Thanks,
LE

I pull a report from a query. When I do so, I am prompted for date
parameters (part of the query). I would like the values that I enter for
these parameters to show up on the report. Any suggestions?

You can get the parameters into the query as calculated fields. Here's an
example:

SELECT
MyTable.*,
[Enter Start Date] As FromDate,
[Enter End Date] As ToDate,
FROM MyTable
WHERE
MyTable.DateField Between [Enter Start Date] And [Enter End Date]

Having set up the query that way, the parameter values will be available as
fields FromDate and ToDate, and you can include those fields on your report.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[John W. Vinson]

Sorry -I'm a novice. So you are saying that I need to write a formula in the
Criteria section of the query?

I don't understand the formula. The table name I want to use is "transcript
orders". The field I want to criteria on is simply "Date"

Are SELECT, FROM and WHERE commands that I need in the formula?

What is "MyTable.*,"?

The more you can break it down for me, the better - me knowledge is very
limmited.

Thanks,
LE

I pull a report from a query. When I do so, I am prompted for date
parameters (part of the query). I would like the values that I enter for
these parameters to show up on the report. Any suggestions?

You can get the parameters into the query as calculated fields. Here's an
example:

SELECT
MyTable.*,
[Enter Start Date] As FromDate,
[Enter End Date] As ToDate,
FROM MyTable
WHERE
MyTable.DateField Between [Enter Start Date] And [Enter End Date]

The query grid is simply a tool to construct a SQL string. Dirk - not knowing
anything about your tablenames or fieldnames - has offered you a SQL string.

What you can do to get the same result is open your query in design grid view.
Copy and paste the prompts from your Critera line into vacant Field cells in
the top row of the query. Or, select View... SQL; you'll see something
resembling what Dirk posted, with your actual table names, fieldnames and
prompts. You can copy the Between... And... prompts into the SELECT clause
following his example.

John W. Vinson [MVP]

 

[Dirk Goldgar]

Sorry -I'm a novice. So you are saying that I need to write a formula in
the
Criteria section of the query?

No. I was suggesting that you define calculated fields in your query that
would hold the parameters.

I don't understand the formula. The table name I want to use is
"transcript
orders". The field I want to criteria on is simply "Date"

Just for future reference, "Date" is a bad name to use for a field. There's
a built-in function named "Date" and having that as a field name can lead to
problems unless you handle it just right. I suppose it's too late to rename
that field? We can work around it if we have to.

Are SELECT, FROM and WHERE commands that I need in the formula?

No. Those are elements of the SQL statement. It's not a formula, it's the
"SQL View" of a query. What I posted was an example.

What is "MyTable.*,"?

"MyTable" was just the name of the table I used for my example. "SELECT
MyTable.*", in SQL, means "select all the fields in the table named
'MyTable'".

The more you can break it down for me, the better - me knowledge is very
limmited.

I think we'd better approach this from a different angle. You say your
report is based on a query that has date parameters. Please open that query
in SQL View, and copy/paste the SQL statement into a reply to this message.

 

[pht1991]

No. I was suggesting that you define calculated fields in your query that
would hold the parameters.


Just for future reference, "Date" is a bad name to use for a field. There's
a built-in function named "Date" and having that as a field name can lead to
problems unless you handle it just right. I suppose it's too late to rename
that field? We can work around it if we have to.


No. Those are elements of the SQL statement. It's not a formula, it's the
"SQL View" of a query. What I posted was an example.


"MyTable" was just the name of the table I used for my example. "SELECT
MyTable.*", in SQL, means "select all the fields in the table named
'MyTable'".


I think we'd better approach this from a different angle. You say your
report is based on a query that has date parameters. Please open that query
in SQL View, and copy/paste the SQL statement into a reply to this message.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk - someone else suggested that I past the prompts into blank fields in
the query. This seems to have worked just fine. THanks for the help!

 

[Dirk Goldgar]

Dirk - someone else suggested that I past the prompts into blank fields in
the query.

Yes, that's the visual equivalent of what I was trying to get at via SQL.
I'm glad you got it working.

 

[Dirk Goldgar]

<a clearer explanation than I was able to manage>

Thanks, John.