Starting and Ending an invoice with a "$" 1

[rcoutts]

I have an invoice that I want to have only the first and last dollar amount with a "$" and the other get none, e.g.,

  BALANCE
    Previous Balance    .    .    .    . $1000
    Amount Received     .    .    .    .    50
    Interest  .    .    .    .    .    .     3

  FEES
    Hourly Fee     .    .    .    .    .  2000

  CONSULTANTS
    Structural consultant for framing  .   500

  TOTAL AMOUNT DUE UPON RECEIPT   .    . $3453

The last one is no problem since I have "TOTAL AMOUNT..." in a footer, so I can specify it explicitly. The 1st "$" is a little trickier. The values for my invoice are read in from a table and sorted by Category. My table has (3) columns, e.g.,

   Category    Description               Subtotal
   ----------- ------------------------- --------
   BALANCE     Previous Balance              1000
   BALANCE     Amount Received                 50
   BALANCE     Interest                         3
   FEES        Hourly Fees                   2000
   CONSULTANTS Structural consultant          500

So the 1st value for Subtotal needs the "$," but not the subsequent ones. How can a put a test in my Report that checks if this is the first attempt to write a value for Subtotal and, if so, format it with a "$?"

Thanks!
Rich

 

[dawnd3]

Ok, I did something like that once, let's see if it helps you. I needed to add a value to only the first value in the report. So in the detail section, using the on print event I have the following code: ([BaseReturnAmt] being the field I wanted to add the value to just the first record)

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

lBaseValue = 0

If Me.CurrentRecord = 1 Then
lBaseValue = Me.BaseReturnAmt
End If
End Sub

Does this help? Dawn

http://www.calliopeconsulting.net

 

[rcoutts]

Your suggestion got me heading in the right direction. The "On Format" event gets triggered for every detail in my report. If I put in the line:

  MsgBox "Subtotal:" & lblSubtotal.OldValue

Then every time my report opens it shows a series of dialog boxes starting with the first value (the one I want to add the "$" to). So, it seems like I can play with format at this point, e.g., if its the first call to the event handler, change the format of lblSubtotal. Can you tell me how to do that? Looking at the properties of lblSubtotal, I don't see anything resembling format.

-Rich

 

[CosmoKramer]

Rich, Try this:

Add an invisible text box to your detail section. Set its control source to =1 and its Running Sum to Over Group.

In the On Format event of your detail section you could interrogate the value of your text box. If it's equal to 1, then format your subtotal field the way you want it......

 

[dawnd3]

Oh I forgot to tell you that part. I will use my example again. The lbasevalue in my example is then used in the formula in the control source of the feild. I am not very good at this and had help on mine. But what Kosmo says is accurate, I just set the running sum to over group for the field I was calculating. Then on the first record it added the value and after that it did not. I am sorry I am not more helpful. Dawn

http://www.calliopeconsulting.net

 

[rcoutts]

Thanks for the posts. They make sense to me and I'm sure they will work. I just have one, embarassingly simple question -- how does one change the format of a text box in VB? I know how to call the code when the Event gets triggered, but what is the function call to change a text box's format?

Thanks!
Rich

 

[dawnd3]

I am not sure but if you click in one of the properties to change the format, and hit the F1 key it should tell you the VBA code for that format. Dawn

http://www.calliopeconsulting.net

 

[CosmoKramer]

One easy way is to change the text box to the Currency format:

If me.txtCounter = 1 Then
  me.Subtotal.Format = "Currency"
End If