 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / May 2008#Name? Error with DateAdd function
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.  SignatureLeslie M The form must be open and you will need data in it. SignatureKARL 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 [quoted text clipped - 9 lines] > > Thank you. 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. SignatureLeslie M > The form must be open and you will need data in it. > [quoted text clipped - 11 lines] > > > > Thank you. You need to use the fields from the query. SignatureKARL DEWEY Build a little - Test a little > Karl, > [quoted text clipped - 17 lines] > > > > > > Thank you. 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. SignatureDuane 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. > [quoted text clipped - 19 lines] > > > > > > > > Thank you. 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. SignatureKARL 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... [quoted text clipped - 49 lines] > > > > > > > > > > Thank you. 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? SignatureLeslie M > When you run your crosstab query by it's self it should look like this -- > xxxx Mth0 Mth1 Mth2 Mth3 .... Mth11 [quoted text clipped - 58 lines] > > > > > > > > > > > > Thank you. 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. SignatureLeslie 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, [quoted text clipped - 62 lines] > > > > > > > > > > > > > > Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.