Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Unanswered: Adding (math) problem with variable data type

    I am having a problem when adding. The problem is located at:

    PctComp = PctComp + Pct

    It does not return a value. I am working on setting up a percentage complete label. For each record in the recordset, it will add to the percentage total.
    The total code is not complete, but this part has me stumped. I've searched, but searching adding brings back everything.

    Thanks,
    Chris



    What I have so far is:

    Dim PctComp As Integer
    Dim Pct As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ttl, cnt As Integer
    cnt = 0
    PctComp = 0
    Set db = CurrentDb
    Set rs = CurrentDb.OpenRecordset("select UserID,Email_address from UserList where user_status = 'PENDING")
    rs.movelast
    ttl = rs.RecordCount
    Pct = 100 / ttl
    rs.movefirst
    Do While Not rs.EOF
    If rs.RecordCount > 0 Then
    cnt = cnt + 1
    PctComp = PctComp + Pct
    Me.Percent_lbl.Caption = PctComp
    'send the email here
    Me.Form.Repaint
    End If

    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    How are you planning to calculate the percentage complete without knowing the total count of records?

    What is the point of cnt? You add 1 to it in every loop but you never use it.

    You also seem to be doing extra work, all you need to calculate the percentage complete is the total record count and the pending record count

    total count can be found with "select count(UserID) from UserList"
    pending count can be found with the query in your question

    percentage complete is: (total count - pending count) / pending count

    Steve

  3. #3
    Join Date
    Oct 2006
    Posts
    110
    Thanks Steve for your quick reply.

    The cnt = cnt + 1 is going to be used in a label to show the record count, for example 1 of 100, etc.

    My total is the count of the recordset.
    rs.movelast
    ttl = rs.RecordCount


    I do not have a total and separate pending record count. My total is the total pending records.

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    well, ttl is going to be the count of pending records and when you loop through the record set adding 1 to cnt each time the value of cnt is going to be the count of pending records. so ttl will equal cnt... not very useful for calculations.

    Take a look at this (from your first post)
    Code:
    Set rs = CurrentDb.OpenRecordset("select UserID,Email_address from UserList where user_status = 'PENDING")
    rs.movelast
    ttl = rs.RecordCount
    Pct = 100 / ttl
    your recordset will contain every pending record so ttl will be the count of pending records. For the sake of argument lets say this is 40

    Now Pct will be 100 / 40 or 2.5.

    Continuing on with the code, you have:
    Code:
    rs.movefirst
    Do While Not rs.EOF
    If rs.RecordCount > 0 Then
    cnt = cnt + 1
    PctComp = PctComp + Pct
    so you move back to the start of the recordset and loop thru it increasing the value of cnt as you go along. when you are done, cnt will be the same as ttl.

    You gave PctComp a value of 0 early on, so now it will be 0 + 2.5 on the first loop and by the end it will be 100. It will always be 100 regardless of the record count.

    In addition, you have Me.Percent_lbl.Caption = PctComp inside your loop so you are setting the caption for every record instead of just the final answer

    Steve

  5. #5
    Join Date
    Oct 2006
    Posts
    110
    I understand what you saying, but the problem I was having is

    PctComp = PctComp + Pct did not return a value. It stayed at 0.

    I changed:
    Dim PctComp As Integer to a Variant.
    Now it is incrementing with each record.

    The cnt was never meant to give me a total from the start, more of a progress of what's going on while the process runs, much as the same as the percentage.

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Why do you have PctComp and Pct as Variants anyway? You know the value is always going to be a number so why not use double? It's half the size of a variant and easier to deal with.

    Steve

  7. #7
    Join Date
    Oct 2006
    Posts
    110
    I've changed it. Thanks for the tip.
    I haven't ventured out much with variables. Not too sound too newb, but I rarely have a need for anything outside of string & integer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •