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

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

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

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

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

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

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

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

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