Populating an Access query using VB module code

[jeffy202]

I am trying to write code to populate the 'correct data' column. I know how to check if the data is correct but I cant figure out how to return text for each individual record in the 'correct data' column. I want to have the module run and return 'yes' or 'no' in the correct records but it will only return, I believe, the last answer I would be returning. Is there a way to parse through the column like the .movenext operator in recordsets?



Thanks a bunch!

Function joeshmo() As String

Dim db As Database
Dim Orig As DAO.Recordset
Dim Ltr As DAO.Recordset
Dim checker As Boolean


Set db = CurrentDb()
Set Orig = db.OpenRecordset("select OriginalMult from Small")
Set Ltr = db.OpenRecordset("select LetterCode from Small")



Orig.MoveFirst
With Ltr
While Not Orig.EOF
.MoveFirst
checker = False

While Not .EOF

If Orig("OriginalMult") = Ltr("LetterCode") Then

joeshmo = "yes" 'This is where im trying to do it
checker = True
.MoveLast

Else
.MoveNext
End If
Wend
If checker = False Then
joeshmo = "no" 'this is the option for no
End If



Orig.MoveNext
Wend
End With
Orig.Close
Ltr.Close


End Function

[June7]

If I understand what you want, that won't work and trying the hard way anyhow.

Do an IIf expression in the query to calculate the CorrectData value.
IIf([OriginalMult]=[LetterCode], "Yes", "No")

However, from what I see of the sample data, these two fields will never equal. OriginalMult value can be found within LetterCode, but not equal to.

IIf([LetterCode] LIKE [OriginalMult] & "*", "Yes", "No")

[jeffy202]

I'm not checking to see if the [OrignalMult] and [LetterCode] are the equal in the same row. I want to see if there is an [OrignalMult] that is equal to a [LetterCode] in a different row. I can't do this using IIf(...), unless you know a way to do it

[June7]

Try:

IIf(IsNull(DLookup("LetterCode", "tablename", "LetterCode='" & [OriginalMult] & "'")), "No", "Yes")

Domain aggregate functions can run a bit slow in query but is safer than calling a custom function. The DLookup could be used in the function.

Function CheckCode(strMult) As String
CheckCode = IIf(IsNull(DLookup("LetterCode", "tablename", "LetterCode='" & strMult & "'")), "No", "Yes"))
End Function

Call the function from query with: CheckCode([OriginalMult])

If you want to know a record that matches:
DLookup("ID", "tablename", "LetterCode='" & [OriginalMult] & "'")

Or how many:
DCount("ID", "tablename", "LetterCode='" & [OriginalMult] & "'")

[jeffy202]

Thank you very much. The first iff statement worked for me