Access97 assistance

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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
 
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.
 

Rcarle

Thread Starter
Joined
Jul 18, 2006
Messages
3
OBP said:
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
OPB
Thank you for the extra "service" that you provided to solve my missing dates problem. You were/are most helpful.

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
 
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.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Members online

Top