#Name? Error with DateAdd function

[Leslie M]

I have a report based on a crosstab query which uses a form to specify date
parameters. Per Duane Hookum's recommendations on other posts I set up the
query so that the column headers display relative dates - Mth0, Mth1, etc.
However when I attempt to use the DateAdd fuction to add relative month
labels to my report, I consistently get a #Name? error.

Text Box Control Source: =DateAdd("m",0,[Forms]![frmMLUsage]![TrendStartDate])
Form: frmMLUsage
Form Text Box: TrendStartDate

Can anyone help me figure out why I keep getting this error?

Thank you.

 

[KARL DEWEY]

The form must be open and you will need data in it.

 

[Leslie M]

Karl,

Thanks for your reply. When the form is open I get a runtime error - "You
entered an expression that has no value." I worked on this all day of
Friday, and can't for the life of me figure out what I'm doing wrong.

--
Leslie M

The form must be open and you will need data in it.
--
KARL DEWEY
Build a little - Test a little

I have a report based on a crosstab query which uses a form to specify date
parameters. Per Duane Hookum's recommendations on other posts I set up the
query so that the column headers display relative dates - Mth0, Mth1, etc.
However when I attempt to use the DateAdd fuction to add relative month
labels to my report, I consistently get a #Name? error.

Text Box Control Source: =DateAdd("m",0,[Forms]![frmMLUsage]![TrendStartDate])
Form: frmMLUsage
Form Text Box: TrendStartDate

Can anyone help me figure out why I keep getting this error?

Thank you.

 

[KARL DEWEY]

You need to use the fields from the query.
--
KARL DEWEY
Build a little - Test a little

Karl,

Thanks for your reply. When the form is open I get a runtime error - "You
entered an expression that has no value." I worked on this all day of
Friday, and can't for the life of me figure out what I'm doing wrong.

--
Leslie M

The form must be open and you will need data in it.
--
KARL DEWEY
Build a little - Test a little

I have a report based on a crosstab query which uses a form to specify date
parameters. Per Duane Hookum's recommendations on other posts I set up the
query so that the column headers display relative dates - Mth0, Mth1, etc.
However when I attempt to use the DateAdd fuction to add relative month
labels to my report, I consistently get a #Name? error.

Text Box Control Source: =DateAdd("m",0,[Forms]![frmMLUsage]![TrendStartDate])
Form: frmMLUsage
Form Text Box: TrendStartDate

Can anyone help me figure out why I keep getting this error?

Thank you.

 

[Leslie M]

I'm not sure I follow. When I set this up, I used the instructions below
from another post...

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP

Sorry to be so dense, but do you have any other suggestions?
--
Leslie M

You need to use the fields from the query.
--
KARL DEWEY
Build a little - Test a little

Karl,

Thanks for your reply. When the form is open I get a runtime error - "You
entered an expression that has no value." I worked on this all day of
Friday, and can't for the life of me figure out what I'm doing wrong.

--
Leslie M

The form must be open and you will need data in it.
--
KARL DEWEY
Build a little - Test a little


:

I have a report based on a crosstab query which uses a form to specify date
parameters. Per Duane Hookum's recommendations on other posts I set up the
query so that the column headers display relative dates - Mth0, Mth1, etc.
However when I attempt to use the DateAdd fuction to add relative month
labels to my report, I consistently get a #Name? error.

Text Box Control Source: =DateAdd("m",0,[Forms]![frmMLUsage]![TrendStartDate])
Form: frmMLUsage
Form Text Box: TrendStartDate

Can anyone help me figure out why I keep getting this error?

Thank you.

 

[KARL DEWEY]

When you run your crosstab query by it's self it should look like this --
xxxx Mth0 Mth1 Mth2 Mth3 .... Mth11
XYZ 1 4 0 1 6
ABC 3 2 5 4 2

If it does then open your report in design view, click on menu VIEW - Field
List. Drag the fields for the months (Mth0 Mth1...) into position in your
report design.

--
KARL DEWEY
Build a little - Test a little

I'm not sure I follow. When I set this up, I used the instructions below
from another post...

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP

Sorry to be so dense, but do you have any other suggestions?
--
Leslie M

You need to use the fields from the query.
--
KARL DEWEY
Build a little - Test a little

Karl,

