Format Text Box Based on Condition

[ConfusedNAccess]

How can I format at text box, or field on a report to remain blank if the corresponding field is null?

I don't want data in one field to appear if data in any of the corresponding fields are blank, or have null values

Any assistance would be great!

 

[ConfusedNAccess]

1. What type of values go in the Jan, Feb, Mar etc. fields? Are they numbers or text? date fields


2. If there is no data entered in those fields, Jan through Dec, what shows? Nothing (a Null value)

3. Where does the report name (SheetRowText) come from?

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_Borrowers.Borr_NumBreaches, dbo_Borrowers.Borr_NumCovBreaches, dbo_Borrowers.Borr_NumCovNonFinBreaches, dbo_Borrowers.Borr_NumMarginBreaches, dbo_Borrowers.Borr_NumLABreaches, dbo_Borrowers.Borr_NumRepBreaches, dbo_Borrowers.Borr_NumEWSNonFinBreaches, 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;

4. You say the months are grouped. no-grouped by Account Managers name and then by borrower.

 

[THWatson]

Meryn
Whew! That's a huge SQL to wade through!

I see now that SheetRowText is an actual field in the dbo_BorrowerReports table.

It's not clear to me where things are breaking down on you.

Tell me these things...
1. What would a correct report look like?
2. What are you getting now?

If I can't figure it out from that, I'm not sure I know what further to suggest.

Tom