Prev: how to set form properties in vba access 2007
Next: Medium Date format and calendar in Access 2007
From: JOSELUIS via AccessMonster.com on 22 Mar 2010 19:01
I have created a subform based on a query with a field TotalDays and this is
a calculated field:
Dim Totaldays As Integer
Dim DateIn As Date
Dim DateOut As Date
If DateOut Is Null Then
TotalDays=DifDate ("d",[DateIn],[Date])
Else
TotalDays=DifDate ("d",[DateIn],[DateOut])
End If
My questions are:Where is the best place to calculated the field in the
subform or in the query?
If the answer is in the subform in what event i have to put the code?
Thank you for your time.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1

From: Al Campagna on 22 Mar 2010 23:56
JOSELUIS,
While this caclulation can be handled by VB, I would think it easier
to do your IIF in a calculated control... TotalDays.
Set the TotalDays Control Source to...

= IIF(IsNull(DateOut), DateDiff("d",[DateIn],[Date]),
DateDiff("d",[DateIn],[DateOut]))

(I'm not aware of a DifDate function in Access, and your
Is Null syntax is incorrect)

If you feel you must use VB, use the AfterUpdate event of BOTH
DateIn amd DateOut.
(since TotalDays is a control on the form, you would not need to Dim it)

Dim DateIn, DateOut As Date
If IsNull(DateOut) Then
TotalDays=DateDiff("d",[DateIn],[Date])
Else
TotalDays=DateDiff("d",[DateIn],[DateOut])
End If
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


"JOSELUIS via AccessMonster.com" <u58123(a)uwe> wrote in message
news:a568dcd1e1aec(a)uwe...
>I have created a subform based on a query with a field TotalDays and this
>is
> a calculated field:
> Dim Totaldays As Integer
> Dim DateIn As Date
> Dim DateOut As Date
> If DateOut Is Null Then
> TotalDays=DifDate ("d",[DateIn],[Date])
> Else
> TotalDays=DifDate ("d",[DateIn],[DateOut])
> End If
> My questions are:Where is the best place to calculated the field in the
> subform or in the query?
> If the answer is in the subform in what event i have to put the code?
> Thank you for your time.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1
>


From: JOSELUIS via AccessMonster.com on 23 Mar 2010 15:31
Oh my God! I´ve tried everything but it doesn´t work.Firstly I put IIF in the
calculated control TotalDays but it didn´t calculate the total number of
days if DateOUT is null . I look for a syntax error but everything seems to
be allright so I tried the VB code but I dont know where I need to put the
code in TotalDays because if i only write code in the AfterUpdate event of
DateIn and DateOut it doesn´t work. Therefore i created a function but I
think I problably made a mistake . My function:
Function TotalDays() As Integer
Dim DateIn As Date
Dim DateOut As Date
If IsNull(DateOut) Then
TotalDays = DateDiff("d", [DateIn], [Date])
Else
TotalDays = DateDiff("d", [DateIn], [DatOut])
End If
End Function


Al Campagna wrote:
>JOSELUIS,
> While this caclulation can be handled by VB, I would think it easier
>to do your IIF in a calculated control... TotalDays.
> Set the TotalDays Control Source to...
>
>= IIF(IsNull(DateOut), DateDiff("d",[DateIn],[Date]),
>DateDiff("d",[DateIn],[DateOut]))
>
>(I'm not aware of a DifDate function in Access, and your
>Is Null syntax is incorrect)
>
> If you feel you must use VB, use the AfterUpdate event of BOTH
>DateIn amd DateOut.
>(since TotalDays is a control on the form, you would not need to Dim it)
>
>Dim DateIn, DateOut As Date
> If IsNull(DateOut) Then
> TotalDays=DateDiff("d",[DateIn],[Date])
> Else
> TotalDays=DateDiff("d",[DateIn],[DateOut])
> End If
>>I have created a subform based on a query with a field TotalDays and this
>>is
>[quoted text clipped - 11 lines]
>> If the answer is in the subform in what event i have to put the code?
>> Thank you for your time.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1

From: John W. Vinson on 23 Mar 2010 16:06
On Tue, 23 Mar 2010 19:31:55 GMT, "JOSELUIS via AccessMonster.com"
<u58123(a)uwe> wrote:

>Oh my God! I�ve tried everything but it doesn�t work.Firstly I put IIF in the
>calculated control TotalDays but it didn�t calculate the total number of
>days if DateOUT is null . I look for a syntax error but everything seems to
>be allright so I tried the VB code but I dont know where I need to put the
>code in TotalDays because if i only write code in the AfterUpdate event of
>DateIn and DateOut it doesn�t work. Therefore i created a function but I
>think I problably made a mistake . My function:
>Function TotalDays() As Integer
>Dim DateIn As Date
>Dim DateOut As Date
>If IsNull(DateOut) Then
>TotalDays = DateDiff("d", [DateIn], [Date])
>Else
>TotalDays = DateDiff("d", [DateIn], [DatOut])
>End If
>End Function

I'm not sure why you're having all the problems, but if there are textboxes
named DateIn and DateOut (not DatOut!!!) on your form, you should be able to
just use a control source of

=DateDiff("d", [DateIn], NZ([DateOut], Date())

This assumes that DateIn will always have a date; if it might be null,

=IIF(IsNull([DateIn], Null, DateDiff("d", [DateIn], NZ([DateOut], Date()))

Note Date() - the function - rather than [Date], which would refer to a field
or control.
--

John W. Vinson [MVP]
From: JOSELUIS via AccessMonster.com on 23 Mar 2010 19:12
OK I refuse to go on I change colon by semicolons, english into spanish NZ to
Nz or NzDate() and nothing ever happens so I´ve decided to stop working on
this I need a holidays maybe I´ll go to St James´way and I hope to get
enough strength to go on with this.
Up to then God Bless you.

John W. Vinson wrote:
>>Oh my God! I´ve tried everything but it doesn´t work.Firstly I put IIF in the
>>calculated control TotalDays but it didn´t calculate the total number of
>[quoted text clipped - 12 lines]
>>End If
>>End Function
>
>I'm not sure why you're having all the problems, but if there are textboxes
>named DateIn and DateOut (not DatOut!!!) on your form, you should be able to
>just use a control source of
>
>=DateDiff("d", [DateIn], NZ([DateOut], Date())
>
>This assumes that DateIn will always have a date; if it might be null,
>
>=IIF(IsNull([DateIn], Null, DateDiff("d", [DateIn], NZ([DateOut], Date()))
>
>Note Date() - the function - rather than [Date], which would refer to a field
>or control.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1

 |  Next  |  Last
Pages: 1 2
Prev: how to set form properties in vba access 2007
Next: Medium Date format and calendar in Access 2007