arrays comparision

[tksayy]

HI,
I guess this is the correct forum for this question

i have an array 'validcodes'

now i have another array which has two columns from a recordset idno and values


i want to check and see if the values in second array are the values specified in teh first array.
if they are not then i want to print out the id number of that value

thanks

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tksayy]

i have managed to populate the 'validcodes' array with values 3,4,9

now the second array 'vari_able' has two columns and i can see in the watch window that vari_able(0) has the values of fields and vari_able(1) has the id numbers.

i want to write a condition to check whether all the field values i.e vari_able(0) are in confirmance with the first array. if they are not then i need to print the id number and the field value for that defaulting value.

also before this check i want the loop to run only if the value of fields <> 888.

i am confused as to how to check 888 as well as to write the condition of comparing the arrays.

if you want i can post my code

 

[PHV]

if you want i can post my code
It'd be easier to talk about it ...

 

[tksayy]

here goes

i am still trouble shooting so there might be lots of commented codes and mistakes

Option Explicit

Private Sub UpdateNulls()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim tdf As DAO.TableDef
Dim db As Database
Dim varii As Variant, strField As String
Dim strsql As String, strsql2 As String, strsql3 As String
Dim astrFields As Variant 'Stores the array of Field names
Dim intIx As Integer
Dim field As Variant
Dim astrvalidcodes As Variant
Dim i As Integer
Dim validcounter As Long
Dim fieldcounter As Long
Dim vari_able As Variant
Dim fld As DAO.field


Open "C:\Documents and Settings\TAYYAPP\Desktop\testfile.txt" For Input As #1
varii = ""
Do While Not EOF(1)
Line Input #1, strField
varii = varii & "," & strField
Loop
Close #1
astrFields = Split(varii, ",") 'Element 0 empty


'OPEN WORD DOCUMENT FOR WRITING REPORT

Dim objWord As Word.Application
Dim docWord As Word.Document
Dim docExists As Boolean
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
On Error GoTo OpenDoc
docExists = False
Set docWord = objWord.Documents.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT1.doc")
docExists = True
OpenDoc:

On Error GoTo 0
If Not docExists Then
Set docWord = objWord.Documents.Add
End If






For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
strsql = "Select t.* From [" & tdf.Name & "] t Inner Join 01UMWELT On t.fall = [01UMWELT].fall Where [01UMWELT].Status = 4"


Set rs = CurrentDb.OpenRecordset(strsql)


Do While Not rs.EOF
For intIx = 1 To UBound(astrFields)


strsql2 = "SELECT label.validcode FROM variablen s INNER JOIN label ON s.id=label.variablenid WHERE varname='" & astrFields(intIx) & "'"
strsql3 = "SELECT s.[" & astrFields(intIx) & "], s.fall from [" & tdf.Name & "] s Inner Join 01UMWELT on s.fall = [01UMWELT].fall Where [01UMWELT].Status = 4"

Set db = OpenDatabase("C:\Documents and Settings\TAYYAPP\Desktop\GIDAS_Codebook.mdb")
Set rs2 = db.OpenRecordset(strsql2)

With rs2
.MoveLast
.MoveFirst
astrvalidcodes = rs2.GetRows(.RecordCount)
.Close ' assuming you want to do this
End With

Set rs3 = CurrentDb.OpenRecordset(strsql3)


With rs
On Error Resume Next 'Ignore field if table doesn't have it
Call Err.Clear

With rs3
.MoveLast
.MoveFirst
vari_able = rs3.GetRows(.RecordCount)
.Close
End With

If Err.Number <> 3265 Then ' Field not found in table
If vari_able(2) <> 888 Then


'THIS IS WHERE I AM IN DOUBT FOR COMPARISION



'If vari_able <> astrvalidcodes Then

'For validcounter = LBound(astrvalidcodes) To UBound(astrvalidcodes)
'For fieldcounter = LBound(vari_able) To UBound(vari_able)
'If astrvalidcodes(validcounter, 1) <> vari_able(fieldcounter) Then







'Debug.Print rs("fall")

If tdf.Name <> "01umwelt" Then

'docWord.Content.InsertAfter "Variable " & astrFields(intIx) & " " & "in record" & " " & rs("fall") & " contains value 888 which is updated by user"
'docWord.Content.InsertParagraphAfter

'Else

docWord.Content.InsertAfter "Variable " & astrFields(intIx) & " " & "in record" & " " & rs("fall") & " contains invalid value not prescribed in code book "
docWord.Content.InsertParagraphAfter

End If
End If
' Next fieldcounter
' Next validcounter


End If
'End If
'End If

On Error GoTo 0 'End of special handling
End With
Next intIx
rs.MoveNext
Loop
End If
Next


objWord.Visible = True
docWord.Content.InsertParagraphAfter
docWord.SaveAs ("C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT2.doc")
End Sub

 

[PHV]

The GetRows method returns a TWO DIMENSIONAL array.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tksayy]

yes i guessed i might need the idno to write a report of non matching values so i returned both the idno and values field.

 

[Remou]

Here are some explanatory notes.

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Table1")

Debug.Print rs.RecordCount
For Each fld In rs.Fields
    Debug.Print fld.Name; " ";
Next

arRS = rs.GetRows(rs.RecordCount)
d1 = UBound(arRS, 1) 'Fields returned
d2 = UBound(arRS, 2) 'Records returned


For intRecord = 0 To d2
    Debug.Print
    For intField = 0 To d1
        Debug.Print arRS(intField, intRecord);
    Next
Next