Hello, I am usin the access 2003 VBA book. Does anyone know of any db examples using the find methods to do the following below.


I have One table that I read into a DAO record set array to find out how many times did a Part (Example: part number: AC12345) associated with autoID (company ID: 12345=Chevy) fail from the date the selected or all parts associated with a major system was installed (System_Installed_Date).



 PART# AUTOID System_Install_Date Date_service_start Date_service_closed

ac12345 12345 1/1/94 6/28/98 8/16/98

ac12345 12345 1/1/94 5/01/00 6/19/00

ac12345 12345 1/1/94 2/15/03 12/3/03

ac12345 67899 1/1/04 4/12/05 5/3/05


Thank you,

Chris

Hello

Check this link
http://www.geocities.com/raghavendra...l/dao_find.zip

I have done this sing access-xp and dao 3.60. It gives the output as you needed.
It contains a table a module and a macro. Double click macro (or run it)
you will see the total record count msgbox, followed by a inputbox for entering the part id like "ac12345" and another msgbox of the count of records having this
id.

I guess i have understood your problem

hope this helps


With Regards,
Raghavendra Mudugal

Thank you for your help and direction Mr. Raghavendra Mudugal. I was experimenting with the Find methods to retriev and filter records.

I am now working on tieing the example to a combo box on a form and use the datediff function. So, for each Part# in the tblJunction, check to see how many times the part failed. Do this by selecting a record from a form frmPart#Lookup. The record set will compute the number of failures for that part, the Analysis_man_hours & Repair_man_hours and devide the total by number of failures to get MTBF in hours, the record set will also calculate the date difference between date and devide the total sum by the number of failures to get the MTBF and MTBF in hrs.

My stratergy is to do this with an array: The datediff is between the system_install_date for each AUTOID and the date opening. If the AUTOID appears more than once, the current autoID, Part#, Date_service_start , and Date_service_closed is moved to previous and the next record that has a matching AUTOID and PART# moved to previous and the datediff is between the Previous Date_service_closed from and Current Date_service_start. The loop continues until all the records associated with that Part# is analyzed.

If it is Ok with you? I would like to keep you posted on my progress and direction.

I put a copy of my approach regarding what I am trying to do at http://members.cox.net/cesemj/ .

Again thank, you for your direction.

Hello Chris

Okay, that will not be a problem to me at all. you can keep posting your
progress to me.

I saw that link which you have mentioned. I have a fare idea on what you are
trying. But, I did'nt get what difficultied your are facing. This is not clear
to me.

And to your previous post, rather than getting DB work done in VBA, I suggest you
(according to my knowledge) get the most of the data from tables using SQL query
by joining more that one tables (as needed) and get the count at one shot.

Here you will reduce lots of VBA code lines and it will be easy for you to take
care of this application at your best.

Hope this helps.


With Regards,
Raghavendra Mudugal

How to use the datediff function to compare dates for each filtered row in a recordset that may or may matche the same exact criteria in the previous row.

Existing Senerio
************
I use an Access query to filter out each part that I would like to anlyze the failure rate on using where = ac12345 (See Figure 1). When the query is complete I export the data to an excel spreadsheet where I spend lots of hours having fun. For each part I use a formula to calculate the datediff between the system_install_date and the RepairStartDate to get the failure rate value.

My Results are:
1. Part ac12345 failed: 5 times
2. Part ac12345 Railure rate is: 12456 (add up the value returned from the date diff function for each row)
3. Part Mean time between failure is: 4 (Part failure # devided by Failure rate number)
4. Part Repair Hour Total is: 45

Problem
******
1. I will never see day light doing it this way :)
2. How can the recordset below be modified to do this automatically to perform the manual process and send the output to a table instead of the immediate window.

Dificulity
******


1. If the row that is being analyzed does not have one or more matching rows for a specific autoid then the ddatediff is just between the Part_Install_Date and RepairStartDate and the record resumes going through the rest of the record set until EOF.

                                                                                         OR

1.1 If one or more rows have the same autoid (current record) then the next datediff will be between the RepairClose Date from the previous matching row to the next (current) RepairStartDate on the next row. If there are one or more matching rows for a specific autoid the the record resumes going through the rest of the record set.

                                                                                            OR

1.2 If the row that is being analyzed has a PartChangeNumber with a number (1,2,3 or 4 - No letters) and the date then use the NewPartinstallDate
to perform the datediff between the NewPartinstallDate and RepairStartDate. If one or more rows have the same autoid (current record) then the next datediff will be between the RepairClose Date from the previous matching row to the next (current) RepairStartDate on the next row. If there are no more matching rows for a specific autoid the the record resumes going through the rest of the record set.

