Complex Problem

[Neil07979]

Hi all,

I have a fairly complex (In my eyes ) calculation/model that I need to create - and hoping that some one can point me in the best way to get this done.

I have a simple Query in Access pulling across data such as below:

Ref : 123
Start Date : 01/12/07
End Date : 01/01/08
Impact : +0.3

Ref : 123
Start Date : 18/12/07
End Date : 17/01/08
Impact : +0.4

These relate to errors that have occured and the impact these errors have. Ther may be multiple instances of the Ref, but relating to a different time period.

I am trying to create something within Access that will allow me to enter a start date and end date, along with a reference number and give me a result of the maximum impact that occured between those dates.

For instance: Using the above 2 examples:

I enter Start Date: 01/12/07 and End Date : 01/01/08 and Ref : 123

The maximum impact that I would be expecting is +0.7 between the dates of 18/12/07 and 01/01/08.

Can anyone help ?

 

[Calvin]

Due to the complexity of your criteria you cannot use a DLookup method which usually solves most peoples need. Therefore I would have to recommend a custom function.

Start with something like this, it may not compile but should get you started, it's late and my eyes are giving out...

this assumes your data is stored in a table...

Private Function fcnFindMaxImpact (dtStart as Date, dtEnd as Date, lngRef as Long) as Single

fcnFindMaxImpact = 0

dim conn as new adodb.connection
dim rst as new adodb.recordset
dim strSQL as string
strSQL = "Select Sum([Impact]) as Impact From MyTable Where [StartDate] >=#" & dtStart & "# AND [EndDate]<=#" & dtEnd & "# AND [RefNo] =" & lngRef

conn = currentproject.connection
rst.open strSQL, conn, ForwardOnly, Optimistic
if rst.eof and rst.bof then
   'no data found so handle it how you want
   fcnFindMaxImpact = 0

   goto 50
end if

' otherwise data was found and need to read it
rst.movefirst
fcnFindMaxImpact =rst![Impact]

'and now were done 
50:
rst.close
rst = nothing
conn = nothing

End Function

'you can call the above from any sort of action event like a command button or a forms OnLoad event

'button example, create a button on your form named cmdGetImpact
'and also add a text box named Text1 to the form
sub cmdGetImpact_Click

   me.Text1 = fcnFindMaxImpact (#01/12/07#,#01/01/08#, 123)

end sub

I hope you get the drift here and this is sufficient to get you started.