#Name? Error with DateAdd function

L

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.
 
L

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


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


Leslie M said:
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.
Click to expand...
Click to expand...
 
K

KARL DEWEY

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


Leslie M said:
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


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


Leslie M said:
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.
Click to expand...
Click to expand...
Click to expand...
 
L

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


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


Leslie M said:
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


KARL DEWEY said:
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.
Click to expand...
Click to expand...
Click to expand...
 
K

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


Leslie M said:
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


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


Leslie M said:
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.
Click to expand...
Click to expand...
Click to expand...
 
L

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


KARL DEWEY said:
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


Leslie M said:
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


KARL DEWEY said:
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.
Click to expand...
Click to expand...
Click to expand...
 
L

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


Leslie M said:
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


KARL DEWEY said:
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


Leslie M said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Reference controls on a dialogue form as the parameters 1
Crosstab repor and total 3
Debtors Ledger 3
DateAdd function in Frontpage 2
Crosstab Report's subreport 1
Relative months In reports with cross tab query 1
Entering Report Parameters through a Form 2
Show Parameter Information in Report Footer 1

Top