?
Solved

Array Question

Posted on 1998-09-18
7
Medium Priority
?
212 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 180 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month7 days, 19 hours left to enroll

765 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