Calculated a field in a subform.

[JOSELUIS via AccessMonster.com]

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.

 

[Al Campagna]

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]

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

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.

 

[John W. Vinson]

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.

 

[JOSELUIS via AccessMonster.com]

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.

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.

 

[John W. Vinson]

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.

Buen suerte y ten un buen dia. Ojala que San Tiago tendra merced...

 

[Al Campagna]

JOSELUIS,
Cut & Paste exactly what you have in your calculation at this time.
We always need to see your code if you still have a problem.
Both John's and my suggestion should work, but John's is a more elegant
solution... in that it substitutes Date() for DateOut, if DateOut is null.
Why don't we stick with John's code for now...
--
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."

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.

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.

 

[Douglas J. Steele]

Time to get your prescription checked, John? <g>

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

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

You need an extra closing parenthesis:

=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()))

You're missing a closing parenthesis for the IsNull function call (in
addition to the closing parenthesis for the DateDiff function call)

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

 

[John W. Vinson]

Time to get your prescription checked, John? <g>

<blush> Thanks for the catch, Douglas.

 

[JOSELUIS via AccessMonster.com]

<blush> Thanks for the catch, Douglas.

Thank you very much now it works perfectly. This Easter holidays I pray for
all of you in my way to Santiago.