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
|
|||
|
|||
Public Function Problems
I have the following function:
Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
#2
|
|||
|
|||
Public Function Problems
Hello Judy
Queries have different rules then forms do. In a form, you have to use object oriented statements to get a field's value. When you call the function, try using this statement: returnvalue = CalcWeeks(DateStart.Value,DateEnd.Value) "JudyB" wrote: I have the following function: Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
#3
|
|||
|
|||
Public Function Problems
The value property is not required.
There are a couple of issues that do need addressing. First, the return type of the function is Single. That is not necessary as a Variant of Long Intger will be returned from the DateDiff function. The function should return the data type of the field bound to the control. Additionally, the objects referenced in the control source expression should be the names of controls on the form, not fields in the form's recordset. Barring all that, have you done a trace through the code to be sure it is executing and what it is doing? -- Dave Hargis, Microsoft Access MVP "JString" wrote: Hello Judy Queries have different rules then forms do. In a form, you have to use object oriented statements to get a field's value. When you call the function, try using this statement: returnvalue = CalcWeeks(DateStart.Value,DateEnd.Value) "JudyB" wrote: I have the following function: Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
#4
|
|||
|
|||
Public Function Problems
Hi Dave,
Thanks for the help. I removed the return type of the function "is Single" as suggested. I also opened the Immediate Window of VBA and typed ?CalcWeeks(#1/1/2008#,#12/31/2008#) and it returned the value of 52. The query still works, but I still cannot get the calculated control in the subform to work. If I were using the incorrect names of controls within the subform, wouldn't I get an error when I type in the DateDiff("ww", DateStart, Nz(DateEnd, Date()) expression? This is my first database, so I am not quick to understand. Any other suggestions as to what my problem might be? Thanks in advance. -- JudyB "Klatuu" wrote: The value property is not required. There are a couple of issues that do need addressing. First, the return type of the function is Single. That is not necessary as a Variant of Long Intger will be returned from the DateDiff function. The function should return the data type of the field bound to the control. Additionally, the objects referenced in the control source expression should be the names of controls on the form, not fields in the form's recordset. Barring all that, have you done a trace through the code to be sure it is executing and what it is doing? -- Dave Hargis, Microsoft Access MVP "JString" wrote: Hello Judy Queries have different rules then forms do. In a form, you have to use object oriented statements to get a field's value. When you call the function, try using this statement: returnvalue = CalcWeeks(DateStart.Value,DateEnd.Value) "JudyB" wrote: I have the following function: Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
#5
|
|||
|
|||
Public Function Problems
The expression should be in the Control Source property text box in the
properties dialog for the control. It should start with an =, not a ? =CalcWeeks(#1/1/2008#,#12/31/2008#) If you did not use the correct control names, the control would probably contain #Error -- Dave Hargis, Microsoft Access MVP "JudyB" wrote: Hi Dave, Thanks for the help. I removed the return type of the function "is Single" as suggested. I also opened the Immediate Window of VBA and typed ?CalcWeeks(#1/1/2008#,#12/31/2008#) and it returned the value of 52. The query still works, but I still cannot get the calculated control in the subform to work. If I were using the incorrect names of controls within the subform, wouldn't I get an error when I type in the DateDiff("ww", DateStart, Nz(DateEnd, Date()) expression? This is my first database, so I am not quick to understand. Any other suggestions as to what my problem might be? Thanks in advance. -- JudyB "Klatuu" wrote: The value property is not required. There are a couple of issues that do need addressing. First, the return type of the function is Single. That is not necessary as a Variant of Long Intger will be returned from the DateDiff function. The function should return the data type of the field bound to the control. Additionally, the objects referenced in the control source expression should be the names of controls on the form, not fields in the form's recordset. Barring all that, have you done a trace through the code to be sure it is executing and what it is doing? -- Dave Hargis, Microsoft Access MVP "JString" wrote: Hello Judy Queries have different rules then forms do. In a form, you have to use object oriented statements to get a field's value. When you call the function, try using this statement: returnvalue = CalcWeeks(DateStart.Value,DateEnd.Value) "JudyB" wrote: I have the following function: Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
#6
|
|||
|
|||
Public Function Problems
Hi Dave,
I used the expression =CalcWeeks([DateStart],[DateEnd]) in the Control Source property text box. Returns a "0" value. I used the ?CalcWeeks(#1/1/2008#,#12/31/2008#) code in the Immediate Window of VBA just to test the function to see if it worked correctly. It did. Still very confused. Any other suggestions. Am I supplying enough information? Thanks! -- JudyB "Klatuu" wrote: The expression should be in the Control Source property text box in the properties dialog for the control. It should start with an =, not a ? =CalcWeeks(#1/1/2008#,#12/31/2008#) If you did not use the correct control names, the control would probably contain #Error -- Dave Hargis, Microsoft Access MVP "JudyB" wrote: Hi Dave, Thanks for the help. I removed the return type of the function "is Single" as suggested. I also opened the Immediate Window of VBA and typed ?CalcWeeks(#1/1/2008#,#12/31/2008#) and it returned the value of 52. The query still works, but I still cannot get the calculated control in the subform to work. If I were using the incorrect names of controls within the subform, wouldn't I get an error when I type in the DateDiff("ww", DateStart, Nz(DateEnd, Date()) expression? This is my first database, so I am not quick to understand. Any other suggestions as to what my problem might be? Thanks in advance. -- JudyB "Klatuu" wrote: The value property is not required. There are a couple of issues that do need addressing. First, the return type of the function is Single. That is not necessary as a Variant of Long Intger will be returned from the DateDiff function. The function should return the data type of the field bound to the control. Additionally, the objects referenced in the control source expression should be the names of controls on the form, not fields in the form's recordset. Barring all that, have you done a trace through the code to be sure it is executing and what it is doing? -- Dave Hargis, Microsoft Access MVP "JString" wrote: Hello Judy Queries have different rules then forms do. In a form, you have to use object oriented statements to get a field's value. When you call the function, try using this statement: returnvalue = CalcWeeks(DateStart.Value,DateEnd.Value) "JudyB" wrote: I have the following function: Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
#7
|
|||
|
|||
Public Function Problems
What start and end dates are you using that returns 0?
-- Dave Hargis, Microsoft Access MVP "JudyB" wrote: Hi Dave, I used the expression =CalcWeeks([DateStart],[DateEnd]) in the Control Source property text box. Returns a "0" value. I used the ?CalcWeeks(#1/1/2008#,#12/31/2008#) code in the Immediate Window of VBA just to test the function to see if it worked correctly. It did. Still very confused. Any other suggestions. Am I supplying enough information? Thanks! -- JudyB "Klatuu" wrote: The expression should be in the Control Source property text box in the properties dialog for the control. It should start with an =, not a ? =CalcWeeks(#1/1/2008#,#12/31/2008#) If you did not use the correct control names, the control would probably contain #Error -- Dave Hargis, Microsoft Access MVP "JudyB" wrote: Hi Dave, Thanks for the help. I removed the return type of the function "is Single" as suggested. I also opened the Immediate Window of VBA and typed ?CalcWeeks(#1/1/2008#,#12/31/2008#) and it returned the value of 52. The query still works, but I still cannot get the calculated control in the subform to work. If I were using the incorrect names of controls within the subform, wouldn't I get an error when I type in the DateDiff("ww", DateStart, Nz(DateEnd, Date()) expression? This is my first database, so I am not quick to understand. Any other suggestions as to what my problem might be? Thanks in advance. -- JudyB "Klatuu" wrote: The value property is not required. There are a couple of issues that do need addressing. First, the return type of the function is Single. That is not necessary as a Variant of Long Intger will be returned from the DateDiff function. The function should return the data type of the field bound to the control. Additionally, the objects referenced in the control source expression should be the names of controls on the form, not fields in the form's recordset. Barring all that, have you done a trace through the code to be sure it is executing and what it is doing? -- Dave Hargis, Microsoft Access MVP "JString" wrote: Hello Judy Queries have different rules then forms do. In a form, you have to use object oriented statements to get a field's value. When you call the function, try using this statement: returnvalue = CalcWeeks(DateStart.Value,DateEnd.Value) "JudyB" wrote: I have the following function: Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
#8
|
|||
|
|||
Public Function Problems
Hi Dave,
I am using the following: Date Start Date End Service Time 7/9/1984 7/9/1985 0 Format for both dates is setup as short dates. I hope this is the type information you were requesting. -- JudyB "Klatuu" wrote: What start and end dates are you using that returns 0? -- Dave Hargis, Microsoft Access MVP "JudyB" wrote: Hi Dave, I used the expression =CalcWeeks([DateStart],[DateEnd]) in the Control Source property text box. Returns a "0" value. I used the ?CalcWeeks(#1/1/2008#,#12/31/2008#) code in the Immediate Window of VBA just to test the function to see if it worked correctly. It did. Still very confused. Any other suggestions. Am I supplying enough information? Thanks! -- JudyB "Klatuu" wrote: The expression should be in the Control Source property text box in the properties dialog for the control. It should start with an =, not a ? =CalcWeeks(#1/1/2008#,#12/31/2008#) If you did not use the correct control names, the control would probably contain #Error -- Dave Hargis, Microsoft Access MVP "JudyB" wrote: Hi Dave, Thanks for the help. I removed the return type of the function "is Single" as suggested. I also opened the Immediate Window of VBA and typed ?CalcWeeks(#1/1/2008#,#12/31/2008#) and it returned the value of 52. The query still works, but I still cannot get the calculated control in the subform to work. If I were using the incorrect names of controls within the subform, wouldn't I get an error when I type in the DateDiff("ww", DateStart, Nz(DateEnd, Date()) expression? This is my first database, so I am not quick to understand. Any other suggestions as to what my problem might be? Thanks in advance. -- JudyB "Klatuu" wrote: The value property is not required. There are a couple of issues that do need addressing. First, the return type of the function is Single. That is not necessary as a Variant of Long Intger will be returned from the DateDiff function. The function should return the data type of the field bound to the control. Additionally, the objects referenced in the control source expression should be the names of controls on the form, not fields in the form's recordset. Barring all that, have you done a trace through the code to be sure it is executing and what it is doing? -- Dave Hargis, Microsoft Access MVP "JString" wrote: Hello Judy Queries have different rules then forms do. In a form, you have to use object oriented statements to get a field's value. When you call the function, try using this statement: returnvalue = CalcWeeks(DateStart.Value,DateEnd.Value) "JudyB" wrote: I have the following function: Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
#9
|
|||
|
|||
Public Function Problems
Thanks for all the help...I finally found my problem. I had an old function
that the subform was pulling from. Once I deleted, all worked fine. -- JudyB "JudyB" wrote: Hi Dave, I am using the following: Date Start Date End Service Time 7/9/1984 7/9/1985 0 Format for both dates is setup as short dates. I hope this is the type information you were requesting. -- JudyB "Klatuu" wrote: What start and end dates are you using that returns 0? -- Dave Hargis, Microsoft Access MVP "JudyB" wrote: Hi Dave, I used the expression =CalcWeeks([DateStart],[DateEnd]) in the Control Source property text box. Returns a "0" value. I used the ?CalcWeeks(#1/1/2008#,#12/31/2008#) code in the Immediate Window of VBA just to test the function to see if it worked correctly. It did. Still very confused. Any other suggestions. Am I supplying enough information? Thanks! -- JudyB "Klatuu" wrote: The expression should be in the Control Source property text box in the properties dialog for the control. It should start with an =, not a ? =CalcWeeks(#1/1/2008#,#12/31/2008#) If you did not use the correct control names, the control would probably contain #Error -- Dave Hargis, Microsoft Access MVP "JudyB" wrote: Hi Dave, Thanks for the help. I removed the return type of the function "is Single" as suggested. I also opened the Immediate Window of VBA and typed ?CalcWeeks(#1/1/2008#,#12/31/2008#) and it returned the value of 52. The query still works, but I still cannot get the calculated control in the subform to work. If I were using the incorrect names of controls within the subform, wouldn't I get an error when I type in the DateDiff("ww", DateStart, Nz(DateEnd, Date()) expression? This is my first database, so I am not quick to understand. Any other suggestions as to what my problem might be? Thanks in advance. -- JudyB "Klatuu" wrote: The value property is not required. There are a couple of issues that do need addressing. First, the return type of the function is Single. That is not necessary as a Variant of Long Intger will be returned from the DateDiff function. The function should return the data type of the field bound to the control. Additionally, the objects referenced in the control source expression should be the names of controls on the form, not fields in the form's recordset. Barring all that, have you done a trace through the code to be sure it is executing and what it is doing? -- Dave Hargis, Microsoft Access MVP "JString" wrote: Hello Judy Queries have different rules then forms do. In a form, you have to use object oriented statements to get a field's value. When you call the function, try using this statement: returnvalue = CalcWeeks(DateStart.Value,DateEnd.Value) "JudyB" wrote: I have the following function: Public Function CalcWeeks(DateStart As Variant, DateEnd As Variant) As Single If IsNull(DateStart) Then CalcWeeks = Null Exit Function End If CalcWeeks = DateDiff("ww", DateStart, Nz(DateEnd, Date)) End Function I was concerned that the code above CalcWeeks = ...Nz(DateEnd, Date)) since it did not have the parentheses at the end ...Date())) but the system converts it back to the way it is shown above and I was told not to worry about it that it would still work. I am using this function in a query and it works great. I have been trying to use it in my subform as well and it just doesn't work. It returns the number "0". This is the code that I have used in the control source of the WeeksService field: =CalcWeeks([DateStart],[DateEnd]) I can type in the code DateDiff("ww", DateStart, Nz(DateEnd, Date)) and it works great. Can someone please tell me why the function is not working in the subform but works in the query and how to correct the problem? Any help would be greatly appreciated. Thanks in advance. This is really wearing me down and I have much more work to do before completing this database. -- JudyB |
Thread Tools | |
Display Modes | |
|
|