Report with different record source - possible??? (1 Viewer)

[phinix]

In the last query you posted, I would want to code it so it refers to the 3 letter unique ID of each subject. For example, I could do this:

"Avg(Query_Residuals_ALL.[" & "[COLOR=Red][B]art[/B][/COLOR]" & "-res]"

Where art is that 3 letter that uniquely identifies art. You can't use Left("Subject_Name", 3) because you have subjects like English Language and English Literature, hence, trying to use the first 3 letters would mean those two subjects would end up being "eng" which will cause an error.

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

 

[vbaInet]

What if in future you had English Literature and English Language (for example)? Both will be "eng" so there's no distinction.

If you think the likelihood of that happening is slim then here:

Dim qdf As QueryDef
    
Const sqlString = "SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, " & _
            "[Query_Residuals_Departments_F&M].school_year " & _
          "FROM [Query_Residuals_Departments_F&M] " & _
          "GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, " & _
            "[Query_Residuals_Departments_F&M].school_year " & _
          "ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level " & _
          "PIVOT [Query_Residuals_Departments_F&M].Gender;"
    
Set qdf = CurrentDb.QueryDefs("Query_Residuals_Departments_F&M_Crosstab_option")
    
qdf.SQL = "TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOf" & ucase(left([COLOR=Blue][B]Combobox1[/B][/COLOR].value, 3)) & "-res]) AS " & _
               [FirstOfAvgOf" & ucase(left(Combobox1.value, 3)) & "-res] " & sqlString

Change the bit in blue to the name of your combo box.

 

[phinix]

What if in future you had English Literature and English Language (for example)? Both will be "eng" so there's no distinction.

If you think the likelihood of that happening is slim then here:

Dim qdf As QueryDef
    
Const sqlString = "SELECT [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, " & _
            "[Query_Residuals_Departments_F&M].school_year " & _
          "FROM [Query_Residuals_Departments_F&M] " & _
          "GROUP BY [Query_Residuals_Departments_F&M].Year, [Query_Residuals_Departments_F&M].Level, " & _
            "[Query_Residuals_Departments_F&M].school_year " & _
          "ORDER BY [Query_Residuals_Departments_F&M].Year DESC , [Query_Residuals_Departments_F&M].Level " & _
          "PIVOT [Query_Residuals_Departments_F&M].Gender;"
    
Set qdf = CurrentDb.QueryDefs("Query_Residuals_Departments_F&M_Crosstab_option")
    
qdf.SQL = "TRANSFORM First([Query_Residuals_Departments_F&M].[AvgOf" & ucase(left([COLOR=Blue][B]Combobox1[/B][/COLOR].value, 3)) & "-res]) AS " & _
               [FirstOfAvgOf" & ucase(left(Combobox1.value, 3)) & "-res] " & sqlString

Change the bit in blue to the name of your combo box.

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?

 

[vbaInet]

That's correct, put the code before opening the report.

Make the report's record source the query which you're building - Query_Residuals_Departments_F&M_Crosstab_option

 

[phinix]

That's correct, put the code before opening the report.

Make the report's record source the query which you're building - Query_Residuals_Departments_F&M_Crosstab_option

Sweet Jesus Holy Batman! It works!
You my friend.. You.. no.. You!!
Thank you again - this is fantastic, I have to practice more of that VB, you can make amazing stuff in the background of that Access. Fantastic, thank you so much !

 

[vbaInet]

I never knew Batman was holy, I thought he drank and partied alot

Glad it's working.

 

[phinix]

I never knew Batman was holy, I thought he drank and partied alot

Glad it's working.

Hi there again!

Now I have a pickle...
I have same thing as before, report based on combo box, only thing is this time the name in combo box has the name of the "set", but it is not always first 3 characters - sometimes it is something like "5APhyH1", where the name I would need to use is Phy. That means i cannot use Left function and set it on 3 characters...

How can I define it so it will "search" thsoe 3 letters in a string?
Please help!