A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculated a field in a subform.



 
 
Thread Tools Display Modes
  #1  
Old March 23rd, 2010, 12:01 AM posted to microsoft.public.access.forms
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default Calculated a field in a subform.

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 aWhere 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/For...forms/201003/1

  #2  
Old March 23rd, 2010, 04:56 AM posted to microsoft.public.access.forms
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default Calculated a field in a subform.

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@uwe wrote in message
news:a568dcd1e1aec@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 aWhere 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/For...forms/201003/1



  #3  
Old March 23rd, 2010, 08:31 PM posted to microsoft.public.access.forms
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default Calculated a field in a subform.

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/For...forms/201003/1

  #4  
Old March 23rd, 2010, 09:06 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Calculated a field in a subform.

On Tue, 23 Mar 2010 19:31:55 GMT, "JOSELUIS via AccessMonster.com"
u58123@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]
  #5  
Old March 24th, 2010, 12:12 AM posted to microsoft.public.access.forms
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default Calculated a field in a subform.

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/For...forms/201003/1

  #6  
Old March 24th, 2010, 12:23 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Calculated a field in a subform.

On Tue, 23 Mar 2010 23:12:10 GMT, "JOSELUIS via AccessMonster.com"
u58123@uwe wrote:

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...
--

John W. Vinson [MVP]
  #7  
Old March 24th, 2010, 12:27 AM posted to microsoft.public.access.forms
Al Campagna[_2_]
external usenet poster
 
Posts: 1,462
Default Calculated a field in a subform.

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."


"JOSELUIS via AccessMonster.com" u58123@uwe wrote in message
news:a57587981698c@uwe...
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/For...forms/201003/1



  #8  
Old March 24th, 2010, 12:12 PM posted to microsoft.public.access.forms
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default Calculated a field in a subform.

Time to get your prescription checked, John? g

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

"John W. Vinson" wrote in message
...

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


  #9  
Old March 24th, 2010, 04:46 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Calculated a field in a subform.

On Wed, 24 Mar 2010 07:12:21 -0400, "Douglas J. Steele"
wrote:

Time to get your prescription checked, John? g


blush Thanks for the catch, Douglas.

--

John W. Vinson [MVP]
  #10  
Old March 24th, 2010, 06:28 PM posted to microsoft.public.access.forms
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default Calculated a field in a subform.

John W. Vinson wrote:
Time to get your prescription checked, John? g


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.

--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.