Help with an Expression

[sondriven]

Hi, I have a single database form containing Supplier and Supplier Rating.

I have all the records sorted Ascending.

I need my report to have a box that Averages the rating#(in the form) for only the records specific to Supplier field. I hope this is making sense.

I am assuming that an expression could perform this task.

Thanks for everyones help. This forum is really saving my butt!

john

 

[David R]

Look into a Totals query in Help

The "sigma" button on the toolbar will get you started.

 

[sondriven]

I couldnt find a sigma button, but I found some info in the help section and heres where im at:

I created a select query. In the first column i put in the information for the Supplier name. And had it Total by group.
In the second column i put in the Rating number and had it Total by AVG.

Dont know if I need to put anymore information into the query from there, so I saved it.

Now I went into the report and made a text box for my calculated Average Number. In the properties I put in the Query I made into the control source. But when I went to preview the report, It now is asking for a Rating Number Parameter.

Im kinda lost from here and on what Im doing wrong. More help is definitely needed. Thanks

john

 

[David R]

The sigma button looks like a sideways "M"

But it sounds like you've got a Totals query running, if you're getting options for Group By and Avg. So far so good.

Have you tried running your query by itself to make sure it's giving you the correct results? Make sure that's working before you go for the report just yet.

It sounds like your report is trying to control source [ReportNumber]. But the new field is probably actually called [AvgOfReportNumber]. See if that fixes it.

 

[sondriven]

Thanks for replies DavidR,

Okay the query is working fine. I did a Run on it and looks great.

In the report I used the "AvgofReportNumber" in the control source and am still getting the "add parameter window".

Stuck here.

j

 

[David R]

Popup the Field List again (View>Field List, I think) in the Report Design window and recreate the text box. All I can think of is that the field isn't remembering properly this new change. That, or you've typoed it somehow. What does the Enter Parameter box ask for - ReportNumber or AvgOfReportNumber?

 

[sondriven]

The parameter looks like this:

Enter Parameter Value

Rating Average Query
__________________

Okay - Cancel



I dont know if this is screwing it up or not but I do have a record source for the report itself on another query. It is for a Between Dates parameter.

So I actually get a Enter the first date, then the last. And after that I get the parameter above. But the first query works fine.

 

[David R]

Your control on the report should reference the field, not the query name.

Is [Ratings Average Query] the name of the query we're talking about? The report should be based on that, but the individual controls must reference the individual query fields.

Try rebuilding it from scratch. Use the Report Wizard, and base it on the Query, which you know works.

 

[sondriven]

David,

Thanks for all your help. I made a new query with all the info and then redid a report based on that. Works perfectly.

I used a Modal/Pop up form to enter in the dates (start/End) for the report and was wondering what event will make the form close after I hit enter for the preview report button.

Thanks again.

John

 

[Rich]

Don't close it, set the visible property to false and close it when the report closes Me.Form.Visible=False

 

[David R]

If you make it invisible make sure you can make it visible again!

To expand upon what Rich said:
If you're going to need the form later, you can put

Me.Form.Visible = False

right after the DoCmd.OpenReport ... stuff on your popup form.

Then in the Report's Close event, you can put

Forms!PopupFormName.Visible = True

to get it back again.

Alternatively, don't make it popup/modal and it should slip behind your report when it opens (might have to make the report open Maximized).

DoCmd.Maximize

in the Report's Open event.

 

[Cosmos75]

Found this helpful!

Just FYI,

I found this topic to be helpful!