Solved

Array Question

Posted on 1998-09-18
7
200 Views
Last Modified: 2010-04-30
If I have an access db named salesinfo that has a table called table1.

just assume it has a field known as totalsales and a field known as month, and year, and also has a field salesperson.

I want to put in an array, the sales data where the salespersons name ="david" and the year="1998", and it needs to be grouped by month.  (jan, feb, march, etc)

I need this data in an array, because I want to use mschart with this array.  I need to understand how to do this array, so I can do similar arrays on other criteria.

Thank you.
0
Comment
Question by:checkout
  • 4
  • 2
7 Comments
 
LVL 2

Expert Comment

by:MelissaC
ID: 1435520
Just declare the array:

dim myArray(1) as currency
dim count as integer
count=1
myArray(count-1)=ReadFirstItemFromTable()


Each time you put an item in the array, you can redim the array:
count=count+1
Redim PRESERVE myArray(count)  'the preserve makes sure the older
                               'values stay in the array
myArray(count-1)=ReadNextItemFromTable()

The grouping by month can be done in de sql-statement
"select sales from table1 where name="david and year="1998" group by month

0
 

Author Comment

by:checkout
ID: 1435521
Melissa,

Thank you for your time, I am sure your example may work, but I need a working example. I gave the details so I can have code written, Then I learn how to write an array.  At this point, I have never done this before so it is very new to me.  If you could be so kind as to write the code for me, I will appriciate it!


Thank you for your time.



0
 
LVL 1

Expert Comment

by:jforbes
ID: 1435522
You can make a Type, then array the type.

Option Explicit
   Dim Total as long
'Set type to use in the array
Private Type typSalesInfo
     Dim lTotalSales as Long
     Dim sMonth as String
     Dim sSalesPersonName as String
     Dim sSalesYear as String
End Type

'Set an increment counter to expand the array by if it exceeds 'limit
Private Const cstSalesInfoInc = 1000

'Declare the array as the type created above
Private arrSalesInfo() as typSalesInfo

Private Sub FillArr()
   On Error Goto ErrProc
   'Set for array count
   Dim count as long
   'This sets the array for 1000 as the type
   ReDim arrSalesInfo(cstSalesInfoInc) As typSalesInfo
 
   do while 'Some Crieteria, most likely database EOF  
    count = count + 1
'***** In this area you can add you data reading calls from   'the database

'once you receive info you can enter it in the array this way

  arrSalesInfo(count).sSalesPersonName = "david" 'Rs!SalesPerson
  arrSalesInfo(count).sSalesYear = "1998" ' This could also be rs

Loop  
   
   Total = count
'This capture the out of dimension error and redims the array.
ErrProc:
   If Err.Number = 9 Then
      ReDim Preserve arrSalesInfo(UBound(arrSalesInfo) + cstSalesInfoInc) As typSalesInfo
      Resume
   End If
End Sub

Once you retrieve all info you can just do a for next to retrieve it.

Hope this Helps

John M. Forbes
Programmer/Analyst
Competitive Technologies Inc.


0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Expert Comment

by:jforbes
ID: 1435523
By the way Melissa is right you should probably do an SQL Statement to filter the info.

dim SQL as String
Bare in mind name of data fields need to be changed to accomadate
SQL = "SELECT SalesPersonName, Year FROM SalesInfo WHERE SalesPersonName = "david" AND Year = "1998"
0
 

Author Comment

by:checkout
ID: 1435524
I am having problems with your example, first:

Private type statement says "invalid outside procedure"

I removed the "dim" and it worked.  I am having problems with
arrsalesinfo(count).ssalespersonname="david"
arrsalesinfo(count).ssalesyear="1998"

I get subscript out of range.  I created a workspace and opened the db and the recordset.  but How do I tie the recordset to this array?  I feel like I am missing some pieces.

Anyhelp is appreciated

0
 
LVL 1

Expert Comment

by:jforbes
ID: 1435525
First the Type can only be in the Declaration Section of the Program, and when declaring variables under Option Explicit you should use the Private or Public instead of Dim.  Read the differences about them in the online help.

The array must be Dimentioned before it can be used.

The redim statement below must be done right when you enter the sub.

If you are entering the sub multiple times, you would want to use the Redim Preserve statement

If you have more than 1000 records in the database, and are trying to run the array, you must turn off Break on All Errors, because the array will be over the limit and have to be sent to the ErrProc to be redimentioned.

Put the following all in one sub.
Private Sub FillArr()
   On Error Goto ErrProc
   'Set for array count
   Dim count as long

   'This sets the array for 1000 as the type
   ReDim arrSalesInfo(cstSalesInfoInc) As typSalesInfo
 
   do while 'Some Crieteria, most likely database EOF  
    count = count + 1
'***** In this area you can add you data reading calls from the database

'once you receive info you can enter it in the array this way

  arrSalesInfo(count).sSalesPersonName = "david" 'Rs!SalesPerson
  arrSalesInfo(count).sSalesYear = "1998" ' This could also be rs

Loop    
   
   Total = count
'This capture the out of dimension error and redims the array.
ErrProc:
   If Err.Number = 9 Then
      ReDim Preserve arrSalesInfo(UBound(arrSalesInfo) + cstSalesInfoInc) As typSalesInfo
      Resume
   End If
End Sub
0
 
LVL 1

Accepted Solution

by:
jforbes earned 90 total points
ID: 1435526
When trying a recordset to the database, just say the following:
Bare in mind that these field names may not total match yours

say there were 2 fields in your database Table1

SalesPerson
SalesYear

Say your recordset workspace is rs

    arrSalesInfo(count).sSalesPersonName = rs!SalesPerson
    arrSalesInfo(count).sSalesYear = rs!SalesYear

when a value of something is on the right of the = sign then the value on the left receives the value of the right.

now if the clause was the following:
    rs!SalesYear = arrSalesInfo(count).sSalesYear
the recordset would now equal the array.

a way you can test to see if your array is full
put this statement right after the

    arrSalesInfo(count).sSalesPersonName = rs!SalesPerson
    arrSalesInfo(count).sSalesYear = rs!SalesYear
    debug.print arrSalesInfo(count).sSalesPersonName, arrSalesInfo(count).sSalesYear

This will print the values that are stored within the array on the immediate window.

Hope this Helps

-John M. Forbes
Programmer/Analyst
Competitive Technologies Inc.
jforbes@comptechinc.net
jforbes@nwrain.com
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now