CODEWITHSUNDEEP
ms-accessvba
Jason
Jason

Reputation: 11

VBA Code Not returning the expected results


I have some VBA that I am using in a module in access. My Data looks like,

SectionID------Left_Img
---------------------------------
---19--------------394
---26--------------781
---83--------------842
---83--------------450
---83--------------453
---83--------------456
--104--------------621
--104--------------622

I need to generate a sample number for these numbers. The data should look like this,

SectionID------Left_Img-----Sample--
--------------------------------------------------
---19--------------394-----------------1
---26--------------781-----------------1
---83--------------842-----------------1
---83--------------450-----------------2
---83--------------453-----------------3
---83--------------456-----------------4
--104--------------621-----------------1
--104--------------622-----------------2

I have written some vba but it is returning the following results,
SectionID------Left_Img-----Sample--
--------------------------------------------------
---19--------------394-----------------1
---26--------------781-----------------2
---83--------------842-----------------3
---83--------------450-----------------4
---83--------------453-----------------5
---83--------------456-----------------6
--104--------------621-----------------7
--104--------------622-----------------8

Here is the VBA I wrote. I should mention this is my first time using VB so speak slowly when replying,

Option Compare Database
Option Explicit

Public wIMG As String
Public wSEC As String
Public wRuningCount As Long

Function GetRunCount(Left_Img) As Long
    Dim sectionid As String

    If wSEC = sectionid And wIMG = Left_Img Then
       wRuningCount = wRuningCount
       wSEC = sectionid
       wIMG = Left_Img
    Else<b>
        If wSEC = sectionid And wIMG <> Left_Img Then
            wSEC = sectionid
            wIMG = Left_Img
            wRuningCount = wRuningCount + 1
        Else
            If wSEC <> sectionid Then
                wSEC = sectionid
                wIMG = Left_Img
                wRuningCount = 1
            End If
        End If
    End If

    GetRunCount = wRuningCount
End Function

Anyone have any idea what this isn't working as I expect?

Thanks for your time.

Upvotes: 0

Views: 153

Answers (3)

Thomas G
Thomas G

Reputation: 10226

You have 2 issues :

1. Your sectionid variable is local and you don't assign a value to it. Thus its always a blank string.

It should therefore fall into the latest if : If wSEC <> sectionid Then, however following your results, your wRuningCount counter is incremented each time, which means that it enters the second if : If wSEC = sectionid And wIMG <> Left_Img Then

The conclusion is the second issue:
2. when you enter your function for the first time, your global wSEC variable is also a blank string. And it constantly stay blank because you assign a blank to it each time: wSEC = sectionid

It is difficult to give you a code that works without having the complete picture, ie: how do you assign a value to, or initialize your global variables ?

Upvotes: 1

Klaus
Klaus

Reputation: 902

Could I describe your requirement as follow:

  • if I have a new SectionID then start with Sample #1
  • if I have a different Left_Img in the same section then increment the Sample by 1

So your code could look like:

Option Compare Database
Option Explicit

Private lastSection  As String
Private lastLeft_Img As String
Private RunningCount As Long

Public Sub Reset
  lastSection = vbNullString
  lastLeft_Img = vbNullString
  RunningCount = 0 
End Sub

Public Function GetRunCount(ByVal SectionID as String, ByVal Left_Img As String) As Long
  If SectionID <> lastSection Then
    RunningCount = 1
    lastLeft_Img = Left_Img
  Elseif Left_Img <> lastLeft_Img Then
    RunningCount = RunningCount + 1
    lastLeft_Img = Left_Img
  Else
    ' We have a duplicate Entry ??
  End If
  GetRunCount = RuningCount
End Function

Remarks:

  • Don't make your Variables 'Public'
  • Declare the Type of your Function Parameters
  • Declare your function parameters 'ByVal' unless you explicitly want to change the value of this parameter
  • name things consistently

Upvotes: 0

Cheesenbranston
Cheesenbranston

Reputation: 68

As others have mentioned, sectionid is initialized as a zero length string "" and remains that way. You need to modify the function GetRunCount to include an argument of sectionid

Function GetRunCount(Left_Img, sectionid)

remove the declaration of sectionid in the function GetRunCount

and in the calling procedure, pass the sectionid along with Left_Img

Upvotes: 0

Related Questions