Wierd report display

[Tawcan]

Having some wierd report display on my report...

Basically I have a main report where 6 reports are linked to it by using subreport. On these 6 reports there is an area for displaying annual average (database for school). The calculation code used:

=DSum(" [GRADES] * [ACT CREDITS] ",Forms![INDIVIDUAL APR]!BOX2,"[YEAR] = REPORTS![APR REPORT YEAR3]!YEAR AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )")/DSum("[ACT CREDITS]",Forms![INDIVIDUAL APR]!BOX2,"[YEAR] = REPORTS![APR REPORT YEAR3]!YEAR AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)")

APR REPORT YEAR_ changes depending on the year...

When I open these 6 reports individually the annual averages show up on each report. However, when I open up the main report by itself, I get #ERROR on the annual average box. Now the interesting thing is, if I have these 6 pages opened up individiually (or any of them), then open up the main report, everything works on the main report.

Any idea why this might be happening? Seems very wierd to me...

Thanx for the helps.

 

[Pat Hartman]

You are not referencing the control properly.

Reports!YourMainReport!YourSubReport.Report!YourControl

 

[Tawcan]

You are not referencing the control properly.

Reports!YourMainReport!YourSubReport.Report!YourControl

So instead of

REPORTS![APR REPORT YEAR3]!YEAR

Change it to...

REPORTS![Main Report]!APR REPORT YEAR3.Report![APR REPORT YEAR3]!YEAR

?

 

[Pat Hartman]

You have the subreport name too many times.

REPORTS![Main Report]![APR REPORT YEAR3].Report![YEAR]

BTW, Year is a poor choice for a name since it is the name of a function. You must surround it with square brackets. It is also a poor choice to use names with embedded spaces. You must also surround those names with square brackets.

 

[Tawcan]

Changed it to:

=DSum(" [GRADES] * [ACT CREDITS] ",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = REPORTS![Main Report]![APR REPORT YEAR1].REPORT![YEAR] AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )")/DSum("[ACT CREDITS]",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = REPORTS![Main Report]![APR REPORT YEAR1].REPORT![YEAR] AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)")

But still doesn't work... still shows up as #ERROR on both the report and main report. (I suspect it will display error on the report but should work on the main report...)

Help

 

[Tawcan]

Is subreport just simply showing up the report that's linked to? Why do I need to reference the control?

I'm changing the control source for the individual reports.... is there's something I'm doing wrong?

 

[Pat Hartman]

If [YEAR] is numeric try this:

=DSum(" [GRADES] * [ACT CREDITS] ",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = " & REPORTS![Main Report]![APR REPORT YEAR1].REPORT![YEAR] & " AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")/DSum("[ACT CREDITS]",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = " & REPORTS![Main Report]![APR REPORT YEAR1].REPORT![YEAR] & " AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")

If [YEAR] is text try this:

=DSum(" [GRADES] * [ACT CREDITS] ",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![APR REPORT YEAR1].REPORT![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")/DSum("[ACT CREDITS]",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![APR REPORT YEAR1].REPORT![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")

You cannot use the expression "If somefield = null" to compare a field to null. The result will ALWAYS be false. You need to use the IsNull() function to determine if a field is null.

 

[Tawcan]

Hmmm... YEAR is defined as a string but I tried both of those codes...

When I view the report both codes gave me a #Name display on the box (what does that mean?). Also the code seems to change to this after I view it:

=DSum(" [GRADES] * [ACT CREDITS] ",Forms![INDIVIDUAL APR]!BOX2,"[YEAR] = '" & Reports![Main Report]![APR REPORT YEAR1]!Report!YEAR & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )")/DSum("[ACT CREDITS]",Forms![INDIVIDUAL APR]!BOX2,"[YEAR] = '" & Reports![Main Report]![APR REPORT YEAR1]!Report!YEAR & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)")

Could you take a look at my database? It's probably easier when you see it.
It's located here

I'm editing APR REPORT YEAR1 right now.... (Go in Individual APR, type in student # (01234567), click on View Grade Report. This will display the main report)

Thanx.

 

[Tawcan]

Help?

 

[Tawcan]

H...e...l...p...

 

[Pat Hartman]

You didn't read my post. I wasn't kidding.

You cannot use the expression "If somefield = null" to compare a field to null. The result will ALWAYS be false. You need to use the IsNull() function to determine if a field is null.

Your immediate problem is caused because you are not referencing the report field correctly. The reference is to the report CONTROL name NOT the name of the report that you see in the database window. The first report is actually named Child57.

But it's even worse than that. You need to go back to square 1 and rebuild everything. You simply CANNOT have a separate table for EVERY student. You are making separate tables for years and all sorts of other pieces of data. You have made an incredible amount of work for yourself and the database will rapidly become unusable.

Do some reading on database normalization before you start again. Your forms and reports look very pretty but they are built on sand.

 

[Tawcan]

I understand that the database needs to be normalized badly. I've suggested my boss on doing so but he said that would just cause too much time. I've also talked to a few ppl that modified the database previously and they agreed on the normalization idea, but the boss says no. So normalization is not an option.

Since YEAR is text in the field (I think) I tried with this code:

=DSum(" [GRADES] * [ACT CREDITS] ",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![APR REPORT YEAR1].REPORT![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")/DSum("[ACT CREDITS]",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![APR REPORT YEAR1].REPORT![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")

But it didn't work. Are you saying it should be...

=DSum(" [GRADES] * [ACT CREDITS] ",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![Child57].REPORT![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")/DSum("[ACT CREDITS]",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![Child57].REPORT![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")

 

[Pat Hartman]

I deleted your db already. I think this is what I got to work:

=DSum(" [GRADES] * [ACT CREDITS] ",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![Child57]![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")/DSum("[ACT CREDITS]",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![Child57]![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")

Sounds like your boss built the database.

 

[Tawcan]

I deleted your db already. I think this is what I got to work:

=DSum(" [GRADES] * [ACT CREDITS] ",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![Child57]![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")/DSum("[ACT CREDITS]",[Forms]![INDIVIDUAL APR]![BOX2],"[YEAR] = '" & REPORTS![Main Report]![Child57]![YEAR] & "' AND ([GRADES] LIKE '[0-9]*' OR IsNull([GRADES]))")

Sounds like your boss built the database.

I think so too.

I'll give it a try on Monday at work.