Record Source Problem

[vani65]

Hi,
In one of my report, On open event of the report.
Two queries are run to load information from various tables into a dummy table.
Then I have a record source for the report in the code. Which is as following:

' Set up Report RecourdSource
'
mySql = "SELECT tmpJobAnalysisByMth1.JobId, Sum(IIf(([FinPeriodYear]<" & myYear & ") Or ([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]<" & myMth & "),[ExpensesTotal],0)) AS BFAP, Sum(IIf(([FinPeriodYear]<" & myYear & ") Or ([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]<" & myMth & "),[IncomeTotal],0)) AS BFAR, (Sum(IIf([FinPeriodYear]<" & myYear & " Or ([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]<" & myMth & "),nz([IncomeTotal],0),0)))-(Sum(IIf([FinPeriodYear]<" & myYear & " Or ([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]<" & myMth & "),nz([ExpensesTotal],0),0))) AS BFBal, "
mySql = mySql & "Sum(IIf([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]=" & myMth & ",[ExpensesTotal],0)) AS CurrAP, Sum(IIf([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]=" & myMth & ",[IncomeTotal],0)) AS CurrAR, (Sum(IIf([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]=" & myMth & ",nz([IncomeTotal],0),0)))-(Sum(IIf([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]=" & myMth & ",nz([ExpensesTotal],0),0))) AS CurrBal, "
mySql = mySql & "Sum(IIf([FinPeriodYear]<" & myYear & " Or ([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]<=" & myMth & "),[ExpensesTotal],0)) AS CFAP, Sum(IIf([FinPeriodYear]<" & myYear & " Or ([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]<=" & myMth & "),[IncomeTotal],0)) AS CFAR, (Sum(IIf([FinPeriodYear]<" & myYear & " Or ([FinPeriodYear]=" & myYear & " And [FinPeriodMonth]<=" & myMth & "),nz([IncomeTotal],0),0)))-(Sum(IIf([FinPeriodYear]<" & myYear & " Or ([FinperiodYear]=" & myYear & " And [FinPeriodMonth]<=" & myMth & "),nz([ExpensesTotal],0),0))) AS CFBal, "
mySql = mySql & " Sum(tmpJobAnalysisByMth1.SOH) AS SOHTotal "
mySql = mySql & "FROM tmpJobAnalysisByMth1 "
mySql = mySql & "GROUP BY tmpJobAnalysisByMth1.JobId "

'' mySql = mySql & "ORDER BY tmpJobAnalysisByMth1.JobId "

Me.RecordSource = mySql
DoCmd.SetWarnings True


Then also in record source property I have :
SELECT tmpJobAnalysisByMth1.JobId AS JobID, Sum(IIf(([FinPeriodYear]<2003) Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<12),[ExpensesTotal],0)) AS BFAP, Sum(IIf(([FinPeriodYear]<2003) Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<12),[IncomeTotal],0)) AS BFAR, (Sum(IIf([FinPeriodYear]<2003 Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<12),nz([IncomeTotal],0),0)))-(Sum(IIf([FinPeriodYear]<2003 Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<12),nz([ExpensesTotal],0),0))) AS BFBal, Sum(IIf([FinPeriodYear]=2003 And [FinPeriodMonth]=12,[ExpensesTotal],0)) AS CurrAP, Sum(IIf([FinPeriodYear]=2003 And [FinPeriodMonth]=12,[IncomeTotal],0)) AS CurrAR, (Sum(IIf([FinPeriodYear]=2003 And [FinPeriodMonth]=12,nz([IncomeTotal],0),0)))-(Sum(IIf([FinPeriodYear]=2003 And [FinPeriodMonth]=12,nz([ExpensesTotal],0),0))) AS CurrBal, Sum(IIf([FinPeriodYear]<2003 Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<=12),[ExpensesTotal],0)) AS CFAP, Sum(IIf([FinPeriodYear]<2003 Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<=12),[IncomeTotal],0)) AS CFAR, (Sum(IIf([FinPeriodYear]<2003 Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<=12),nz([IncomeTotal],0),0)))-(Sum(IIf([FinPeriodYear]<2003 Or ([FinperiodYear]=2003 And [FinPeriodMonth]<=12),nz([ExpensesTotal],0),0))) AS CFBal, Sum(tmpJobAnalysisByMth1.SOH) AS SOHTotal FROM tmpJobAnalysisByMth1 GROUP BY tmpJobAnalysisByMth1.JobId HAVING ((((Sum(IIf([FinPeriodYear]<2003 Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<=12),nz([IncomeTotal],0),0)))-(Sum(IIf([FinPeriodYear]<2003 Or ([FinperiodYear]=2003 And [FinPeriodMonth]<=12),nz([ExpensesTotal],0),0))))<>0) AND (((Sum(IIf([FinPeriodYear]<2003 Or ([FinPeriodYear]=2003 And [FinPeriodMonth])<12,Nz([IncomeTotal],0),0)))-(Sum(IIf([FinPeriodYear]<2003 Or ([FinPeriodYear]=2003 And [FinPeriodMonth]<12),Nz([ExpensesTotal],0),0))))<>0)) ORDER BY tmpJobAnalysisByMth1.JobId;

the information being loaded into dummy table is correct, but the problem is with the report, in BFAP column only $0.00 is being displayed instead of values and data in rest of the columns is fine. I could not figure it out, I,m totally stumped could anyone please suggest where it went wrong.

Thanks in advance