1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access97 assistance

Discussion in 'Business Applications' started by Rcarle, Jul 18, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Rcarle

    Rcarle Thread Starter

    Joined:
    Jul 18, 2006
    Messages:
    3
    I am using Access97. I have one database which has one Table.
    This table has 7 fields one of which is DATE
    The Table contains 33943 records.
    The dates run from 9/25/1995 to the present.
    Is there a way to see if ALL the dates between these times are included?
    Is thare a query that will find missing dates, and can you assist me in constructing such a query?

    Rcarle
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    I can't think of a way to do it with just a query. It is certainly possible to do it with VBA, but it may be a bit slow. If it is for a one off application that might not be a problem for you.
    Can you write a make table or append query to put just the dates in another table and put that in a database and post a zipped copy of it on here?
    Or post a zipped copy of the whole database
     
  3. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Hey there, welcome to TSG.

    First, you'll need to rename that column if you plan on querying the table. Date is a reserved word in both Access and Jet and will cause all sorts of problems in a query.

    You might be able to swing something like that with a self-join, but. I don't know. It's actually kind of doubtful. I also think you'll need to use VBA, but it shouldn't take that long to run, I don't think. You'd query all the dates in the original table, run through the recordset sequentially, and if a given record's date is more than one day greater than the previous record, add dates to a new (temporary?) table. The new table should contain all the dates not in the first.

    chris.
     
  4. Rcarle

    Rcarle Thread Starter

    Joined:
    Jul 18, 2006
    Messages:
    3
    OPB
    Thank you for the extra "service" that you provided to solve my missing dates problem. You were/are most helpful.

    Rcarle
     
  5. Rcarle

    Rcarle Thread Starter

    Joined:
    Jul 18, 2006
    Messages:
    3
    Chris,
    Thank you for your reply. OBP's post arrived first and he was able to solve my missing dates problem. I appreciate your efforts.

    Rcarle
     
  6. coachdan32

    coachdan32

    Joined:
    Nov 13, 2003
    Messages:
    1,021
    RCarle,

    You could create another table and place all dates possible in the time span mentioned in it. Then you could do an unmatched query comparing the two tables and it should return just the dates contained in the All Date table that are not found in your original table.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    CoachDan, one of the problems is populating the Table with 3000+ records, it is easy enough to do with VB. So I just used the same For/Next loop to check the current table and put any missing dates in a missing dates table, with a query and form to view the table.
    The form with the start and finish dates on has a comand button witht the following simple and "BASIC" code.


    On Error GoTo Err_Command3_Click
    Dim recount As Integer, count As Integer, lookdate As Date, foundflag As String, start As Integer
    start = 1

    DoCmd.GoToRecord , , acLast
    recount = Me.CurrentRecord
    For lookdate = Me![Start Date] To Me![Finish Date]
    foundflag = ""
    For count = start To recount
    DoCmd.GoToRecord , , acGoTo, count
    If lookdate < Me.Report_Date Then Exit For
    If Me.Report_Date = lookdate Then
    'MsgBox "Found Date - " & lookdate
    foundflag = "yes"
    End If
    Next count
    If foundflag <> "yes" Then
    DoCmd.OpenForm "Missing Dates", , , , , acHidden
    Forms![Missing Dates]![Missing Dates] = lookdate
    DoCmd.Close acForm, "Missing Dates"
    End If
    start = Me.CurrentRecord - 1
    Next lookdate


    Exit_Command3_Click:
    Exit Sub

    Err_Command3_Click:
    MsgBox Err.Description
    Resume Exit_Command3_Click
     
  8. coachdan32

    coachdan32

    Joined:
    Nov 13, 2003
    Messages:
    1,021
    Sorry, I misread the initial post I thought he said the year was 2005, not 1995. It's still quite a few records, but managable. I would have used Excel's copydown function to generate the list and then import it into Access. It's still doable with 3000 records, but since you've taken the time to write the code- I would definately go that route.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/484262

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice