Results 1 to 5 of 5
  1. #1
    jeffy202 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    3

    Question Populating an Access query using VB module code

    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!



    Click image for larger version. 

Name:	help2.jpg 
Views:	16 
Size:	67.6 KB 
ID:	8416


    Code:
    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
    Attached Thumbnails Attached Thumbnails help2.jpg   help.jpg  
    Attached Files Attached Files
    Last edited by jeffy202; 07-12-2012 at 11:38 AM. Reason: Wrong picture

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,217
    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")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jeffy202 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    3
    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

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,217
    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] & "'")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jeffy202 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    3
    Thank you very much. The first iff statement worked for me

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I use a MODULE in a Cross-Tab Query?
    By timo1999 in forum Modules
    Replies: 2
    Last Post: 12-13-2014, 04:51 PM
  2. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  3. Saving Module Code to Text Files
    By ioMatt in forum Modules
    Replies: 2
    Last Post: 07-02-2011, 08:18 AM
  4. Replies: 4
    Last Post: 05-16-2011, 04:58 PM
  5. Replies: 3
    Last Post: 10-15-2010, 11:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums