Showing Time

[Spikemannen]

Hi!

I have five tables:

tblCompany
CompanyID
CompanyName

tblSubject
SubjectID
Subject

tblHandler
HandlerId
Handler

tblArrend
ArrendID
Company
Arrived
Subject
Arrend
Handler
Prio
Description
Status
Finished
Time

tblInvoice
InvoiceID
Company
InvoiceDate
Until
Time

For the function I'm after I will work in tblInvoice.
I choose a invoice date (presumably today), then I choose a date in the tblInvoice.Until, when I leave that field I want a questiothat calculates all tblArrend.Time for every arrend that has a finished date before tblInvoice.Until for the active post but after the last posts tblInvoice.Until value. AND the time will only be calculated for thoose arrends that has the same Company value as tblInvoice.Company.

How to do this? Please, help me...

Best Regardz,

Spikemannen

 

[redapples]

I've read this 5 times and I'm still not convinced I know what your after.

Do you mean that you want all of the records from tblArrend that occur between two dates taken from seperate records within the tblInvoice table?

Private Function SumOfTime(dDate1 as Date,dDate2 as Date) as integer
dim c as ADODB.Connection
dim rs as New ADODB.Recordset
dim strSQL as string
set c = CurrentProject.Connection

strSQL = "SELECT Sum(tblArrend.Time) AS SumOfTime " & _
"FROM tblArrend " & _
WHERE (((tblArrend.Finished) Between #" dDate1 &" # And #" dDate2" #));

rs.Open strSQL,3,1
SumOfTime = rs!SumOfTime
rs.close

you would then need someway (there are numerous) to identify the dates for the function.

Call SumOfTime (dStart,dEnd)

I hope this is what you're after.

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[Spikemannen]

Hi!

Thanks for your reply...

The thing is that I want the sum of tblArrend.Time for all the arrends that has a finished date that is between previous tblInvoice.InvoiceDate and tblInvoice.Until in the active post but only for the company that I have selected in tblInvoice.Company.


Best Regardz,

Spikemannen

 

[redapples]

Private Function SumOfTime(dDate1 as Date,dDate2 as Date,iCompany as Integer) as integer
dim c as ADODB.Connection
dim rs as New ADODB.Recordset
dim strSQL as string
set c = CurrentProject.Connection

strSQL = "SELECT Sum(tblArrend.Time) AS SumOfTime " & _
"FROM tblArrend " & _
WHERE (((tblArrend.Finished) Between #" dDate1 &" # And #" dDate2" #)AND ((tblArrend)= " & iCompany & "));

rs.Open strSQL,3,1
SumOfTime = rs!SumOfTime
rs.close
End Function

should do it

this time call it this way

Call SumOfTime (dStart,dEnd,iSelectedCompany)

How you get the variables dStart,dEnd and sSelectedCompany is up to you. I assume your Form stores the CompanyID of the selected company, I also assume this is an autonumber integer. You must have some way in the Form to store the InvoiceDate and Until fields of the table I suppose but the code here will work assuming you can provide those as the start and end dates. Have you tried this?

Last point this is hard coding SQL which has draw backs in some circumstances. If you are working over SQL Server DB backend then I would suggest using a SP to run this passing the variables to that.

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk