Prev: crosstab query problem
Next: Lebans Report to PDF query
From: Stinky Pete on 28 Jul 2010 22:08
Hi,

I tried ages to ago to wrap my head around this process, but I still
need help and education - I found a workaround at the time but it's
come up again. What I want to do is call a function that calcuates
the number of working days between two dates. I've found a function
that makes sense to me, but I'm coming unstuck when calling it from
the form. The code seems to compile OK, but I'm not getting any data
populated in the correct field.

What I need is the [days open] field to show the number of working
days betwen the [Date Initiated] and [Date Complete] when the [Date
Complete] field is populated. Otherwise the [days open] field remains
blank. The intention was to action this "afterupdate".

I've called the function via ...

Call weekdays(startDate, endDate)
startDate = [Date Initiated]
endDate = [Date Complete]

and the first bit of the function as ...

Public Function weekdays(ByRef startDate As Date, _
ByRef endDate As Date _
) As Integer

If anyone needs the rest of the code, I can copy and past it in if
anyone would like it.

Thanx in advance,

Stinky Pete
From: Salad on 28 Jul 2010 22:24
Stinky Pete wrote:

> Hi,
>
> I tried ages to ago to wrap my head around this process, but I still
> need help and education - I found a workaround at the time but it's
> come up again. What I want to do is call a function that calcuates
> the number of working days between two dates. I've found a function
> that makes sense to me, but I'm coming unstuck when calling it from
> the form. The code seems to compile OK, but I'm not getting any data
> populated in the correct field.
>
> What I need is the [days open] field to show the number of working
> days betwen the [Date Initiated] and [Date Complete] when the [Date
> Complete] field is populated. Otherwise the [days open] field remains
> blank. The intention was to action this "afterupdate".
>
> I've called the function via ...
>
> Call weekdays(startDate, endDate)
> startDate = [Date Initiated]
> endDate = [Date Complete]
>
> and the first bit of the function as ...
>
> Public Function weekdays(ByRef startDate As Date, _
> ByRef endDate As Date _
> ) As Integer
>
> If anyone needs the rest of the code, I can copy and past it in if
> anyone would like it.
>
> Thanx in advance,
>
> Stinky Pete

Wouldn't it work better like
startDate = [Date Initiated]
endDate = [Date Complete]
Call weekdays(startDate, endDate)

When you call the Weekdays() function, it might be start and end are null.

You could even do
Call weekdays([Date Initiated], [Date Complete])

But are you returning anything by using Call that can be assigned to a
control? Something like
Me.DaysWorked = weekdays([Date Initiated], [Date Complete])
would return and assign the value from WeekDays() to the control DaysWorked.


From: Phil on 29 Jul 2010 04:38
On 29/07/2010 03:24:11, Salad wrote:
> Stinky Pete wrote:
>
>> Hi,
>>
>> I tried ages to ago to wrap my head around this process, but I still
>> need help and education - I found a workaround at the time but it's
>> come up again. What I want to do is call a function that calcuates
>> the number of working days between two dates. I've found a function
>> that makes sense to me, but I'm coming unstuck when calling it from
>> the form. The code seems to compile OK, but I'm not getting any data
>> populated in the correct field.
>>
>> What I need is the [days open] field to show the number of working
>> days betwen the [Date Initiated] and [Date Complete] when the [Date
>> Complete] field is populated. Otherwise the [days open] field remains
>> blank. The intention was to action this "afterupdate".
>>
>> I've called the function via ...
>>
>> Call weekdays(startDate, endDate)
>> startDate = [Date Initiated]
>> endDate = [Date Complete]
>>
>> and the first bit of the function as ...
>>
>> Public Function weekdays(ByRef startDate As Date, _
>> ByRef endDate As Date _
>> ) As Integer
>>
>> If anyone needs the rest of the code, I can copy and past it in if
>> anyone would like it.
>>
>> Thanx in advance,
>>
>> Stinky Pete
>
> Wouldn't it work better like
> startDate = [Date Initiated]
> endDate = [Date Complete]
> Call weekdays(startDate, endDate)
>
> When you call the Weekdays() function, it might be start and end are null.
>
> You could even do
> Call weekdays([Date Initiated], [Date Complete])
>
> But are you returning anything by using Call that can be assigned to a
> control? Something like
> Me.DaysWorked = weekdays([Date Initiated], [Date Complete])
> would return and assign the value from WeekDays() to the control
> DaysWorked.
>
>
>

I guess that on the AfterUpdate of both the [Date Initiated] and the[Date
Complete] fields as well as on the OnCurrent of the form you need

If not IsNull(Date Initiated] and not IsNull([Date Complete]) then
Me.DaysWorked orked = weekdays([Date Initiated], [Date Complete]) ' Me is not
stricly neccessary as it is a field on the form end If

Incidently, Life is much more simple if you don't have spaces in field names.
You don't need those square brackets I would use DateInitiated an
DateComplete - Just as readable in my opinion

Phil
From: Stinky Pete on 29 Jul 2010 21:19
On Jul 29, 6:38 pm, "Phil" <p...(a)stantonfamily.co.uk> wrote:
> On 29/07/2010 03:24:11, Salad wrote:
>
>
>
> > Stinky Pete wrote:
>
> >> Hi,
>
> >> I tried ages to ago to wrap my head around this process, but I still
> >> need help and education - I found a workaround at the time but it's
> >> come up again.  What I want to do is call afunctionthat calcuates
> >> the number of working days between two dates.  I've found afunction
> >> that makes sense to me, but I'm coming unstuck when calling it from
> >> the form. The code seems to compile OK, but I'm not getting any data
> >> populated in the correct field.
>
> >> What I need is the [days open] field to show the number of working
> >> days betwen the [Date Initiated] and [Date Complete] when the [Date
> >> Complete] field is populated.  Otherwise the [days open] field remains
> >> blank. The intention was to action this "afterupdate".
>
> >> I've called thefunctionvia ...
>
> >> Call weekdays(startDate, endDate)
> >> startDate = [Date Initiated]
> >> endDate = [Date Complete]
>
> >> and the first bit of thefunctionas ...
>
> >> PublicFunctionweekdays(ByRef startDate As Date, _
> >>     ByRef endDate As Date _
> >>     ) As Integer
>
> >> If anyone needs the rest of the code, I can copy and past it in if
> >> anyone would like it.
>
> >> Thanx in advance,
>
> >> Stinky Pete
>
> > Wouldn't it work better like
> > startDate = [Date Initiated]
> > endDate = [Date Complete]
> > Call weekdays(startDate, endDate)
>
> > When you call the Weekdays()function, it might be start and end are null.
>
> > You could even do
> > Call weekdays([Date Initiated], [Date Complete])
>
> > But are you returning anything by using Call that can be assigned to a
> > control?  Something like
> > Me.DaysWorked = weekdays([Date Initiated], [Date Complete])
> > would return and assign the value from WeekDays() to the control
> > DaysWorked.
>
> I guess that on the AfterUpdate of both the [Date Initiated] and the[Date
> Complete] fields as well as on the OnCurrent of the form you need
>
> If not IsNull(Date Initiated] and not IsNull([Date Complete]) then
> Me.DaysWorked orked = weekdays([Date Initiated], [Date Complete]) ' Me is not
> stricly neccessary as it is a field on the form end If
>
> Incidently, Life is much more simple if you don't have spaces in field names.
> You don't need those square brackets I would use DateInitiated an
> DateComplete - Just as readable in my opinion
>
> Phil

Woo hoo. It was a combination of errors, mostly as identified that
the date completed field was null. Whack in the "if not isnull" combo
and voila, life's good again. Well for now, I've still got a heap of
Q's about correctly calling a function, but will deal with them as
required.

Thanx once again.

Stinky Pete
 | 
Pages: 1
Prev: crosstab query problem
Next: Lebans Report to PDF query