Results 1 to 2 of 2
  1. #1
    Shingo is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    2

    Concatenate Formula

    I am trying to concatenate two calculated values for a report in VB code. I am looking to find a coverage statistics based of a few count equations. The code I have so far is below:

    "=iif(sum([Ccovn" & X & "])= null,0,sum([Ccovn" & X & "]))" & " | " & IIf(sum([Ccovn" & X & "]) = Null, 0, sum([Ccovn" & X & "]) / [Count_S1S2])

    I'm looking for the equation to show a number of items and then the percentage of that number from the total available. Additionally, the second part of the equation should be a percentage. I tried using a formatpercent( function but I had difficulty.

    Ultimately the cell would look like this " 8 | 56% "



    Thanks for any help.

    -Shingo

  2. #2
    tlittell is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Location
    Pittsburgh, PA
    Posts
    8
    Use variables; do your logical comparisons (Iif) when you set the value of the variables. Do your concatenating and formatting where you set the display value for the control. This works:

    Code:
     
    Option Explicit
    Public Sub Command1_Click()
    Dim a As Integer
    Dim b As String
    a = IIf(DSum("[Count]", "test1") = Null, 0, DSum("[Count]", "test1"))
    b = IIf(DSum("[Count]", "test1") = Null, 0, (DSum("[Count]", "test1") / DSum("[total]", "test1")))
    Text5.SetFocus
    Text5.Text = a & " | " & Format(b, "Percent")
    End Sub
    I've attached a screen print showing the test data and the result. Hope this helps.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need Code to Concatenate All Records
    By menntu in forum Programming
    Replies: 4
    Last Post: 06-05-2009, 09:43 AM
  2. concatenate string using loop
    By nengster in forum Programming
    Replies: 0
    Last Post: 02-23-2009, 08:05 PM
  3. formula on report??
    By dike969 in forum Access
    Replies: 0
    Last Post: 03-05-2007, 01:58 AM
  4. Max formula in Access
    By mohsin74 in forum Programming
    Replies: 2
    Last Post: 12-26-2006, 07:21 AM
  5. Concatenate two fields (text & number) for key field
    By Larry Elfenbein in forum Forms
    Replies: 2
    Last Post: 11-10-2005, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums