Search results

After this code add stDocName = "Report_residuals" DoCmd.OpenReport stDocName, acPreview to open report, is that correct? All this code goes under onClick in the button, right? Should this Query_Residuals_Departments_F&M_Crosstab_option be empty? Should I leave report record source empty as well?

You can do this way, cause it will always be 3 letters, art-res, bio-res, che-res, med-res ...etc

Nope, they come from table in Subject field. Each student has few rows with level of exam he took, subject it was, results, etc...

No problem: Code: SELECT Query_Residuals_ALL.Year, Query_Residuals_ALL.Level, Query_Residuals_ALL.Gender, Query_Residuals_ALL.school_year, Avg(Query_Residuals_ALL.[art-res]) AS [AvgOfart-res], Avg(Query_Residuals_ALL.[bio-res]) AS [AvgOfbio-res], Avg(Query_Residuals_ALL.[bus-res]) AS...

They come from simple query: SELECT [imported data - RAW].Subject FROM [imported data - RAW] GROUP BY [imported data - RAW].Subject ORDER BY [imported data - RAW].Subject;

No problem, this one is art query: Code: TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOfart-res]) AS [FirstOfAvgOfart-res] SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, [Query_Residuals_Departments_F&M].school_year FROM...

Can I do sth like this? Create two constant sql strings, first part transform, second rest of the sql statement and middle part based on iif: Const sqlString = "TRANSFORM " Const sqlString2 = "SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level...

OK. So, here is what I got so far, button code: Private Sub Open_Report_Button_Click() On Error GoTo Err_Open_Report_Button_Click Dim stDocName As String Dim qdf As QueryDef Const sqlString = "SELECT * FROM [TableName] " Set qdf =...

OK, so you say it better to send sql query every time, instead of pulling it via its name, got it. Where should I add this "QueryDef" code? To report or form?

I think I know what you mean, but can you please take a look on this: I came up with this, but of course it doesn't work:) I made a button on the form next to combo box with list of subjects. Now, under on click I wrote this: Private Sub Open_Report_Button_Click() On Error GoTo...

Hello again! :) Let me explain a bit more, you already saw my database a bit so it should be easy I guess: I have those subjects, I have crosstab query that shows residuals for each subject. Now I need to generate report that shows those residuals in specific way: report shows it only for one...

Is it possible to create a report that will have different record source depeneds on a combo field? If user picks "english" for example and clicks the button, it will open the report that is based on eng_query, if he picks biology, same report will open based on bio_query... How can I do that?

Guys, I need your help again:) I have a crosstab query and I want value field to be depanded to combo field on one form. I hope it makes sense. If user picks a name of the subject from combo field on one form it will opens report that is based on that crosstab query where value field compared to...

FANTASTIC !!! :D Thank you so much for help! Making fixed column names fixed the problem! I knew you're not gonna let me down:D Thanks again!

I thought you gonna say that:) But I have another question - see this avg feld at the bottom? Delete other two and leave just for art (which have value in the band on the left part fo the form). You will see that avg field actually calculates this residual again inside of itself (basically...

Here it is, cut up like a small sausage:) There is main form, choose "both" option for gender. Now, you can there calculated field below on right that it has #error - it is basically same as field above only with AVG function. How can I make it work?

Oh, you mean just attach db file - really sorry, but I cannot do that, it is pretty much confidential. But I can add some parts of it, like queries, forms, etc. if that helps.

That is exactly my problem - how to calculate average skipping null fields... I cannot change them to zeros, cannot change them to words... P.S. what do you mean by stripped-down version? How can I do it?

OK. I've been thinking about this and I think there is no way to fix it. Basically because avg can be done on numerical values and I cannot put "0" in null fields. All null fields needs to be null, cause if I put "0" in it it will be taken as zero, which would mean something for average. How...

=IIf(IsNumeric([artres]),Avg([artres]),0)