Find out the total days taken for each record.

[aman]

Hi guys

I have designed a system with excel as a frontend and Access as a backend. The main table stores information about each case like recdate,Procdate, Product,daystaken etc. Now I want to design a form from where the user can select the startdate and enddate from the dropdownbox and when the Enter button is clicked then it should store the "daystaken" fro each record whose recdate>=startdate and <=Enddate.

e.g id recdate=09/10/2011 and Procdate=11/10/2011 then daystaken field will store 2(so it is same like finiding the networkdays in excel).

I hope anyone can help me to figure it out.

Thanks

 

[CBrighton]

Try this:

DateDiff("d",[RecDate],[ProcDate])

 

[aman]

I know we can use this function but how to write a loop that will fine date difference for each record.

Thanks a lot.

 

[CBrighton]

An update query with this as the "update to" (which may not actually be what the row's called, but it's something like that) should do it.

If you want to check beforehand then create a select query with the appropriate criteria (copied from OP: "recdate>=startdate and <=Enddate") and add the datediff as a new field. You will see that the difference is calculated for each record.

 

[aman]

Thanks but I want to write code in excel that will update records in access table as my frontend is Excel and backend is Access.

Hope you understand what I mean.

Thanks

 

[CBrighton]

I've not used excel as a front end, however if I were doing it in Access VBA I would use a recordset which has the date criteria and loop through each record.

I can go into detail on how to do that in Access VBA, but you will need to find someone with more "Access automation via Excel" knowledge to get any extra bits such as connecting to the database, etc (though I expect you have things like the connections already sorted).

In access I would do something like this:

Dim db as DAO.Database
Dim rst as DAO.Recordset
Set rst = db.OpenRecordset("SELECT * FROM [B]TableName[/B] WHERE RecDate between #" & [StartDate] & "# and #" & [EndDate] & "#")
 
If rst.EOFthen
   msgbox "No records found, procedure cancelled"
   Exit sub
End if
 
rst.movefirst
 
Do Until rst.EOF
   rst.edit
   rst!DaysTaken = DateDiff("d",rst!RecDate,rst!ProcDate)
   rst.update
   rst.movenext
Loop
 
set rst = nothing
rst.close

:edit:

You should add some error checking, checking for nulls in the date fields before using them in the DateDiff function, etc.