Easy question about skipping lines in report

[joebloeonthego]

In my report I would like to skip (hide) orders that were delivered ontime, but still count them for the totals.
To do this I've added 'Detail.Visible = ([field] > 0)' to the onFormat property of the detail band but it doesn't work. In fact, even when using 'Detail.Visible = False' it still shows. I've set all fields in detail section to 'canshrink' and the detail section itself to 'canshrink'.
Can anyone mention something I'm missing? (I'm pretty sure the visible=false + canshrink should equal it not showing because I made a summary report from the same report by just setting all fields to invisible and canshrink along with the detail to canshrink and it worked perfectly. Now i want to do the exact same thing except only make _some_ invisible instead of all.
Thanx!

 

[PaulBricker]

Normally you would set the field visible property not the detail visible property. Something like this in the OnFormat event for the Detail section.

If Me.[Field]>0 Then
Me.[Field].visible = False
Else
Me.[Field].Visible = True
End If

Unless I missed something.

Paul

 

[joebloeonthego]

that's what I would have thought, but I did a search before asking and someone was describing skipping lines with a null field and they just said:
Detail.Visible = IsNull([field])

so I figured it should work for me.. I can always do it for each field, it'd just be nice to not have to of course

 

[joebloeonthego]

I may just do that...

regarding your suggestion, if you are setting a variable true/false based on an IF, you can just do:
Me.[field].Visible = Me.[field] > 0
(unless of course you worded it that because a list of variables is expected which very well may be the case)

 

[PaulBricker]

No your second suggestion should work just fine. I felt the longer code would be a little more self explanitory but you have a grasp on the short form so see if it does what you need. In the mean time I will play with the first code and see what I get.

Paul

 

[joebloeonthego]

thread703-486165 is the one which mentions the short version.

 

[joebloeonthego]

I can't get anything like that to work. Is it code, expression, macro? I used code, and it doesn't recognize any variables, or complains of data-type mismatch. Right now, I'm trying Reports![fieldname], but I really don't know what to use.. I've only really done expression building.. which won't work, because i need to set the visible property on 10-15 fields.
any help would be MUCH appreciated!

 

[PaulBricker]

I got it to work on the first try.
This is my code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.Water_Charge < 20
End Sub

This is right in the Detail_Format event.

Try putting the code back in your report and then copy and paste it here (including the Private Sub line).

Paul

 

[joebloeonthego]

yeah, it was just syntax I guess. I had to change my field name from [% on time] to 'percent' in order to get it to run, but it crashes on the error:

Run-time error '62506'
Microsoft Access can't find the name 'end date' you entered in the expression.

You may have blah blah blbah blah

this ONLY happens if I put code in the detail event. Keep in mind the only code I have is this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.percent < 1
End Sub

'end date' is one of two fields I'm prompted to enter as I run the report. They're in the query the report is taken from, and are 'beg date' and 'end date' to select a range to print. I'm guessing it's the space? I have to go back and rename all my variables? (never should have had spaces in the first place, but if access is going to let you, then they should really let you!)

 

[PaulBricker]

If they are a parameter they should be inside [] like this
Between [enter beg date] and [enter end date]

Then Access will know what they are. Are you using parameters like this?

Paul

 

[joebloeonthego]

well, I changed them to begindate and enddate, and I get the same error:
can't find the name enddate you entered in the expression.
and when I click debug, it takes me to the line of code:
Me.Detail.Visible = Me.percent < 1

and those two variables are not showing in the field list (they're not fields in the query, just used in the query) so what would I reference them as?

 

[PaulBricker]

Not a clue. How are you using them in the query. Can you post the sql for it.

Paul

 

[joebloeonthego]

SELECT [on time shipments].CUSTID, [on time shipments].OENO, [on time shipments].LINEITEM, [on time shipments].PN, [on time shipments].DESC, [on time shipments].UM, [on time shipments].SCHDSHIP, IIf([on time shipments].[status]=&quot;4&quot; Or [on time shipments].[status]=&quot;5&quot; Or [netshipqty]>[qtyord],[netshipqty],[qtyord]) AS OrderQty, [OrderQty]*[usell] AS [Order $], [on time shipments].NetShipQty, IIf([OnTime]>[netshipqty],[netshipqty],IIf([OnTime]<[OrderQty],[ontime],[OrderQty])) AS [On Time], [On Time]*[usell] AS [Ontime $], [OrderQty]-[On Time]-[Outstanding] AS QtyLate, [QtyLate]*[usell] AS [Late $], [on time shipments].[Last Shipped], IIf([OrderQty]-[Ontime]-[late]>0,[Orderqty]-[ontime]-[late],0) AS Outstanding, IIf([On Time]=0,0,[OnTime $]/[Order $]) AS [% on Time], [on time shipments].count, customer.COMPANY, [Outstanding]*[usell] AS [Outstanding $], IIf([% on time]=1,1,0) AS otcount, IIf([Outstanding]>0,1,0) AS outcount, [on time shipments].STATUS, IIf([Late]>0,1,0) AS latcount
FROM [on time shipments] INNER JOIN customer ON [on time shipments].CUSTID = customer.CUSTID
WHERE ((([on time shipments].SCHDSHIP)>=[begindate] And ([on time shipments].SCHDSHIP)<=[enddate]));

I know it's a big mess, but they're right there in the WHERE clause. I just stick a random variable in there, and access will prompt you for it when you run the query.

 

[PaulBricker]

The parameters look fine. Check the name of your textbox in the Report and make sure it's not something like Text22 instead of [percent]. Also, be careful because Access has reserved words that will cause a lot of problems if used incorrectly, like in a field name.

Paul

 

[joebloeonthego]

The thing is, if I don't have any onformat code, there is no problem, it works fine. If I do add the code, it chokes on said code with the error that access can't find my 'ending date' or 'enddate' or 'end date' or whatever I call it, even though there is no reference to it in the specified code!
I think this is something I need to play around with in order to find out just what's going on..
Thanx for the syntax with the code though, at least I got that far! I'll keep the thread posted....

 

[PaulBricker]

Thanks. Good Luck. Post back when you get so progress.

Paul

 

[joebloeonthego]

the problem was referencing the label, instead of the query's field. First of all, if the field name has spaces in it, it won't show up in the list (vb drop-down list of available objects), but the name of the label will. But you can't use that, cause it throws the wierd error.. so I renamed the field, and reference that, and now it works.
BUT, if I run the report for more than 10 (or so) day's worth of data, I get an overflow error. I will start a new thread for this problem.
thanx for all your help.