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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|