1
  • Access 2003 on Windows 7

enter image description here

I have a report grouped by sales person then customer. In the customer group footer I show the customer name, sales for Q1 2015 (calculated field) and sales for Q1 2016 (calculated field). The 2 calculated fields are called txtQ1a and txt Q1b.

If both txtQ1a and txtQ1b are blank or zero, I want to hide that customer. Here's what I've tried, though it's commented out now. 

Private Sub grpCustName_Format(Cancel As Integer, FormatCount As Integer)

'Me.grpCustName.Visible = (Nz(txtQ11Amt.Text) = 0) And (Nz(txtQ12amt.Text) = 0)
'Me.grpCustName.Visible = (Nz(txtQ11Amt) = 0) And (Nz(txtQ12amt) = 0)
'Cancel = (Nz(txtQ11Amt.Text) = 0) And (Nz(txtQ12amt.Text) = 0)

End Sub

I've tried several things but keep getting an "invalid procedure call" before my code is even run. The "invalid procedure call" highlights in yellow the function header that starts with "Private grpCustName_Format...".

Did I forget to link a library or something? What could be wrong here?

Thank you.

EDIT: My formula in the each Q1 2015 sales box: =DSum("[sls_amt]","tblSalesLastYear","[sls_amt] <> 0 and [billed_dt] >= 20150101 and [billed_dt] <= 20150331 and [cus_no] = [txtCustno] "). Same formula in Q1 2016 sales box but dates are different.

Bulrush
  • 548
  • 2
  • 4
  • 19
  • What's the purpose of `Cancel` arg ? And show how you call your sub – Thomas G Apr 26 '16 at 19:03
  • This is object oriented programming. The system, not the programmer, actually calls Private Sub grpCustName_Format and sends in the parameters. – Bulrush Apr 27 '16 at 15:32
  • Did you manually rename the "cus_name Footer" section to `grpCustName`? If this is the correct section name, the syntax is correct and the event procedure should work. A Decompile may help to make it work: http://stackoverflow.com/a/3268188/3820271 – Andre Apr 27 '16 at 18:03

1 Answers1

0

This should do:

Private Sub grpCustName_Format(Cancel As Integer, FormatCount As Integer)

    Me.grpCustName.Visible = (Nz(txtQ11Amt.Value, 0) = 0 And Nz(txtQ12amt.Value, 0) = 0)

End Sub

Alternative syntax:

    Me!grpCustName.Visible = (Nz(Me!txtQ11Amt.Value, 0) = 0 And Nz(Me!txtQ12amt.Value, 0) = 0)

Corrected for Not visible:

    Me!grpCustName.Visible = Not (Nz(Me!txtQ11Amt.Value, 0) = 0 And Nz(Me!txtQ12amt.Value, 0) = 0)
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I still get an "Invalid Procedure Call" error. I still suspect I'm using the wrong library but I have no idea which one. There used to be a section to check and uncheck what they call "References" but I don't see it anywhere. – Bulrush Apr 27 '16 at 15:36
  • thats a good catch anyway and you should still adapt your code with it, because with your strings and textboxes, `NZ()` will by default return a _zero-length string_ and not a `0` as you expect. Regarding your answer in my other comment, I still would like to see how you call your sub, it doesnt matter wether it's OO or not. The explanation might be there and not here. If you had a reference issue, it would throw errors during compilation. It looks like the error happens at runtime and I have the feeling it has something to do with your arguments. – Thomas G Apr 27 '16 at 16:07
  • You may try the alternative syntax I've added. _References_ are found unde menu Tools in the code windows. – Gustav Apr 27 '16 at 17:27
  • How do I find where the grpCustName_Format function is called? – Bulrush Apr 28 '16 at 10:05
  • This `Me.grpCustName.Visible = (Nz(Me.txtQ11Amt.Value, 0) = 0 And Nz(Me.txtQ12amt.Value, 0) = 0)` allowed the code to run so maybe the Invalid Procedure call was from the Nz() function. But when both value are zero, it's still not hiding that grpCustName_Footer. I'm sorry, but we are trying to get away from Access and I haven't used it much in the past 5 years. – Bulrush Apr 28 '16 at 10:08
  • Is there a better way to check for empty, blank, null or zero data? Should I be using IsEmpty() or IsNull()? – Bulrush Apr 28 '16 at 10:16
  • Correction, the code above is still getting an Invalid Procedure call. – Bulrush Apr 28 '16 at 10:31
  • Reverse the logic for Visible. See edit. _IsEmpty_ serves another purpose then checking for Null/Zero; _IsNull_ is covered by Nz which excatly checks for this. – Gustav Apr 28 '16 at 11:30