Comparing records in Access via VB 
Author Message
 Comparing records in Access via VB

Hi

Let me first say that you guys have been a very big help to my entry to the
world O' VB.  Especially the StrComp() nugget of knowledge (Thanks Michael
Gaven for that one).

Well, I got another.

I'm trying to compare a field from to different tables.  I want to see if a
lender exists in one table by comparing it to the lender field of the other
table.

What currently happens is it goes down row by row (starting with row 1 of
each table) but that won't work.  Some how i need one tables field to search
the other's.  But I don't know how.

I'll add my code to the end of this if it helps.

Thank you,
Jeff McDonald

BTW I'm sure my database design is pretty bad, but I'll have to deal with
that at another time :)

Private Sub cmdOHSeptCompare_Click()
    Dim strstrComp As String
    Dim intMsgBox As Integer
    strstrComp = StrComp(datSept.Recordset.Fields("Lender"),
datLenders.Recordset.Fields("Misspelled"))
    If strstrComp <> 0 Then
        intMsgBox = MsgBox(datSept.Recordset.Fields("Lender") & " is not the
same as " & _
        datLenders.Recordset.Fields("Misspelled") & vbCrLf & " Do you want
to change your record?", vbYesNoCancel + vbDefaultButton2, "Change")
            If vbNo Then datSept.Recordset.MoveNext
            datLenders.Recordset.MoveNext
    End If

End Sub



Sat, 15 Mar 2003 03:00:00 GMT
 Comparing records in Access via VB

Well lets say you want to know all the lender's that are in table A and in
table B. The following SQL will return that record set.

SELECT Lender FROM A INNER JOIN B ON A.Lender = B.Lender

If you would like a recordset that returns all the Lenders from table A that
are missing from table B. Use this SQL.

SELECT Lender FROM A LEFT JOIN B ON A.Lender = B.Lender WHERE B.Lender IS
NULL

I may not under stand your question, so please feel contact me directly.
Remove the JunkMail. from the email address.

Jim


Quote:
> Hi

> Let me first say that you guys have been a very big help to my entry to
the
> world O' VB.  Especially the StrComp() nugget of knowledge (Thanks Michael
> Gaven for that one).

> Well, I got another.

> I'm trying to compare a field from to different tables.  I want to see if
a
> lender exists in one table by comparing it to the lender field of the
other
> table.

> What currently happens is it goes down row by row (starting with row 1 of
> each table) but that won't work.  Some how i need one tables field to
search
> the other's.  But I don't know how.

> I'll add my code to the end of this if it helps.

> Thank you,
> Jeff McDonald

> BTW I'm sure my database design is pretty bad, but I'll have to deal with
> that at another time :)

> Private Sub cmdOHSeptCompare_Click()
>     Dim strstrComp As String
>     Dim intMsgBox As Integer
>     strstrComp = StrComp(datSept.Recordset.Fields("Lender"),
> datLenders.Recordset.Fields("Misspelled"))
>     If strstrComp <> 0 Then
>         intMsgBox = MsgBox(datSept.Recordset.Fields("Lender") & " is not
the
> same as " & _
>         datLenders.Recordset.Fields("Misspelled") & vbCrLf & " Do you want
> to change your record?", vbYesNoCancel + vbDefaultButton2, "Change")
>             If vbNo Then datSept.Recordset.MoveNext
>             datLenders.Recordset.MoveNext
>     End If

> End Sub



Sat, 15 Mar 2003 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Comparing 2 tables of records in Access using VB

2. Updating Access Records via VB

3. VB Script error when Inserting records via ADO w/Access Database

4. Accessing encrypted data in Access 2000 DB via VB

5. Accessing Access report via VB

6. How does Access development compare to VB development?

7. How does Access development compare to VB development?

8. A date comparing problem in VB/Access 2.0

9. Indexes / Speed of VB compared to Access

10. SQL: Updating records via VB 3.0 (Part II)

11. Creating new records in FoxPro via VB and ADO

12. SQL: Updating records via VB 3.0


 
Powered by phpBB® Forum Software