Ignore Nulls

[ConfusedNAccess]

I have a query which produces a field for a reportname and then shows fields Jan-Dec with a date field. Fields Jan-Dec, may or maynot have a date field.

In my report, I need to show only reportnames when a date appears in any one of the fields Jan-Dec.
If a date is not present for fields Jan-Dec, I don't want anything to appear


How do I write this?

 

[randy700]

If you post the SQL for the query, we can help you modify it.


Randy

 

[ConfusedNAccess]

SELECT dbo_AccountManagers.AM_ID, dbo_ActualReports.BorrID, dbo_BorrowerReports.BorrRepID, dbo_AccountManagers.AM_LastName, dbo_AccountManagers.AM_FirstName, dbo_Borrowers.ManagerNo, dbo_AccountManagers.AM_Transit, dbo_Borrowers.BorrowerName, dbo_Borrowers.SingleName, dbo_Borrowers.Brr, dbo_Borrowers.SRF, dbo_Borrowers.ClientYearEnd, dbo_ActualReports.ActualYear, dbo_BorrowerReports.SheetRowText, dbo_BorrowerReports.EffectiveFromDate, dbo_BorrowerReports.EffectiveToDate, dbo_BorrowerReports.Frequency, dbo_BorrowerReports.GracePeriod, IIf(([dbo_ActualReports]![Active01]=-1 And [dbo_ActualReports]![Month01Value]=0),Yes,No) AS [Breach 1], dbo_ActualReports.Month01DueDate, dbo_ActualReports.Month01SignOff, IIf(([dbo_ActualReports]![Active02]=-1 And [dbo_ActualReports]![Month02Value]=0),Yes,No) AS [Breach 2], dbo_ActualReports.Month02DueDate, dbo_ActualReports.Month02SignOff, IIf(([dbo_ActualReports]![Active03]=-1 And [dbo_ActualReports]![Month03Value]=0),Yes,No) AS [Breach 3], dbo_ActualReports.Month03DueDate, dbo_ActualReports.Month03SignOff, IIf(([dbo_ActualReports]![Active04]=-1 And [dbo_ActualReports]![Month04Value]=0),Yes,No) AS [Breach 4], dbo_ActualReports.Month04DueDate, dbo_ActualReports.Month04SignOff, IIf(([dbo_ActualReports]![Active05]=-1 And [dbo_ActualReports]![Month05Value]=0),Yes,No) AS [Breach 5], dbo_ActualReports.Month05DueDate, dbo_ActualReports.Month05SignOff, IIf(([dbo_ActualReports]![Active06]=-1 And [dbo_ActualReports]![Month06Value]=0),Yes,No) AS [Breach 6], dbo_ActualReports.Month06DueDate, dbo_ActualReports.Month06SignOff, IIf(([dbo_ActualReports]![Active07]=-1 And [dbo_ActualReports]![Month07Value]=0),Yes,No) AS [Breach 7], dbo_ActualReports.Month07DueDate, dbo_ActualReports.Month07SignOff, IIf(([dbo_ActualReports]![Active08]=-1 And [dbo_ActualReports]![Month08Value]=0),Yes,No) AS [Breach 8], dbo_ActualReports.Month08DueDate, dbo_ActualReports.Month08SignOff, IIf(([dbo_ActualReports]![Active09]=-1 And [dbo_ActualReports]![Month09Value]=0),Yes,No) AS [Breach 9], dbo_ActualReports.Month09DueDate, dbo_ActualReports.Month09SignOff, IIf(([dbo_ActualReports]![Active10]=-1 And [dbo_ActualReports]![Month10Value]=0),Yes,No) AS [Breach 10], dbo_ActualReports.Month10DueDate, dbo_ActualReports.Month10SignOff, IIf(([dbo_ActualReports]![Active11]=-1 And [dbo_ActualReports]![Month11Value]=0),Yes,No) AS [Breach 11], dbo_ActualReports.Month11DueDate, dbo_ActualReports.Month11SignOff, IIf(([dbo_ActualReports]![Active12]=-1 And [dbo_ActualReports]![Month12Value]=0),Yes,No) AS [Breach 12], dbo_ActualReports.Month12DueDate, dbo_ActualReports.Month12SignOff, IIf([Breach 1]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month01DueDate])=Year(Now()),[dbo_ActualReports].[Month01DueDate],"") AS Jan, IIf([Breach 2]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month02DueDate])=Year(Now()),[dbo_ActualReports].[Month02DueDate],"") AS Feb, IIf([Breach 3]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month03DueDate])=Year(Now()),[dbo_ActualReports].[Month03DueDate],"") AS Mar, IIf([Breach 4]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month04DueDate])=Year(Now()),[dbo_ActualReports].[Month04DueDate],"") AS Apr, IIf([Breach 5]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month05DueDate])=Year(Now()),[dbo_ActualReports].[Month05DueDate],"") AS May, IIf([Breach 6]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month06DueDate])=Year(Now()),[dbo_ActualReports].[Month06DueDate],"") AS Jun, IIf([Breach 7]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month07DueDate])=Year(Now()),[dbo_ActualReports].[Month07DueDate],"") AS Jul, IIf([Breach 8]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month08DueDate])=Year(Now()),[dbo_ActualReports].[Month08DueDate],"") AS Aug, IIf([Breach 9]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month09DueDate])=Year(Now()),[dbo_ActualReports].[Month09DueDate],"") AS Sep, IIf([Breach 10]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month10DueDate])=Year(Now()),[dbo_ActualReports].[Month10DueDate],"") AS Oct, IIf([Breach 11]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month11DueDate])=Year(Now()),[dbo_ActualReports].[Month11DueDate],"") AS Nov, IIf([Breach 12]=Yes And DatePart("yyyy",[dbo_ActualReports].[Month12DueDate])=Year(Now()),[dbo_ActualReports].[Month12DueDate],"") AS [Dec]
FROM ((dbo_Borrowers INNER JOIN (dbo_BorrowerReports INNER JOIN dbo_ActualReports ON dbo_BorrowerReports.BorrRepID = dbo_ActualReports.BorrRepID) ON dbo_Borrowers.BorrID = dbo_BorrowerReports.BorrID) INNER JOIN dbo_Centres ON dbo_Borrowers.CentreID = dbo_Centres.CentreID) INNER JOIN dbo_AccountManagers ON dbo_Borrowers.AM_ID = dbo_AccountManagers.AM_ID
WHERE (((dbo_AccountManagers.AM_ID)<>459)
ORDER BY dbo_AccountManagers.AM_LastName, dbo_Borrowers.BorrowerName;

 

[randy700]

For this type of problem, I would build and run the query in code. Something like...

Private Sub YourReport()
Dim strSQL as String
strSQL = "SELECT dbo_AccountManagers.AM_ID, " & _
"dbo_ActualReports.BorrID, " & _
"dbo_BorrowerReports.BorrRepID, " & _
"dbo_AccountManagers.AM_LastName, " & _
"dbo_AccountManagers.AM_FirstName, " & _
"dbo_Borrowers.ManagerNo, " & _
"dbo_AccountManagers.AM_Transit, " & _
"dbo_Borrowers.BorrowerName, " & _
"dbo_Borrowers.SingleName, " & _
"dbo_Borrowers.Brr, " & _
"dbo_Borrowers.SRF, " & _
"dbo_Borrowers.ClientYearEnd, " & _
"dbo_ActualReports.ActualYear, " & _
"dbo_BorrowerReports.SheetRowText, " & _
"dbo_BorrowerReports.EffectiveFromDate, " & _
"dbo_BorrowerReports.EffectiveToDate, " & _
"dbo_BorrowerReports.Frequency, " & _
"dbo_BorrowerReports.GracePeriod, "
If dbo_[Actual Reports]!Active01 = -1 AND dbo_[Actual Reports]!Month01Value = 0 Then
strSQL = strSQL & "'YES' AS Breach1, " & _
"dbo_ActualReports.Month01DueDate, " & _
"dbo_ActualReports.Month01SignOff, "
If DatePart("yyyy",dbo_ActualReports.Month01DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month01DueDate AS Jan "
End If
Else
strSQL = strSQL & "'NO' AS Breach1 "
End If
If dbo_[Actual Reports]!Active02 = -1 AND dbo_[Actual Reports]!Month02Value = 0 Then
strSQL = strSQL & "'YES' AS Breach2, " & _
"dbo_ActualReports.Month02DueDate, " & _
"dbo_ActualReports.Month02SignOff "
If DatePart("yyyy",dbo_ActualReports.Month02DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month02DueDate AS Feb "
End If
Else
strSQL = strSQL & "'NO' AS Breach2 "
End If
If dbo_[Actual Reports]!Active03 = -1 AND dbo_[Actual Reports]!Month03Value = 0 Then
strSQL = strSQL & "'YES' AS Breach3, " & _
"dbo_ActualReports.Month03DueDate, " & _
"dbo_ActualReports.Month03SignOff "
If DatePart("yyyy",dbo_ActualReports.Month03DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month03DueDate AS Mar "
End If
Else
strSQL = strSQL & "'NO' AS Breach3 "
End If
If dbo_[Actual Reports]!Active04 = -1 AND dbo_[Actual Reports]!Month04Value = 0 Then
strSQL = strSQL & "'YES' AS Breach4, " & _
"dbo_ActualReports.Month04DueDate, " & _
"dbo_ActualReports.Month04SignOff "
If DatePart("yyyy",dbo_ActualReports.Month0rDueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month04DueDate AS Apr "
End If
Else
strSQL = strSQL & "'NO' AS Breach4 "
End If
If dbo_[Actual Reports]!Active05 = -1 AND dbo_[Actual Reports]!Month05Value = 0 Then
strSQL = strSQL & "'YES' AS Breach5, " & _
"dbo_ActualReports.Month05DueDate, " & _
"dbo_ActualReports.Month05SignOff "
If DatePart("yyyy",dbo_ActualReports.Month05DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month05DueDate AS May "
End If
Else
strSQL = strSQL & "'NO' AS Breach5 "
End If
If dbo_[Actual Reports]!Active06 = -1 AND dbo_[Actual Reports]!Month06Value = 0 Then
strSQL = strSQL & "'YES' AS Breach6, " & _
"dbo_ActualReports.Month06DueDate, " & _
"dbo_ActualReports.Month06SignOff "
If DatePart("yyyy",dbo_ActualReports.Month06DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month06DueDate AS Jun "
End If
Else
strSQL = strSQL & "'NO' AS Breach6 "
End If
If dbo_[Actual Reports]!Active07 = -1 AND dbo_[Actual Reports]!Month07Value = 0 Then
strSQL = strSQL & "'YES' AS Breach7, " & _
"dbo_ActualReports.Month07DueDate, " & _
"dbo_ActualReports.Month07SignOff "
If DatePart("yyyy",dbo_ActualReports.Month07DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month07DueDate AS Jul "
End If
Else
strSQL = strSQL & "'NO' AS Breach7 "
End If
If dbo_[Actual Reports]!Active08 = -1 AND dbo_[Actual Reports]!Month08Value = 0 Then
strSQL = strSQL & "'YES' AS Breach8, " & _
"dbo_ActualReports.Month08DueDate, " & _
"dbo_ActualReports.Month08SignOff "
If DatePart("yyyy",dbo_ActualReports.Month08DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month08DueDate AS Aug "
End If
Else
strSQL = strSQL & "'NO' AS Breach8 "
End If
If dbo_[Actual Reports]!Active09 = -1 AND dbo_[Actual Reports]!Month09Value = 0 Then
strSQL = strSQL & "'YES' AS Breach9, " & _
"dbo_ActualReports.Month09DueDate, " & _
"dbo_ActualReports.Month09SignOff "
If DatePart("yyyy",dbo_ActualReports.Month09DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month09DueDate AS Sep "
End If Else
strSQL = strSQL & "'NO' AS Breach9 "
End If
If dbo_[Actual Reports]!Active10 = -1 AND dbo_[Actual Reports]!Month10Value = 0 Then
strSQL = strSQL & "'YES' AS Breach10, " & _
"dbo_ActualReports.Month10DueDate, " & _
"dbo_ActualReports.Month10SignOff "
If DatePart("yyyy",dbo_ActualReports.Month10DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month10DueDate AS Oct "
End If
Else
strSQL = strSQL & "'NO' AS Breach10 "
End If
If dbo_[Actual Reports]!Active11 = -1 AND dbo_[Actual Reports]!Month11Value = 0 Then
strSQL = strSQL & "'YES' AS Breach11, " & _
"dbo_ActualReports.Month11DueDate, " & _
"dbo_ActualReports.Month11SignOff "
If DatePart("yyyy",dbo_ActualReports.Month11DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month11DueDate AS Nov "
End If
Else
strSQL = strSQL & "'NO' AS Breach11 "
End If
If dbo_[Actual Reports]!Active12 = -1 AND dbo_[Actual Reports]!Month12Value = 0 Then
strSQL = strSQL & "'YES' AS Breach12, " & _
"dbo_ActualReports.Month12DueDate, " & _
"dbo_ActualReports.Month12SignOff "
If DatePart("yyyy",dbo_ActualReports.Month12DueDate = Year(Now()) Then
strSQL = strSQL & "dbo_ActualReports.Month12DueDate AS Dec "
End If
Else
strSQL = strSQL & "'NO' AS Breach12 "
End If
strSQL = strSQL & _
"FROM ((dbo_Borrowers INNER JOIN (dbo_BorrowerReports INNER JOIN dbo_ActualReports " & _
"ON dbo_BorrowerReports.BorrRepID = dbo_ActualReports.BorrRepID) " & _
"ON dbo_Borrowers.BorrID = dbo_BorrowerReports.BorrID) INNER JOIN dbo_Centres " & _
"ON dbo_Borrowers.CentreID = dbo_Centres.CentreID) INNER JOIN dbo_AccountManagers " & _
"ON dbo_Borrowers.AM_ID = dbo_AccountManagers.AM_ID " & _
"WHERE (((dbo_AccountManagers.AM_ID)<>459) " & _
"ORDER BY dbo_AccountManagers.AM_LastName, dbo_Borrowers.BorrowerName"
CurrentDb.Execute(strSQL)














Randy

 

[ConfusedNAccess]

This is awesome! It returns a compile error and highlights

Private Sub YourReport()in yellow.

1.If dbo_[Actual Reports]!Active01 = -1 AND dbo_[Actual Reports]!Month01Value = 0 Then

2.If DatePart("yyyy",dbo_ActualReports.Month01DueDate = Year(Now()) Then

1 and 2 are both highlighted in red. what does this mean and how can i correct? I'm hoping that you meant to copy and paste your text into a "new module".

I have no idea what i'm doing.

 

[randy700]

Create the function in the form module because of the ! symbols used. These indicate you are refering to controls on the current form. By putting the code in a separate module, Access won't know where to find the information.

I cut and pasted this code, but have no means of testing because I'm currently on a computer that does not have Access installed. You might have to play around with it a little to get the spacing and comma's correct.

Another "trick" you might want to try. Put stops in various places and check the SQL code in the immediate window. This will let you check a little at a time instead of trying to analyze the entire thing at once.

Good Luck!

Randy

 

[ConfusedNAccess]

what does expected list separator or ) mean? this is coming up for

If DatePart("yyyy","dbo_ActualReports.Month01DueDate" = Year(Now())then
strSQL = strSQL & "dbo_ActualReports.Month01DueDate AS Jan "
End If
Else

 

[randy700]

It mean I missed a closing paren ).
If DatePart("yyyy","dbo_ActualReports.Month01DueDate" = Year(Now()))

You'll have the same problem on EVERY line that uses this code.

Randy

 

[ConfusedNAccess]

The last statement with stars around it returned red with a syntax error.. ?????

Else
strSQL = strSQL & "'NO' AS Breach8 "
End If
If "dbo_[Actual Reports]Active09" = -1 And "dbo_[Actual Reports]Month09Value" = 0 Then
strSQL = strSQL & "'YES' AS Breach9, " & _
"dbo_ActualReports.Month09DueDate, " & _
"dbo_ActualReports.Month09SignOff "
If DatePart("yyyy", dbo_ActualReports.Month09DueDate = Year(Now())) Then
strSQL = strSQL & "dbo_ActualReports.Month09DueDate AS Sep "
***** End If Else*****
strSQL = strSQL & "'NO' AS Breach9 "

 

[ConfusedNAccess]

i've fixed that,

now it shows compile error: expected end sub

 

[randy700]

You need to look at the code. Obviously, the Else is misplaced on this particular line. I told you that it was going to be necessary to play around with the spacing, commas, etc.


Randy

 

[ConfusedNAccess]

Ok, i've fixed the compile errors.. and then runtime error 13 popped up.. and took me back to this highlighted..

***** If dbo_[Actual Reports]Active01 = -1 And dbo_[Actual Reports]Month01Value = 0 Then*****


"dbo_AccountManagers.AM_Transit, " & _
"dbo_Borrowers.BorrowerName, " & _
"dbo_Borrowers.SingleName, " & _
"dbo_Borrowers.Brr, " & _
"dbo_Borrowers.SRF, " & _
"dbo_Borrowers.ClientYearEnd, " & _
"dbo_ActualReports.ActualYear, " & _
"dbo_BorrowerReports.SheetRowText, " & _
"dbo_BorrowerReports.EffectiveFromDate, " & _
"dbo_BorrowerReports.EffectiveToDate, " & _
"dbo_BorrowerReports.Frequency, " & _
"dbo_BorrowerReports.GracePeriod, "
If dbo_[Actual Reports]Active01 = -1 And dbo_[Actual Reports]Month01Value = 0 Then
strSQL = strSQL & "'YES' AS Breach1, " & _
"dbo_ActualReports.Month01DueDate, " & _
"dbo_ActualReports.Month01SignOff, "
If DatePart("yyyy", dbo_ActualReports.Month01DueDate = Year(Now())) Then
strSQL = strSQL & "dbo_ActualReports.Month01DueDate AS Jan "
End If

 

[randy700]

It doesn't appear that you are even beginning to try and figure out anything with your code. I'll off assistance, but do not expect you to send a new question every time something minor occurs. It is patently obvious that a Sub needs an End Sub. It didn't get copied in when I gave you the code -- so add it.


Randy

 

[ConfusedNAccess]

Randy,

I apologize. Please bear with me. I'm VERY new to access, and don't understand code. I've never written anything in Code before. This is all Greek to me. I'm self taught, and apologize for asking questions regarding minor things.

I do appreciate your patience and you have been very kind to assist me.

 

[randy700]

As I stated earlier, I don't have the ability to test any code on the computer I'm presently at. You should carefully review the entire code, and look for any missing commas, improper spacing, etc. Look at the references to field names and controls, making sure the dot (.) isn't missing when required (I think it is in your 15:20 post).

Have you tried looking at the code, in smaller sections, in the immediate window? It can be extremely helpful. Also, once the code is completely built, you can copy it into a query object and test it.


Randy