Using Arrays To Make Data Display Correctly...NOT WORKING! HELP!

[jawilli1]

Hi Folks,
I've got a tough one here that I've been trying to work though every which way I can think of. I know i'm getting closer, because the concept is making more sense now, but I don't think I got the code correct.

I'm trying to take in everyone who is set to graduate in one of our databases, and format it like below (automatically).

DEGREE
Major: Name, City, State; Name, City, State; etc...

DEGREE 2
Major: Name, City, State; etc...

I can easily pump this out where each person is on their own line, but that format is going to cause me or whoever works with this in the end to have to do alot of manually manipulation (which I'm tryign to avoid).

All of this being so, I come with with the below code:

Public Function InsertGradData()
DoCmd.SetWarnings False
Dim strSQL1, strSQL2, strSQL3 As String
Dim rs1, rs2 As DAO.Recordset
Dim myArray() As String
Dim r As String
Dim c As String

strSQL1 = "SELECT DISTINCT PeopleGraduating.Degree, PeopleGraduating.Major1 From PeopleGraduating order by Degree"
Set rs1 = CurrentDb.OpenRecordset(strSQL1)
Do Until rs1.EOF
        r = RTrim(rs1![Degree])
        c = RTrim(rs1![Major1])
        ReDim Preserve myArray(r, c)
        strSQL2 = "SELECT PeopleGraduating.FullName, PeopleGraduating.City, PeopleGraduating.Degree, PeopleGraduating.Major1, PeopleGraduating.State FROM PeopleGraduating" & _
        " Where PeopleGraduating.Degree='" & r & "' and PeopleGraduating.Major1='" & c & "'"
        Set rs2 = CurrentDb.OpenRecordset(strSQL2)
        strSQL3 = strSQL3 & "; " & rs2![FullName] & ", " & rs2![City] & ", " & rs2![State]
        DoCmd.RunSQL "INSERT INTO JAWILLI1_TMP_GRADS (MAJOR,DEGREE,NAMES) VALUES('" & rs1![Major1] & "','" & rs1![Degree] & "','" & EscapeOffRegexChars(strSQL3) & "');"
Loop
End Function

I've been looking up how to do a multidementional array in VBA, but all I could find what tthat ReDim function. Because I want to store 2 values on each index, I figured a 2 dimensional array would work. But I'm getting a type mismatch error. All of the data in the tables is varchar or varchar2, and I think I'm declaring the array correctly..but i'm not sure. Can someone Please help me out with this issue that I'm tryign to resolve. I've done alot of research and the above code is what I've come up with but it doesn't seem to want to work.

ANY IDEAS? HELP!

 

[RuralGuy]

Can't you group by DEGREE and get it to work? Reports have a lot of flexibility in grouping.

 

[jawilli1]

I can group it just fine, the issue I run into is the formatting they want it in. They want it formatted with the names listed as such by major.

name, city, state; name, city, state; name, city, state; etc.... Like I had said in my origional post I can pull this information where everythign is grouped correctly, but the name, city, state; are each on their own line. I need them all put together (like one big paragraph), for each major (under a degree).

 

[RuralGuy]

Isn't that just another group under degree? I would think you could achieve the following:

DEGREE1
Major1: Name, City, State; Name, City, State; etc...
        Name, City, State; Name, City, State; etc...
        Name, City, State; Name, City, State; etc...
Major2: Name, City, State; Name, City, State; etc...
        Name, City, State; Name, City, State; etc...
        Name, City, State; Name, City, State; etc...

DEGREE 2
Major1: Name, City, State; etc...
        Name, City, State; etc...
        Name, City, State; etc...
Major2: Name, City, State; etc...
        Name, City, State; etc...
        Name, City, State; etc...

 

[jawilli1]

It is another degree group and what you displayed I can do and have already done, but they want it to display the name information like this.

DEGREE
Major: name, city, state; name, city, state; name, city, state; etc...

DEGREE
Major: name, city, state; name, city, state; etc....


They don't want each "name, city, state;" on their own line, they want it as one big grouping of names (side by side...essentially all the info on one big long line)

It this concept that is posing trouble to me.

 

[jawilli1]

Here is an example of what we used last year (and how they want it this year...last year they did all of this manually...ug)

BACHELOR OF ARTS

American Studies/Communication Studies: Shauna Lynn White, DeSoto, Texas; John Paul Jones, Daytona, Ohio.

American Studies/Sociology: Michael Charles Chronister, Port Orange.

Art: Nayda Aurora Cuevas, Deltona; Ann West Hall, DeLand; Jennifer Lee Knight, Orlando; etc.

Art/English: Katheryn Denise Wright, DeLand.

Communication Studies: Elizabeth Anjel Arzillo, Spring Hill; Mark Franklin Burkhalter, Douglasville, Ga.; Robert C. Curtis, DeLand; etc.



see how all of the people are next to each other, instead of each person having their own line.

 

[RuralGuy]

Sorry, I missed the sequence on each line! You might be able to do it with a SubReport and I know you can do it with a RecordSet and create a temp table for the report.

 

[jawilli1]

any code ideas?

 

[RuralGuy]

Have you ever walked through a table with a RecordSet using MoveNext?

Edit: I don't want to do it for you but I'd be glad to assist you in doing it.

 

[jawilli1]

This is my new code:

Public Function fExportData22()
DoCmd.SetWarnings False
Dim strSQL, strSQL1, StrCnt As String
Dim rs, rs1     As DAO.Recordset
strSQL = "SELECT PeopleGraduating.Graduation_Date, PeopleGraduating.FullName, PeopleGraduating.City, PeopleGraduating.Degree, PeopleGraduating.Major1, PeopleGraduating.State FROM PeopleGraduating;"
Set rs = CurrentDb.OpenRecordset(strSQL)
StrCnt = "Select Distinct PeopleGraduating.Degree, PeopleGraduating.Major1 From PeopleGraduating"
Set rs1 = CurrentDb.OpenRecordset(StrCnt)
With rs1
  If Not .EOF Then
    .MoveLast
    .MoveFirst
  End If
End With
Dim counter As Integer
'strSQL1 = ""
Do Until rs1.EOF
    strSQL1 = strSQL1 & "; " & rs![FullName] & ", " & rs![City] & ", " & rs![State]
    DoCmd.RunSQL "INSERT INTO JAWILLI1_TMP_GRADS (MAJOR,DEGREE,NAMES) VALUES('" & rs1![Major1] & "', '" & rs1![Degree] & "','" & EscapeOffRegexChars(strSQL1) & "');"
    rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
End Function

it is creating only one instance of a major / degree program as it should, and it is counting the correct number of people in that program, but it is putting the same person's name,city,state; string in for each person. Essentially it's grabbing the first person in the degree table and replicating it over and over again for each person in the degree program.

I know I'm off here somewhere, just not sure where.

 

[RuralGuy]

OK, let's make some adjustments:
'-- The following line is poor practice if you can avoid it.
DoCmd.SetWarnings False
'-- Using CurrentDB.Execute for action queries instead of DoCmd.RunSQL
'-- does not generate the warning messages you are turning off.

'-- The following Dim statements are mostly Variants
'-- Only StrCnt is a String
Dim strSQL, strSQL1, StrCnt As String
'-- And only rs1 is a DAO.RecordSet
Dim rs, rs1 As DAO.Recordset
'-- They should be:
Dim strSQL As String, strSQL1 As String, StrCnt As String
Dim rs As DAO.Recordset, rs1 As DAO.Recordset

Now, don't you already have a query that returns a record for each graduate?
Why not use that query? Include the OrderBy clause so I can see what is going on.

 

[jawilli1]

Through alot of testing and screwing up, it appears as if I got it working correctly. I don't think i was cleaning up my strings and such correctly. Below is my final product. One function creates the output, and the other function takes care of escape characters that may need to be put into peoples names to allow them to be inserted. Thanks for your help! I took your suggestions to clean up my code a bit.

Public Function fCreateGradData()
'---------------------------------------'
'Name: fCreateGradData                  '
'Date: Tuesday, Feb. 28, 2006           '
'Purpose: Create NewsJournal Format     '
'---------------------------------------'

'Declare Variables
Dim strSQL  As String
Dim strSQL1 As String
Dim StrCnt  As String
Dim rs      As DAO.Recordset
Dim rs1     As DAO.Recordset

'Delete Old Information From Table
CurrentDb.Execute "DELETE FROM JAWILLI1_TMP_GRADS;"

'Declare Degree / Major Distinctive String
StrCnt = "Select Distinct PeopleGraduating.Degree, PeopleGraduating.Major1 From PeopleGraduating;"
'Assign it to a recordset called rs1
Set rs1 = CurrentDb.OpenRecordset(StrCnt)

'Run until rs1 is empty - this creates one instance of each degree / major combination
Do Until rs1.EOF
    'Declare People Information String
    strSQL = "SELECT PeopleGraduating.Graduation_Date, PeopleGraduating.FullName, PeopleGraduating.City, PeopleGraduating.Degree, PeopleGraduating.Major1, PeopleGraduating.State FROM PeopleGraduating" & _
    " Where PeopleGraduating.Degree = '" & rs1![Degree] & "' and PeopleGraduating.Major1 = '" & rs1![Major1] & "';"
    'Assign it to a recordset called rs
    Set rs = CurrentDb.OpenRecordset(strSQL)
    'reset strSQL1 to a blank value every time, so it doesn't just keep reappending the new value
    strSQL1 = ""
    'Run until rs is empty - this creates the groupings of Names,City,State; desired by NewsJournal
    Do Until rs.EOF
        'Build Final String
        strSQL1 = strSQL1 & "; " & rs![FullName] & ", " & rs![City] & ", " & rs![State]
        rs.MoveNext
    Loop
    'Close and clean up the rs recordset
    rs.Close
    Set rs = Nothing
    'Insert major, degree, and name string from strSQL1 into temp table
    CurrentDb.Execute "INSERT INTO JAWILLI1_TMP_GRADS (MAJOR,DEGREE,NAMES) VALUES('" & rs1![Major1] & "', '" & rs1![Degree] & "','" & fEscapeOffRegexChars(strSQL1) & "');"
    rs1.MoveNext
Loop
'Clsoe and clean up rs1 recordset
rs1.Close
Set rs1 = Nothing
End Function

Public Function fEscapeOffRegexChars(ByVal strString As String) As String
'------------------------------------------------------------'
'Name: fEscapeOffRegexChars                                  '
'Date: Tuesday, Feb. 28, 2006                                '
'Purpose: Account for needed escape characters in string     '
'------------------------------------------------------------'
' Declare our Regex Array
Dim FindTerm(13)
FindTerm(0) = "*"
FindTerm(1) = "@"
FindTerm(2) = "\"
FindTerm(3) = "("
FindTerm(4) = ")"
FindTerm(5) = "["
FindTerm(6) = "]"
FindTerm(7) = "?"
FindTerm(8) = "<"
FindTerm(9) = ">"
FindTerm(10) = "!"
FindTerm(11) = "{"
FindTerm(12) = "}"
FindTerm(13) = "'"
For i = 0 To 13
    ' To replace all occurrences of one string in another
    Dim intPos As Integer, intLP As Integer, intLen As Integer, strTemp As String
    ' find each search string and replace with target
    intLen = Len(FindTerm(i))
    intPos = InStr(strString, FindTerm(i))
    While intPos <> 0
        strTemp = strTemp & Left$(strString, intPos - 1)
        strTemp = strTemp & "'" & FindTerm(i)
        strString = Right$(strString, Len(strString) - intPos - intLen + 1)
        intPos = InStr(strString, FindTerm(i))
    Wend
Next
' append remainder upon failure of last InStr search
strTemp = strTemp & strString
EscapeOffRegexChars = strTemp
End Function

 

[RuralGuy]

Outstanding!! Glad you got it working on your own. There is always more learned that way. Good job!