Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 6th, 2006, 09:11 AM
Authorized User
 
Join Date: Jun 2006
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default Working with DAO recordset

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


Reply With Quote
  #2 (permalink)  
Old June 7th, 2006, 01:18 AM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old June 7th, 2006, 08:27 AM
Authorized User
 
Join Date: Jun 2006
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old June 7th, 2006, 11:34 PM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #5 (permalink)  
Old June 9th, 2006, 04:01 PM
Authorized User
 
Join Date: Jun 2006
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #6 (permalink)  
Old June 9th, 2006, 05:12 PM
Authorized User
 
Join Date: Jun 2006
Location: , , .
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


Reply With Quote
  #7 (permalink)  
Old June 9th, 2006, 11:07 PM
Friend of Wrox
Points: 687, Level: 9
Points: 687, Level: 9 Points: 687, Level: 9 Points: 687, Level: 9
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: Chennai, Tamilnadu, India.
Posts: 221
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
DAO Recordset Problems SteveH Access VBA 1 October 5th, 2005 05:39 PM
Convert ADO reocordset to DAO recordset use report redd Access 1 September 24th, 2004 03:34 PM
Working with Excel Worksheet as ADODB.RecordSet Sebastiaan Classic ASP Basics 2 May 25th, 2004 03:10 AM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM



All times are GMT -4. The time now is 10:29 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.