2. If a row has a blank date field then the datediff is not performed (record is not counted/skiped) and move to the next row because the repair job may not be finished at the time I ran the query or Human Error.


Figure 1
*******
 PART# AUTOID Part_Install_Date Time 1 Time2 RepairStartDate RepairClose Date PartChangeNumber NewPartinstallDate
ac12345 12345 1/1/94 1 6 5/6/98 5/8/98 3 (blank)
ac12345 12345 1/1/94 2 5 7/1/99 7/21/99 3 (blank)
ac12345 12345 1/1/94 3 2 5/3/01 (blank) 3 (blank)
ac12345 99999 5/1/94 5 3 6/2/00 7/1/00 3 (blank)
ac12345 88888 8/1/95 4 2 9/1/05 9/2/05 3 (blank)
ac44444 22222 1/1/98 4 4 3/5/00 3/7/00 (blank) (blank)
ac55555 33333 5/6/99 6 3 6/7/01 6/8/01 4 (blank)
ac55555 44444 5/6/99 5 0 4/1/05 4/3/05 4 (blank)
ac55555 12345 5/6/99 4 2 3/1/03 3/1/03 4 (blank)
ac12345R1 12345 (blank) 2 3 5/1/06 5/2/06 3 1/3/04
ac12345R1 99999 (blank) 1 4 6/1/06 6/2/06 3 3/1/05

Existing:
******
Public Function DaoRst() As Integer
Dim daoDB As Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim t As TableDef
Dim rst2 As DAO.Recordset 'Question I'm thinking I will have to create another recordset that will include all fields and base it off of rst1?

Set daoDB = CurrentDb
Set t = daoDB.TableDefs!tblRepair 'Absolute Table Reference.
Set rst = t.OpenRecordset(dbOpenSnapshot)

s = Forms!Form1!cboPart 'Reads-in part selected from From1.
If Len(Trim(s)) = 0 Then Exit Function

'Filter the Selected record
rst.Filter = "part='" & s & "'"
Set rst1 = rst.OpenRecordset 'New recordset based on selected Part

'Check if the selected Part has failure record(s) listed in tblJunction.
If rst1.RecordCount = 0 Then
    MsgBox "Part Not Found. Please Make Another Selection"
    Else
    If rst1.RecordCount > 0 Then rst1.MoveLast
End If

Thank you again MR. Raghavendra_Mudugal for the sample, I listed the sample you provided mw with above and re-explained my area of difficulty. You may see the same post in this area twice.

Hello Chris,

Check this link, which uses the dynamic recordset.
http://p2p.wrox.com/topic.asp?TOPIC_ID=44633

Okay, here you have a situation of getting the data from the same table
with all calculations on one or more fields (customed).

Here if it was SQL2000, I have guided you to use StoredProcedures to get
the data as you needed. In Access you cannot write SP only queries of
DDL and DML.

So all you have to do is, create a dynamic recordset, create all your
fields as needed, open the tblRepairs table with another recordset
with all proper SQL statement with where conditions, Make sure that
recordset holds the data which needs to be calculated on date diff,
then using DateDiff functions of VBA calculate the date between
two dates, and insert this value into the dyna recordset field.
Loop the main recordset of tblRepairs upto EOF and keep inserting
the data with all calculated values into this dyna recordset.

Finally you can bind this Recordset to the datagrid.

NOTE: above will require the ADO2.0 not DAO.

So, if you feel and want to stick on DAO itsself, then i have
another way to approach this situation.

1.
Just create another table with all fields you needed on output.

2.
open recordset, get the value from the table, use them for your
date calcultions. take all calculted data in respective variables.

3.
Keep inserting them into the table until you reach EOF.

4.
finally, you bind that table to the grid.
(Here you have to design a static table where design is not
changed every now and then, only the data in table will
deleted and new data is inserted every time you call this
function.

NOTE: these are the low level ideas to get the work done.

OR.

Open the recordset like Select A,B,C, datediff(e - f) as 'days' from table
name where ... alll conditions follow.

There are some other ways too, but our limitations is on T-SQL of MS-ACCESS.
So we have to program is such a way that my keeping them in mind.

well, i guess, its not a easy job to understand such process too soon.
needs some time to get the best solution to fit in.

anyway, just think about it and let me.

Hope this helps.


With Regards,
Raghavendra Mudugal