[Access] Compare fields in 2 tables to find

03-23-2010, 01:27 PM

InFy

Freshman

Join Date: Mar 2004

Posts: 30

[Access] Compare fields in 2 tables to find

Edit: title doesn't make sense because I didn't finish it before posting.
Compare fields in 2 tables to see if values are equal

Following situation:

I have 2 tables:
1 containing names and a date when they had signed the current procedures on site
1 with people who are 'in' or 'out'

Currently, when a person visits the site twice, there will be 2 records of him visiting. On the other hand, there's only 1 record of each person in the procedures table with the date field, which gets updated accordingly.

Right now there's someone entering names in the visitor table, this needs to look for the same name in the procedure table, if a name is found it should look at the date when the procedure was signed. If it's more than 6 months ago, the visitor needs to read and sign them again.

I'm stuck with the comparison of the table fields.
I can figure out the date check and popup myself if not included in code samples.

Thanks in advance!

03-30-2010, 12:15 PM

InFy

Freshman

Join Date: Mar 2004

Posts: 30

I have created a selection query that will only show the date matched to the names.

Then in the code I use (shortened)

Code:

Dim dtDate As Date
Dim qry As String

qry = "Name of the selection query" 'This has to be the name you gave it, including the spaces, query between ""

    If Not IsNull(DLookup("Datum", qry)) Then
        dtDate = DLookup("Datum", qry)
    End If

Then you can output the dtDate via various ways (msgbox, textbox, ...) or use it to compare.

I used it to check if the dtDate is older than 6 months or not, then show a popup.

Code:

    If dtDate <> 0 Then
        intDateDiff = DateDiff("d", dtDate, Date)

        If (intDateDiff > 183) Then
            popup = MsgBox("The date is older than 183 days.")
        End If
    End If

I'll be checking this thread for the next week, so if any question, now's the time