Help in report

[sachuchem22]

Hi
i have an report with fields Student Name ,Roll No ,subI subII,SubIII,subIV In which i to sum all sub. so i add unbound txtbox and in controlsource add expression =(subI)+(subII)+(SubII)+(subIV). It work perfectly when all sub having marks but from 4 sub only 3 are compulsory and one optional so when optional sub not having marks sum is not take place and when i add zero to optional sub sum take place but i want add dashes (--) in that sub than zero. How can i do this

[Narender Sagar]

Let's assume Sub4 is optional. You can create two columns in a query. In first column give it a name e.g. Sub4a : Apply "if" formula something like : IIf([Sub4]>0,[Sub4],0) and do calculation part based on this column of the query.
Create another column in the query e.g. Sub4b : Apply "if" formula again something like : IIf([Sub4]>0,[Sub4],"-") and display this field in report/form.
So my point is : for display purpose you can use 'Sub4b' and for calculation purpose use 'Sub4a'

[PhilOfWalton]

Try

expression =Nz(subI)+Nz(subII)+Nz(SubIII)+Nz(subIV)
In other words, if one of the Sub is blank, treat it as .

Phil

[Narender Sagar]

Phil, this will definitely resolve calculation part, however, they also want that the value should appear either the number or (--) dashes, instead of zeros (0).
Thanks

[PhilOfWalton]

Not too difficult.
On your report, create 4 labels identical in size to the Sub... box
The label caption is "--" your dashes. Aligned Right and the same border as your Sub.. Text box.
Put those labels directly over their corresponding text boxes.

Now we need a bit of code to either show the text box or the label.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 
    If Nz(SubI) = 0 Then
        SubIDash.Visible = True
        SubI.Visible = False
    Else
        SubIDash.Visible = False
        SubI.Visible = True
    End If
 
    If Nz(SubII) = 0 Then
        SubIIDash.Visible = True
        SubII.Visible = False
    Else
        SubIIDash.Visible = False
        SubII.Visible = True
    End If
 
    If Nz(SubIII) = 0 Then
        SubIIIDash.Visible = True
        SubIII.Visible = False
    Else
        SubIIIDash.Visible = False
        SubIII.Visible = True
    End If
 
    If Nz(SubIV) = 0 Then
        SubIVDash.Visible = True
        SubIV.Visible = False
    Else
        SubIVDash.Visible = False
        SubIV.Visible = True
    End If
 
End Sub
 
 

Phil

[jforbes]

Another option is to perform the substitution in the ControlSource of your unbound TextBox. You can change your ControlSource to something like this:

=IIf(NZ([subI],0)=0 OR NZ([subII],0)=0 OR NZ([SubII],0)=0 OR NZ([subIV],0)=0 ,"-",[subI]+[subII]+[SubII]+[subIV])

[sachuchem22]

Thanks to all you i will try your solution and i hope it help me.