VBA Code Not returning the expected results

Question

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.

Can I assume that the second Else is associated with the second If which is inside the first Else?? And that the third If is inside the second Else?? The poor indentation of your code makes it very confusing. I am making those guesses based solely on where the End Ifs are. — Mark Balhoff
– Mark Balhoff, Commented Oct 29, 2014 at 20:50
I submitted an edit formatting your code. The first step to debugging your code is being able to read it. — RubberDuck
– RubberDuck, Commented Oct 29, 2014 at 20:56
The table does not have a primary key. I pulled data from 20 tables into a single table and omitted the primary key so I didn't have issues importing tables that had duplicate IDs. — Jason
– Jason, Commented Oct 29, 2014 at 20:58
Yea you are going to have to format the If statement flow for readability. But regardless I don't see how you are using a single global wRuningCount and then expecting to get increments per Section ID. You need something more like say a dictionary that has a key of Section ID and a value of your running count. — Mark Balhoff
– Mark Balhoff, Commented Oct 29, 2014 at 20:59
Based on your output, it appears to be going in the second If statement. Therefore I think your only problem may be defining sectionid locally instead of globally. It is always empty and therefore wSEC is always empty. — Mark Balhoff
– Mark Balhoff, Commented Oct 29, 2014 at 21:31

Thomas G · Accepted Answer · 2016-03-18 19:24:28Z

1

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 ?

edited Mar 18, 2016 at 19:24

answered Oct 30, 2014 at 10:22

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jason Over a year ago

When I say, "DIM sectionid As String" am I not telling it what the sectionid data is? SectionID is a column in the data. I thought I was telling it to look at that.

Thomas G Over a year ago

No. You simply declare a local variable of type string inside your function and this variable is thus only exposed in this function. You should declare a new input parameter to your fonction to pass the SectionID: Function GetRunCount(Left_Img as string, SectionID as string) As Long

Jason Over a year ago

I changed my function line to,

Jason Over a year ago

I changed my function line to, Function GetRunCount(Left_Img As String, SectionID As String) As Long. Now I get the error, Undefined Function 'GetRunCountGetRunCount' in expression. I updated my query to, Runing count: GetRunCountGetRunCount([Left_Img as string],[SectionID as string]) I also tried to get rid of the AS String in each and it gave me the same Undefined Function message.

Jason Over a year ago

Noticed that in my query I had, Runing count: GetRunCountGetRunCount([Left_Img as string],[SectionID as string]) Changed it to, Runing count: GetRunCount([Left_Img as string],[SectionID as string]) and now get the error, Ambiguous name. in query expression 'GetRunCount([Left_Img],[SectionID])'.

|

Cheesenbranston · Accepted Answer · 2014-10-30 11:24:07Z

0

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

answered Oct 30, 2014 at 11:24

Cheesenbranston

681 silver badge7 bronze badges

Comments

Klaus · Accepted Answer · 2014-10-30 15:36:04Z

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

Collectives™ on Stack Overflow

VBA Code Not returning the expected results

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related