Hi

I am trying to compare two tables,an input table and a master table. I am trying to compare the variables vAccNumber1,vAccNumber2,vAccNumber3 and vAccNumber4 appearing in the input table (tbl1) with vAccNumber1b,vAccNumber2b, vAccNumber3b, vAccNumber4b appearing in the master table (tbl2). If there is a match I call another function. However I also need another condition that the variables vAccNumber1,vAccNumber2,vAccNumber3 and vAccNumber4 are more than zero. I don't want the function called if a match of two zeros is found. I have tried to put if statemnts within the select cases but access doesn't like this. Anyone any ideas?

Here's the code:

Do Until tbl1.EOF
    vAccNumber1 = tbl1![AccountNumber1]
    vAccNumber2 = tbl1![AccountNumber2]
    vAccNumber3 = tbl1![AccountNumber3]
    vAccNumber4 = tbl1![AccountNumber4]


        Do Until tbl2.EOF
            vAccNumber1b = tbl2![AccountNumber1]
            vAccNumber2b = tbl2![AccountNumber2]
            vAccNumber3b = tbl2![AccountNumber3]
            vAccNumber4b = tbl2![AccountNumber4]

            Select Case vAccNumber1

                Case vAccNumber1b
                    Call bob
                    Exit Sub
                Case vAccNumber2b
                    Call bob
                    Exit Sub
                Case vAccNumber3b
                    Call bob
                    Exit Sub
                Case vAccNumber4b
                    Call bob
                    Exit Sub
                Case Else


            Select Case vAccNumber2

                Case vAccNumber1b
                    Call bob
                    Exit Sub
                Case vAccNumber2b
                    Call bob
                    Exit Sub
                Case vAccNumber3b
                    Call bob
                    Exit Sub
                Case vAccNumber4b
                    Call bob
                    Exit Sub
                Case Else


            Select Case vAccNumber3

                Case vAccNumber1b
                    Call bob
                    Exit Sub
                Case vAccNumber2b
                    Call bob
                    Exit Sub
                Case vAccNumber3b
                    Call bob
                    Exit Sub
                Case vAccNumber4b
                    Call bob
                    Exit Sub
                Case Else


            Select Case vAccNumber4

                Case vAccNumber1b
                    Call bob
                    Exit Sub
                Case vAccNumber2b
                    Call bob
                    Exit Sub
                Case vAccNumber3b
                    Call bob
                    Exit Sub
                Case vAccNumber4b
                    Call bob
                    Exit Sub
                Case Else

                    tbl2.MoveNext
            End Select
            End Select
            End Select
            End Select
        Loop
        tbl1.MoveNext
        tbl2.MoveFirst
Loop



Cheers
Tony

__________________
Cheers
Tony

I would need more information, but you can do this.

Why is there a table with fields named [AccountNumber1], [AccountNumber2], [AccountNumber3], and [AccountNumber4]?

This is not normalized data. Is this a strictly OLAP database, or are you managing transactions as well (OLTP)? If there is any transaction processing going on, then this structure needs to be changed.

You shouldn't have this sort of record:

CustomerID
FirstName: John
LastName: Doe
AccountNumber1: XXXXXXXXXX
AccountNumber2: ""
AccountNumber3: ""
AccountNumber4: ""

   You should always build for n accounts. So Accounts should be in a seperate table. This is why you have to loop through everything to compare account numbers.

It should look like this:

tblCustomer
CustomerID = PK
FirstName
LastName

tblAccounts
AccountID = PK
CustomerID = FK
Account Number

   Then each customer or whomever can have one account, or a thousand accounts.

Anywho, if you explain the structure you do have, I could help write the code to do this.



mmcdonal