Display parameters in access Report

[access11899]

Hi I'm a beginner

I have a problem in displaying two parameters in a report

The report is based on the following query:

SELECT summaryTable.ACCOUNT, Round((Sum(SummaryTable.Time)*24*60)/60)+((Sum(SummaryTable.Time)*24*60) Mod 60)/100 AS WORKED_HOUR
FROM summaryTable
WHERE summaryTable.date Between [from] And [TO]
GROUP BY summaryTable.ACCOUNT
ORDER BY Round((Sum(SummaryTable.Time)*24*60)/60)+((Sum(SummaryTable.Time)*24*60) Mod 60)/100 DESC;

The parameters I want to display are [from] And [TO].

I have already tried to create
1)text box in the report
2)and typed in =[from] for text box 1 and =[TO] for text box 2.


The reports works and displays a table graph but instead of displaying the two parameters all I get is #Name?

I have made sure that the text in the text boxes is exactly the same as the sql query [from] and [TO].

Many thanks for your help

 

[vbaInet]

The way to do is to create a form with two textboxes that will be used to collect the parameter, then reference the textboxes in your query and your report.

1. Create form to collect parameters, let's say it's called frmParams.
2. Let's assume that the textboxes are named txtFrom and txtTo.
3. The WHERE part of your query will look like this:

WHERE summaryTable.date Between [Forms]![[B]frmParams[/B]]![[B]txtFrom[/B]] And [Forms]![[B]frmParams[/B]]![[B]txtTo[/B]]

4. Then the From textbox in your report (for example) will look like this:

=[Forms]![[B]frmParams[/B]]![[B]txtFrom[/B]]

So before you open the report, open the form, collect the params, keep the form open (you can make it invisible if you wish), open your report.

 

[access11899]

Hi vbaInet,

Thank you for your answer.

1)I have created the form and the two text boxes

2) I have modified the query
SELECT summaryTable.Account, Fix((Sum(SummaryTable.Time)*24*60)/60)+((Sum(SummaryTable.Time)*24*60) Mod 60)/100 AS WORKED_HOUR
FROM summaryTable
WHERE summaryTable.Date Between Forms!frmParams!txtFrom And Forms!frmParams!txtTo
GROUP BY summaryTable.Account
ORDER BY Round((Sum(SummaryTable.Time)*24*60)/60)+((Sum(SummaryTable.Time)*24*60) Mod 60)/100 DESC;


And I have tested it and it works




3) Finally I have modified the text boxes in the report

=[Forms]![frmParams]![from] and [Forms]![frmParams]![to]

Please note that when I save the report the square bracket are automatically removed.

Then when I run the report I obtain the same result as before. #Name?

Thank you again for your help

 

[vbaInet]

The square brackets are automatic so nothing to worry about.

You are not referring to the right control in your report. You called it txtTo but you are referring to To.

 

[access11899]

Hi vbInet,
Thank you again for your answer.

I have called the text boxes

from
and

to

this is the query

SELECT summaryTable.Account, Round((Sum(SummaryTable.Time)*24*60)/60)+((Sum(SummaryTable.Time)*24*60) Mod 60)/100 AS WORKED_HOUR
FROM summaryTable
WHERE summaryTable.Date Between Forms!frmParams!from And Forms!frmParams!to
GROUP BY summaryTable.Account
ORDER BY Round((Sum(SummaryTable.Time)*24*60)/60)+((Sum(SummaryTable.Time)*24*60) Mod 60)/100 DESC;


These are the text boxes in the report

[Forms]![frmParams]![from]
[Forms]![frmParams]![to]


Probably there is something very silly that I am doing.


I have noticed that even if I change the query

from

Between Forms!frmParams!from And Forms!frmParams!to


to

Between Forms!frmParams!from2 And Forms!frmParams!to2

without changing the form. the query still works. Maybe there I have not created a link between the form and the query.

Thank you for your help

 

[CBrighton]

FYI another possible solution would be to add 2 fields to the query: [From] AS StartDate, [To] AS EndDate (in the query browser this would be "StartDate: [From]" & "EndDate: [To]").

Those fields can then be used in control sources either as a field or concatonated into a string.

 

[vbaInet]

You just simply haven't properly followed all the steps I listed out. I specifically mentioned using certain naming conventions because I don't know what you called other controls or fields on your report or your form but you ignored it.

Let me see your db.

 

[access11899]

First of all thank you to CBrighton

I have tried to implement your solution but I get exactly the same message #Name?

I can select in the data source the fields now but I still get #Name

vbaInet it is very kind of you.

Please see the db in the attachement

Many Thanks

 

[vbaInet]

Seriously, all the instructions you needed were in my first post. Step 4 was were you didn't follow exactly what was written. All it needed was an equal to (=) in the Control Source of both texboxes in your report.

Have a look at my first post again, and notice how it was written (i.e. step 4).

 

[access11899]

Thanks vbaInet,

Now it's working.

There is only one point.

Is there any way to run the report from the form?

What I mean is that at the moment
1) the user has to fill in the form and to keep it open
2) and then click on the report.

What I would like is to fill in the form and then see the report without clicking on it. Is there any way to do it?

Thanks.

 

[vbaInet]

Drop a button on your form and follow the wizard through to Open the Report.

Once you get the code, you can make the form invisible before the code line that opens the report. But remember to close the form in the Unload event of your report.

 

[access11899]

hi vbaInet,

I've tried to implement your solution but with no luck
I mean that when I opened the code window I did not know what to do
Then I found on the internet another solution.

Create a macro that opens the report and then
1. Go back to your form.
3. On the Toolbar at the top hit View>Design View
2. Right click on your button.
3. Select properties.
4. Click the event tab.
5. Look for "On click.................." Click the field to the right.
6. Within the drop down you will see the name of your Macro that you made.
7. Click the Macro name.
8. Close the Properties box.
9. Go back to View>Form View
10. Click your new button and your report should show up in Print Preview.




It works.

Thank you for your help and I am sorry for not being able to follow your instructions.

 

[vbaInet]

Happy to see you got it resolved!