Datediff on form and subform

[TomS]

I have tried to without success to have a calulated time elapsed field in my
subform. It involves an original date on the main form and a later date in
the subform. I want the calculated time to be on the subform.
Here is my control:
=DateDiff("ww", [Forms]![MainFormName][Date], [Visit Date])
and I get #NAME? from this.

I cannot get it to recognize the main form date. What should I do to fix
this? I cannot put the main form date on the subform because of the join
properties. I've tried running the calculation in a query which returns
correct values, but I also got #NAME? when I set this as the control source
for the control on the subform.
=[Queries]![Date Query]![Time]!

 

[Jeff Boyce]

Tom

First of all, don't use "Date" as the name of a date-related field. Access
treats "Date" as a reserved word, so what Access means and what YOU think it
means can differ.

Next, check your syntax ... I believe you need a "!" between [MainFormName]
and [WhateverDate].

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[TomS]

Jeff,

Thanks for your quick response. I have changed the date field name and I
actually did have an exclamation point between the form name and the field
(sorry for the typo). I will copy and paste what I have now.
=DateDiff("ww",[Forms]![SWL Data Sheet]![SDate],[Visit Date])

and it still gives puts out #NAME?
Would it be better to reference [SDate] from its table instead?

Tom

Tom

First of all, don't use "Date" as the name of a date-related field. Access
treats "Date" as a reserved word, so what Access means and what YOU think it
means can differ.

Next, check your syntax ... I believe you need a "!" between [MainFormName]
and [WhateverDate].

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have tried to without success to have a calulated time elapsed field in
my
subform. It involves an original date on the main form and a later date in
the subform. I want the calculated time to be on the subform.
Here is my control:
=DateDiff("ww", [Forms]![MainFormName][Date], [Visit Date])
and I get #NAME? from this.

I cannot get it to recognize the main form date. What should I do to fix
this? I cannot put the main form date on the subform because of the join
properties. I've tried running the calculation in a query which returns
correct values, but I also got #NAME? when I set this as the control
source
for the control on the subform.
=[Queries]![Date Query]![Time]!

 

[Jeff Boyce]

Are you absolutely certain it is not recognizing the mainform's date field?
You could test by swapping out the reference to the mainform's date field
with a hard-coded date, for example #8/1/2009#. If a hard-coded date value
works, then take another look at your spelling. Access uses #NAME when it
can't find something spelled that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your quick response. I have changed the date field name and I
actually did have an exclamation point between the form name and the field
(sorry for the typo). I will copy and paste what I have now.
=DateDiff("ww",[Forms]![SWL Data Sheet]![SDate],[Visit Date])

and it still gives puts out #NAME?
Would it be better to reference [SDate] from its table instead?

Tom

Tom

First of all, don't use "Date" as the name of a date-related field.
Access
treats "Date" as a reserved word, so what Access means and what YOU think
it
means can differ.

Next, check your syntax ... I believe you need a "!" between
[MainFormName]
and [WhateverDate].

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have tried to without success to have a calulated time elapsed field
in
my
subform. It involves an original date on the main form and a later date
in
the subform. I want the calculated time to be on the subform.
Here is my control:
=DateDiff("ww", [Forms]![MainFormName][Date], [Visit Date])
and I get #NAME? from this.

I cannot get it to recognize the main form date. What should I do to
fix
this? I cannot put the main form date on the subform because of the
join
properties. I've tried running the calculation in a query which returns
correct values, but I also got #NAME? when I set this as the control
source
for the control on the subform.
=[Queries]![Date Query]![Time]!

 

[TomS]

Jeff,
I'm not really sure what is going on. I had the subform closed and then I
reopened it, and all of a sudden, the data was displayed. However, I had the
fields in the wrong order so that the datediff was negative. I copy and
pasted switching the order of the fields in DateDiff, and then I got #NAME?
again. When I open this subform, the calculated field is usually blank for a
second while the other fields display fine. Then #Name? will flash into the
column. Do I need to insert an event to get it to update? Again, I appreciate
your help.

Tom

Are you absolutely certain it is not recognizing the mainform's date field?
You could test by swapping out the reference to the mainform's date field
with a hard-coded date, for example #8/1/2009#. If a hard-coded date value
works, then take another look at your spelling. Access uses #NAME when it
can't find something spelled that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your quick response. I have changed the date field name and I
actually did have an exclamation point between the form name and the field
(sorry for the typo). I will copy and paste what I have now.
=DateDiff("ww",[Forms]![SWL Data Sheet]![SDate],[Visit Date])

