Problems with conditional format - DATEADD function

I've been struggling with this problem for over a week now. I have a report
that gives me product renewal dates, in the table I want to highlight rows
depending on what month the renewal date falls into.
Sound pretty straightforward but its been anything but!
In my query I select the renewal dates I want using: >= DATEADD(mm, 1,
GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
However when I use the same DATEADD statement to conditionally format the
table I get the error: Argument 'Interval' is not a valid value. For some
reason it doesn't like anything I put for the Month interval in the
expression. I've tried every permatation I can think of for month including
putting "" round it but it doesn't like it - code is below
=IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
"Yellow",
IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
"Green",
IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
"Blue", "Gray"
)))
Any help would be really appreciated!!!On Jan 14, 6:05=A0am, Jack <J...@.discussions.microsoft.com> wrote:
> I've been struggling with this problem for over a week now. I have a repor=t
> that gives me product renewal dates, in the table I want to highlight rows=
> depending on what month the renewal date falls into.
> Sound pretty straightforward but its been anything but!
> In my query I select the renewal dates I want using: >=3D DATEADD(mm, 1,
> GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
> However when I use the same DATEADD statement to conditionally format the
> table I get the error: Argument 'Interval' is not a valid value. For some
> reason it doesn't like anything I put for the Month interval in the
> expression. I've tried every permatation I can think of for month includin=g
> putting "" round it but it doesn't like it - code is below
> =3DIIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
> "Yellow",
> IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
> "Green",
> IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
> "Blue", "Gray"
> )))
> Any help would be really appreciated!!!
Try DateInterval.Month instead of "mm" e.g. DateAdd(DateInterval.Month,
2,Today())
That should do the trick.
Happy reporting!
toolman|||I actually found the answer elsewhere - I should have been using "m" instead
of "mm".
Haven't tried your solution but I'm sure it works so thanks as well!
"toolman" wrote:
> On Jan 14, 6:05 am, Jack <J...@.discussions.microsoft.com> wrote:
> > I've been struggling with this problem for over a week now. I have a report
> > that gives me product renewal dates, in the table I want to highlight rows
> > depending on what month the renewal date falls into.
> >
> > Sound pretty straightforward but its been anything but!
> >
> > In my query I select the renewal dates I want using: >= DATEADD(mm, 1,
> > GETDATE()) AND < DATEADD(mm, 4, GETDATE()) which works fine.
> >
> > However when I use the same DATEADD statement to conditionally format the
> > table I get the error: Argument 'Interval' is not a valid value. For some
> > reason it doesn't like anything I put for the Month interval in the
> > expression. I've tried every permatation I can think of for month including
> > putting "" round it but it doesn't like it - code is below
> >
> > =IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 2, Today()),
> > "Yellow",
> > IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 3, Today()),
> > "Green",
> > IIF (Fields!New_RenewalDate.Value < DATEADD("mm", 4, Today()),
> > "Blue", "Gray"
> > )))
> >
> > Any help would be really appreciated!!!
> Try DateInterval.Month instead of "mm" e.g. DateAdd(DateInterval.Month,
> 2,Today())
> That should do the trick.
> Happy reporting!
> toolman
>

Problems with accessing database remotely

I'm writing an web app in VS2005.

I've created a product.mdf database through VS2005's Server Explorer (to my knowledge, I'm not using Sql Express). (Side question. The .mdf files are a stand alone xml structure used as a database, correct?).

When I execute locally, everything works great. But when I moved my code to where it is being hosted (remote computer). It's throwing the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This is my connection string:

<addname="MyConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Product.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

What is my problem?

There is no SQL Server 2005 Express running on the remote server.

Jos

|||

Actually, you ARE using SQL Express. The Visual Studio environment does a great job in hiding this - but behind the scenes a SQL Express database engine is running on your local machine (you can see it in the Services.msc explorer). Your web application is using the web.config connection string tofind this SQL Express database.

As the previous reply says, the error message is due to the application trying to find a SQL Express database engine running on the remote computer and serving a database called Product.mdf.

The solution to your problem may be to install SQL Express on the remote computer. You will then need to copy (attach) the Product.mdf database to the remote database server.

However, if your "remote computer" is actually a hosting provider - they may not (and most don't) support SQL Express - and may insist that you use SQL Server Enterprise instead, which is a far more robust option anyway.

|||

Why I got the error message?

The error message you got was due to the using of SQL Express in your local (development) machine.

Ok, how to solve this issue?

It is easy. Eiteher use SQL Express on your production (e.g. hosting server) [NOT Recommended] or do the following:

Attach your .mdf file to a database (e.g. new database) in the sql server of the hosting company.
Change your ConnectionString to be as following:

<add name="MyConnectionString1" connectionString="Server=YourSqlServerName;Database=MyDatabase;Integrated Security=True;" providerName="System.Data.SqlClient"/>

YourSqlServerName: Type the name of the sql server as provided by the hosting company.

MyDatabase: The database you are using which reside in the hosting company sql server.

Note: The above ConnectionString is used with "Windows Authentication", for the "Mixed Mode" use the following ConnectionString:

<add name="MyConnectionString1" connectionString="Server=YourSqlServerName;Database=MyDatabase;User ID=MyUserID; Password:MyStrongPassword"providerName="System.Data.SqlClient"/>