Results 1 to 10 of 10
  1. #1
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291

    What is the best way to check for missing data?


    I want to check what records I have that are missing data and what data they are missing. I have several pieces of data in each record that have the option of "Yes" or "No" and if it is Yes then I need to check another field to see if that data is filled in. What is the best way to get this information?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    During data entry, or after the fact? During data entry/edit:

    http://www.baldyweb.com/BeforeUpdate.htm

    After the fact, you could write a query that had a criteria like:

    WHERE YesNoField = Yes AND OtherField Is Null

    to find the offending records.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    after the entry.
    with your syntax of finding offending records, do you have to make a new query for each check?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could certainly do this type of thing:

    WHERE (YesNoField = Yes AND OtherField Is Null) OR (YesNoField2 = Yes AND OtherField2 Is Null)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    Using an OR statement would only tell me which records are missing a piece of the data in one of the conditional statements, not what data is missing as well, am I right?
    Is it possible to do a check for each record in continuous form view or another view? If I could find out how to call a function for each record and not just the first record on the form I could write VBA code to check everything the way I want.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    A textbox with a control source of:

    =FunctionName(IDField)

    would run for every record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    I like this idea, so it will call the function for each record. Now how do I take the IDField and use it?

    This is where I have started but it just does the first record for every record.
    Sub MissingInfo(ID As Integer)
    Dim str As String
    If MHType = "" Then
    str = str + "Type "
    ElseIf [Elevation (45)] = "" Then
    str = str + "Elev "
    End If
    txtMissing= str
    End Sub

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You would have to use the ID that was passed in some way. In a quick and dirty test, I opened a recordset with it to get the related information:

    Code:
    Public Function GetInfo(ID As Long) As String
      Dim strSQL                  As String
      Dim db                      As DAO.Database
      Dim rs                      As DAO.Recordset
    
      Set db = CurrentDb()
    
      strSQL = "SELECT * FROM tblClocks WHERE IDField = " & ID
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
      GetInfo = rs!Operator
    
      Set rs = Nothing
      Set db = Nothing
    End Function
    I don't know how you need to use it to accomplish your goal, but that's one way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    that will work perfectly

  10. #10
    cowboy is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    291
    I got an infinite loop problem after this, I put up a new post:
    https://www.accessforums.net/program...html#post19978

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

Similar Threads

  1. Grouped Report Missing Data
    By travismd in forum Reports
    Replies: 1
    Last Post: 11-30-2009, 11:08 AM
  2. Probably missing something easy
    By z1efuller1 in forum Database Design
    Replies: 5
    Last Post: 11-12-2009, 11:18 AM
  3. Cross check data from 2 sources
    By Zukster in forum Queries
    Replies: 7
    Last Post: 09-22-2009, 10:54 AM
  4. Replies: 0
    Last Post: 08-01-2009, 12:43 PM
  5. 2007 runtime missing
    By wayfarer in forum Access
    Replies: 0
    Last Post: 07-21-2008, 02:26 AM

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