and it still gives puts out #NAME?
Would it be better to reference [SDate] from its table instead?

Tom

Tom

First of all, don't use "Date" as the name of a date-related field.
Access
treats "Date" as a reserved word, so what Access means and what YOU think
it
means can differ.

Next, check your syntax ... I believe you need a "!" between
[MainFormName]
and [WhateverDate].

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have tried to without success to have a calulated time elapsed field
in
my
subform. It involves an original date on the main form and a later date
in
the subform. I want the calculated time to be on the subform.
Here is my control:
=DateDiff("ww", [Forms]![MainFormName][Date], [Visit Date])
and I get #NAME? from this.

I cannot get it to recognize the main form date. What should I do to
fix
this? I cannot put the main form date on the subform because of the
join
properties. I've tried running the calculation in a query which returns
correct values, but I also got #NAME? when I set this as the control
source
for the control on the subform.
=[Queries]![Date Query]![Time]!

 

[TomS]

I just closed and reopened my main form and data was again displayed in the
calculated field of the subform. I toyed with the formula one more time and
it put out #Name again. But when I closed the subform and reopened the main
form, the data was displayed correctly. I think I may need to enter some code
to get it to update properly, but I'm not sure what that would be.

Tom

Are you absolutely certain it is not recognizing the mainform's date field?
You could test by swapping out the reference to the mainform's date field
with a hard-coded date, for example #8/1/2009#. If a hard-coded date value
works, then take another look at your spelling. Access uses #NAME when it
can't find something spelled that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your quick response. I have changed the date field name and I
actually did have an exclamation point between the form name and the field
(sorry for the typo). I will copy and paste what I have now.
=DateDiff("ww",[Forms]![SWL Data Sheet]![SDate],[Visit Date])

and it still gives puts out #NAME?
Would it be better to reference [SDate] from its table instead?

Tom

Tom

First of all, don't use "Date" as the name of a date-related field.
Access
treats "Date" as a reserved word, so what Access means and what YOU think
it
means can differ.

Next, check your syntax ... I believe you need a "!" between
[MainFormName]
and [WhateverDate].

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have tried to without success to have a calulated time elapsed field
in
my
subform. It involves an original date on the main form and a later date
in
the subform. I want the calculated time to be on the subform.
Here is my control:
=DateDiff("ww", [Forms]![MainFormName][Date], [Visit Date])
and I get #NAME? from this.

I cannot get it to recognize the main form date. What should I do to
fix
this? I cannot put the main form date on the subform because of the
join
properties. I've tried running the calculation in a query which returns
correct values, but I also got #NAME? when I set this as the control
source
for the control on the subform.
=[Queries]![Date Query]![Time]!

 

[Jeff Boyce]

Tom

Is your subform connected to your mainform by the Parent and Child
properties? How is the subform being populated?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I just closed and reopened my main form and data was again displayed in the
calculated field of the subform. I toyed with the formula one more time
and
it put out #Name again. But when I closed the subform and reopened the
main
form, the data was displayed correctly. I think I may need to enter some
code
to get it to update properly, but I'm not sure what that would be.

Tom

Are you absolutely certain it is not recognizing the mainform's date
field?
You could test by swapping out the reference to the mainform's date field
with a hard-coded date, for example #8/1/2009#. If a hard-coded date
value
works, then take another look at your spelling. Access uses #NAME when
it
can't find something spelled that way.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your quick response. I have changed the date field name and
I
actually did have an exclamation point between the form name and the
field
(sorry for the typo). I will copy and paste what I have now.
=DateDiff("ww",[Forms]![SWL Data Sheet]![SDate],[Visit Date])

and it still gives puts out #NAME?
Would it be better to reference [SDate] from its table instead?

Tom

:

Tom

First of all, don't use "Date" as the name of a date-related field.
Access
treats "Date" as a reserved word, so what Access means and what YOU
think
it
means can differ.

Next, check your syntax ... I believe you need a "!" between
[MainFormName]
and [WhateverDate].

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have tried to without success to have a calulated time elapsed
field
in
my
subform. It involves an original date on the main form and a later
date
in
the subform. I want the calculated time to be on the subform.
Here is my control:
=DateDiff("ww", [Forms]![MainFormName][Date], [Visit Date])
and I get #NAME? from this.

I cannot get it to recognize the main form date. What should I do to
fix
this? I cannot put the main form date on the subform because of the
join
properties. I've tried running the calculation in a query which
returns
correct values, but I also got #NAME? when I set this as the control
source
for the control on the subform.
=[Queries]![Date Query]![Time]!