Opening Reports

[vani65]

Hi,
I am running the report through form. The criteria is entered in the form and click on button opens the report. On the open event of the report I have following code. The problem is when the job status is Selected as "Accepted" nothing comes in CFAR field but when Job status is selected as "Finish" data appears in CFAR field. I am not too sure where I am doing wrong Could someone please have a look at code and please help me. Thanks in advance.

Dim mySql As String
Dim myCriteria As String
Dim myStart As Date
Dim myEnd As Date
Dim myDateType As String
Dim myStatus As Variant
DoCmd.Maximize

DoCmd.SetWarnings False

mySql = "DELETE tmpJobAnalysisByMth.* FROM tmpJobAnalysisByMth "
DoCmd.RunSQL mySql

myStart = DLookup("StartDate", "zz_RepPar")
myEnd = DLookup("EndDate", "zz_RepPar")
myDateType = DLookup("DateToRunFrom", "zz_RepPar")
myStatus = DLookup("JobStatus", "zz_RepPar")
'
' Extract Expenses
'
mySql = "INSERT INTO tmpJobAnalysisByMth ( JobId, FinPeriodYear, FinPeriodMonth, IStatus, ExpensesTotal, JobDate ) "
mySql = mySql & "SELECT [unirptJobAnalysisByMth:Expenses].JobID, DatePart('yyyy',[InvoiceDate]) AS IYear, DatePart('m',[InvoiceDate]) AS IMth, tblJob.JobStatusID, Sum([unirptJobAnalysisByMth:Expenses].Total) AS SumOfTotal, "
If myDateType = "Accepted Date" Then
mySql = mySql & "tblJob.AcceptedDate "
Else
mySql = mySql & "tblJob.JobFinishDate "
End If
mySql = mySql & "FROM [unirptJobAnalysisByMth:Expenses] INNER JOIN tblJob ON [unirptJobAnalysisByMth:Expenses].JobID = tblJob.JobID "
mySql = mySql & "GROUP BY [unirptJobAnalysisByMth:Expenses].JobID, DatePart('yyyy',[InvoiceDate]), DatePart('m',[InvoiceDate]), tblJob.JobStatusID, tblJob.AcceptedDate, tblJob.JobFinishDate "

mySql = mySql & "HAVING [unirptJobAnalysisByMth:Expenses].JobID Is Not Null AND "

If myDateType = "Accepted Date" Then
mySql = mySql & "tblJob.AcceptedDate>=#" & Format(myStart, "mm/dd/yy") & "# And (tblJob.AcceptedDate)<=#" & Format(myEnd, "mm/dd/yy") & "# "
'' Else
'' mySql = mySql & "tblJob.JobFinishDate>=#" & Format(myStart, "mm/dd/yy") & "# And (tblJob.JobFinishDate)<=#" & Format(myEnd, "mm/dd/yy") & "# "
End If
'MsgBox mysql
DoCmd.RunSQL mySql
'
' Append Income
'
mySql = "INSERT INTO tmpJobAnalysisByMth ( JobId, FinPeriodYear, FinPeriodMonth, IncomeTotal, IStatus, JobDate ) "
mySql = mySql & "SELECT tblInvoiceHeader.JobID, DatePart('yyyy',[InvoiceDate]) AS IYear, DatePart('m',[InvoiceDate]) AS IMth, Sum(tblInvoiceHeader.TotalexGST) AS SumOfTotalexGST, tblJob.JobStatusID, "
If myDateType = "Accepted Date" Then
mySql = mySql & "tblJob.AcceptedDate "
Else
mySql = mySql & "tblJob.JobFinishDate "
End If
mySql = mySql & "FROM tblJob INNER JOIN tblInvoiceHeader ON tblJob.JobID = tblInvoiceHeader.JobID "
mySql = mySql & "GROUP BY tblInvoiceHeader.JobID, DatePart('yyyy',[InvoiceDate]), DatePart('m',[InvoiceDate]), tblJob.JobStatusID, tblJob.AcceptedDate, tblJob.JobFinishDate "
' mySql = mySql & "HAVING tblInvoiceHeader.JobID Is Not Null AND tblJob.JobStatusID='ACC' AND "
mySql = mySql & "HAVING tblInvoiceHeader.JobID Is Not Null "

'' If myDateType = "AcceptedDate" Then
'' mySql = mySql & "tblJob.AcceptedDate>=#" & Format(myStart, "mm/dd/yy") & "# And (tblJob.AcceptedDate)<=#" & Format(myEnd, "mm/dd/yy") & "# "
'' Else
'' mySql = mySql & "tblJob.JobFinishDate>=#" & Format(myStart, "mm/dd/yy") & "# And (tblJob.JobFinishDate)<=#" & Format(myEnd, "mm/dd/yy") & "# "
'' End If

'MsgBox mysql
DoCmd.RunSQL mySql

' Set up Report RecourdSource
'
mySql = "SELECT tmpJobAnalysisByMth.JobId, Max(tmpJobAnalysisByMth.IStatus) AS IStatus, Sum(tmpJobAnalysisByMth.ExpensesTotal) AS CFAP, Sum(tmpJobAnalysisByMth.IncomeTotal) AS CFAR, Sum(nz([IncomeTotal],0))-(Sum(nz([ExpensesTotal],0))) AS CFBal "
mySql = mySql & "FROM tmpJobAnalysisByMth "
mySql = mySql & "GROUP BY tmpJobAnalysisByMth.JobId "
mySql = mySql & "HAVING (Sum(Nz([IncomeTotal], 0)) - Sum(Nz([ExpensesTotal], 0))) <> 0 "
If IsNull(myStatus) Or myStatus = "" Then
mySql = mySql & "AND Max(tmpJobAnalysisByMth.IStatus) Is Not Null "
Else
mySql = mySql & "AND Max(tmpJobAnalysisByMth.IStatus) = '" & myStatus & "' "
End If

mySql = mySql & "ORDER BY tmpJobAnalysisByMth.JobId "


'MsgBox mysql
Me.RecordSource = mySql
DoCmd.SetWarnings True

End Sub

 

[BoxHead]

If myDateType = "Accepted Date" Then

Does the function DLookup("DateToRunFrom", "zz_RepPar")
actually equal the words "Accepted Date" exactly or is it a true/false/yes/no/something else value?

Put a break point in the code to see what's being returned by your DLookUp.




John

Use what you have,
Learn what you can,
Create what you need.