Thanks for your reply. When the form is open I get a runtime error - "You
entered an expression that has no value." I worked on this all day of
Friday, and can't for the life of me figure out what I'm doing wrong.

--
Leslie M


:

The form must be open and you will need data in it.
--
KARL DEWEY
Build a little - Test a little


:

I have a report based on a crosstab query which uses a form to specify date
parameters. Per Duane Hookum's recommendations on other posts I set up the
query so that the column headers display relative dates - Mth0, Mth1, etc.
However when I attempt to use the DateAdd fuction to add relative month
labels to my report, I consistently get a #Name? error.

Text Box Control Source: =DateAdd("m",0,[Forms]![frmMLUsage]![TrendStartDate])
Form: frmMLUsage
Form Text Box: TrendStartDate

Can anyone help me figure out why I keep getting this error?

Thank you.

 

[Leslie M]

That's exactly how the query looks, but I'm trying to get the report labels
to show the actual month rather than a relative month, i.e. May, Apr, Mar,
etc. rather than Mth0, Mth1, Mth2. Any thoughts?

--
Leslie M

When you run your crosstab query by it's self it should look like this --
xxxx Mth0 Mth1 Mth2 Mth3 .... Mth11
XYZ 1 4 0 1 6
ABC 3 2 5 4 2

If it does then open your report in design view, click on menu VIEW - Field
List. Drag the fields for the months (Mth0 Mth1...) into position in your
report design.

--
KARL DEWEY
Build a little - Test a little

I'm not sure I follow. When I set this up, I used the instructions below
from another post...

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP

Sorry to be so dense, but do you have any other suggestions?
--
Leslie M

You need to use the fields from the query.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

Thanks for your reply. When the form is open I get a runtime error - "You
entered an expression that has no value." I worked on this all day of
Friday, and can't for the life of me figure out what I'm doing wrong.

--
Leslie M


:

The form must be open and you will need data in it.
--
KARL DEWEY
Build a little - Test a little


:

I have a report based on a crosstab query which uses a form to specify date
parameters. Per Duane Hookum's recommendations on other posts I set up the
query so that the column headers display relative dates - Mth0, Mth1, etc.
However when I attempt to use the DateAdd fuction to add relative month
labels to my report, I consistently get a #Name? error.

Text Box Control Source: =DateAdd("m",0,[Forms]![frmMLUsage]![TrendStartDate])
Form: frmMLUsage
Form Text Box: TrendStartDate

Can anyone help me figure out why I keep getting this error?

Thank you.

 

[Leslie M]

Karl,

I was able to resolve my problem. There was an error in the code for the
form. Thanks for your efforts on my behalf.
--
Leslie M

That's exactly how the query looks, but I'm trying to get the report labels
to show the actual month rather than a relative month, i.e. May, Apr, Mar,
etc. rather than Mth0, Mth1, Mth2. Any thoughts?

--
Leslie M

When you run your crosstab query by it's self it should look like this --
xxxx Mth0 Mth1 Mth2 Mth3 .... Mth11
XYZ 1 4 0 1 6
ABC 3 2 5 4 2

If it does then open your report in design view, click on menu VIEW - Field
List. Drag the fields for the months (Mth0 Mth1...) into position in your
report design.

--
KARL DEWEY
Build a little - Test a little

I'm not sure I follow. When I set this up, I used the instructions below
from another post...

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.
--
Duane Hookom
MS Access MVP

Sorry to be so dense, but do you have any other suggestions?
--
Leslie M


:

You need to use the fields from the query.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

Thanks for your reply. When the form is open I get a runtime error - "You
entered an expression that has no value." I worked on this all day of
Friday, and can't for the life of me figure out what I'm doing wrong.

--
Leslie M


:

The form must be open and you will need data in it.
--
KARL DEWEY
Build a little - Test a little


:

I have a report based on a crosstab query which uses a form to specify date
parameters. Per Duane Hookum's recommendations on other posts I set up the
query so that the column headers display relative dates - Mth0, Mth1, etc.
However when I attempt to use the DateAdd fuction to add relative month
labels to my report, I consistently get a #Name? error.

Text Box Control Source: =DateAdd("m",0,[Forms]![frmMLUsage]![TrendStartDate])
Form: frmMLUsage
Form Text Box: TrendStartDate

Can anyone help me figure out why I keep getting this error?

Thank you.