If Statement

[Gismo]

Hi all,

If non of the below if statements work, what else could I look at?

Private Sub Report_Current()
'If IsNull(Me.ADHOC) Then
If Me.ADHOC = " " Then
'If Me.ADHOC <> 0 Then
Me.AdHocLbl.Visible = False
Else
Me.AdHocLbl.Visible = True
End If
End Sub

 

[Minty]

What type of data is ADHOC?
One of the more reliable ways to check for null or empty strings is to use

If Len(Me.ADHOC & "") = 0 Then ...

 

[Gismo]

What type of data is ADHOC?
One of the more reliable ways to check for null or empty strings is to use

If Len(Me.ADHOC & "") = 0 Then ...

ADHOC is a Long Integer

Above code does not give the desirable outcome either

 

[Minty]

What happens if you

Debug.print "My Value: " & me.ADHOC

I bet you 2p get 0

 

[Gismo]

What happens if you

Debug.print "My Value: " & me.ADHOC

I bet you 2p get 0

no debug.print output

 

[Minty]

What not even
My Value:

In that case the event isn't firing.

 

[Minty]

I just noticed your using reports.

Try the detail format event.

 

[Gismo]

I just noticed your using reports.

Try the detail format event.

Still no luck

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Len(Me.ADHOC & "") = 0 Then
Me.AdHocLbl.Visible = False
Else
Me.AdHocLbl.Visible = True
End If
End Sub

 

[Minty]

Again add back in the debug to see if the event is even firing.
Printing off the value will give you a proper indication of what is happening and what the value is.

 

[Gismo]

Again add back in the debug to see if the event is even firing.
Printing off the value will give you a proper indication of what is happening and what the value is.

no result with debug.print

 

[Minty]

So the event isn't firing then.
Why not make a calculated field in the underlying query.

MyADHOC : IIf([ADHOC] = 0, Null, "ADHOC : " & [ADHOC])

Then make a control on the report to hold it.

 

[Gismo]

I also tried using code in the query

ADHOC: IIf([Ad-Hoc]=" ","Refer to full report",0)

 

[Minty]

A long integer will never = " " will it?

 

[Gismo]

So the event isn't firing then.
Why not make a calculated field in the underlying query.

MyADHOC : IIf([ADHOC] = 0, Null, "ADHOC : " & [ADHOC])

Then make a control on the report to hold it.

Sorry, send the message just as your comment came through

 

[Gismo]

Sorry, send the message just as your comment came through

ADHOC: IIf([Ad-Hoc]=Null,"Refer to full report")

For some reason, I still dont get the output in the query either

 

[Gasman]

For some reason, I still dont get the output in the query either

View attachment 99189

You need to check your data is correct, before applying code that refers to it

 

[Gismo]

The data type is long integer
The data comes from a pivot table, all data refers to long integer

I am having the same issue on a report to make the border of a control transparent when no data and this is a text

Private Sub Report_Current()
If IsNull(Me.Description) Then
Me.Description.BorderStyle = 0
End If
End Sub