How to populate a calculated field on a continuous form

[patkeaveney]

On a form I call a Function to calculate the number of working days remaining between a due date and now. ( I have a field in the underlying table that I can use to store this value, but dont know how to populate it to keep it up to date).
The value is displayed fine in the single form.
However i have a continuous form that displays the status of the items (eg open, on hold, closed). For the open items i want to show the number of days remaining for each item.
Any ideas on how i can do this please.
Or is there a way to populate the field within the table?

Thanks
Pat

 

[gemma-the-husky]

can you post your function header?

bascially you can add this to the query producing your subform -

just add another column

calculateddate: myfunction(argument1, argument2 etc)

but this will depend on the arguments in your function header - as some/all will need to come from fields in the tables, and others will need to come from elsewhere.

---------
or you can use a variant of your existing function in the same way. The important thing is to clarify how to get into the function

a) the data that IS in the tables
b) the data that isnt.

 

[patkeaveney]

Hi Gemma,

Thanks for your speedy reply,
the first argument needs to be todays date, the second argument is a field in the table).

If the End Date (second argument) is less then Todays Date (first argument). I dont want to call the function I want the value to be zero.

I am struggking with how to code this.

Public Function CountDays(ByVal dteStartDate As Date, dteEndDate As Date) As Integer


Pat

 

[JANR]

Try this function:

Function calcDays(dtEndDate As Date) As Integer
Dim dtStartDate As Date
dtStartDate = Date ' Current Date
    If dtStartDate > dtEndDate Then
        Exit Function
    Else
    calcDays = DateDiff("d", dtStartDate, dtEndDate)
    End If
End Function

JR

 

[patkeaveney]

Hi JANR and Dave(Gemma the Husky)

JANR
I already have the function ...Thanks.

I was looking for a way to use it in a query.

Thanks to you both for your help

I have used the following line in my query (which seems to work):

NumofDaysLeft: IIf(datToBeCompletedByDate>=Date(),CountDays(Date(),datToBeCompletedByDate),0)


Hope this helps others

 

[gemma-the-husky]

you dont need a function for that. simply use this in your query

includethis: [testeddate]>date()

this will test whether the data you are checking is after today

at the bottom in the criteria row, put TRUE or FALSE depending on which you want.

------------
if you want the day count, then just

daycount: [testeddate]-date()


the brackets are important, or access will just do "date" which wont help at all.

 

[patkeaveney]

Thanks Dave. (Gemma)

I need the function as it calculates the number of WORKING days between the dates.

Your input has been very helpful

Pat