Max length for an if statement

I am working on a formula in crystal using Visual Basic. I need to pull out lists of workers titles from a set list I have written down. I am using a fairly long if statement here and would like to know if there is a max number of conditions I can use in a given if statement.

Here is an example of what I am using.
*******************************

If (InStr({Concurrence.ConcurrenceName}, " MD", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, " COS ", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, ", M.D.", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, "M.D.", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, " M D", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, "D.O.", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, " DO", 1) > 0 _
or Instr({Concurrence.ConcurrenceName}, " DR.", 1) > 0 _
or Instr({Concurrence.ConcurrenceName}, " DR ", 1) > 0) _
or InStr({Concurrence.ConcurrenceName}, "MD", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, " DO ", 1) > 0 _
or InStr({Concurrence.ConcurrenceName}, "DR", 1) = 1) then
formula = "Physician"

ElseIf (InStr({Concurrence.ConcurrenceTitle}, "PHYSICIAN", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "ATTENDING", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "ACOS", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "COS ", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "CHIEF MEDICAL", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "CHIEF OF STAFF", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, " M.D.", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, " MD,", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "MD", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "M.D", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "D.O.", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "SURGEON", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "INTERNIST", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "DOCTOR", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, " CHIEFOFSTAFF", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "ANESTHESIOLOGIST", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "CARDIOLOGIST", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "CHIEFSOFSTAFF", 1) > 0 _
or InStr({Concurrence.ConcurrenceTitle}, "AOD", 1) > 0) then
formula = "Physician"

End If

For some reason crystal does not pull out the "PHYSICIAN" title in the second if else statement. However, if I put it in its own if statement it will evaluate properly. (I broke the if statement up for maintenance ease)

Like this:

If InStr({Concurrence.ConcurrenceTitle}, "PHYSICIAN", 1) > 0 Then
formula = "Physician"
End If

So, I am wondering if I simply have too long of an if statement and need to break this up in order to get all of the titles I am looking for.

Any suggestions on how long each if statement can be, or possibly a more efficient way to write this?

Thanks in advance.

RyanIf there are many IFs then why cant you use Case statement?sql

Matrix report, date: 1900-01-01 wrong!! Suppose to be empty, how?

Hi

I am making a report in Visual Studio. I’m making a matrix report. I have made a UNION of 2 tables. One of them has a field called Date and the other one does not. But to make a UNION of these 2 tables so that the results are printed in a Matrix I have to have the same fields’ aliases at least. So what I did is that in the second table is:

SELECT ‘ ‘ AS ‘Date’

Right?

Now, in the report the first table gives me the dates in a format:

=Format(Fields!Estimated_Close_Date.Value, "yyyy-MM-dd")

But, the second table is it doesn’t have a date in that field when I run the report it gives me:

1900-01-01

Something I don’t want.

So, how do I make it understand that the second tables date field is suppose to be empty?

Try something like this:

SELECT myDate AS DATE FROM MyTable

UNION ALL

SELECT CASE WHEN myBlankField = ' ' THEN ' ' ELSE ' ' END FROM OtherTable

And choose a field that is NOT a date for myBlankField. This will put blanks in the result set, but you can always just filter those out later.

|||

Thanks for your quick answer, sounds interesting, but I don't know quite where to put your example. Here's the code I have, maybe you know.

SELECT estimatedclosedate AS 'Date', blablabla...

INTO TempTable1

FROM blablabla join blabla and so on

WHERE blablabla AND blabla AND blabla and so on

SELECT ' ' AS 'Date', blablabla... -- This is the date that is troubling me

INTO TempTable2

FROM blablabla join blabla and so on

WHERE blablabla AND blabla AND blabla and so on

SELECT * FROM TempTable1

UNION

SELECT * FROM TempTable2

The TempTable1 gives me Opportunities from a CRM system, the TempTable2 gives me Ongoing Business in the CRM system. Of course, Opportunities have an estimated close date and Ongoing Business has not … so I just want it to show an empty cell in the matrix report.

So, where exactly could I put your suggestion?

|||

try -- select null as 'Date'., xyz........into temptable

Priyank

|||

Worked perfectly!!! Thanks mate!!!|||

can you please mark it as answer...|||

By the way ... do you know anything about my other thread I have here? About putting a tooltip window thing on one of my filters? One of my filters is done that the user can write what ever he wishes to filter on, either exactly or even with a % ... the thing is that I don't want to write on the Prompt: "Tradelane (STO-LAX, or STO%, or %STO) ... it's just to long ... do you happen to know how?

|||Mark it as answer? DONE!!

matrix

hey there

Can anyone direct me to explicit examples of a matrix.

eg I would like to see the layout view // then preview

I am using Visual Studio.net 2003 and Reporting Services

thanks

jewel

You can install the AdventureWorks sample reports during the setup installation of Reporting Services. The "Company Sales" sample report provides a matrix layout.

You can also take a look at this article: http://www.gotreportviewer.com/matrices/index.html
While that how-to article is targeted at the ReportViewer controls shipped in VS 2005, most of it still applies for Reporting Services 2000 / Report designer in VS 2003.

Also RS Books Online contains information about matrix reports, e.g.: http://msdn2.microsoft.com/en-us/library/ms157334.aspx

-- Robert

Materializing the cubes, absence of CREATE CUBE

Hi all,

(1) Does making a cube through Cube Wizard in Visual Studio imply materialiing the view
in a database?

I created a cube in AdventureWorksDW sample database. There is no error after I
deployed the project. Still the size of data files and transaction-log files of database
remain the same.

(2) Is the cube materialized? Can we materialize cubes?

(3) Shall Microsoft introduce operation CREATE CUBE in the MDX? I saw only
ALTER CUBE, CREATE SUBCUBE, and DROP SUBCUBE in online book.

Thank you,

Bernaridho

1) No

2) Yes, you can take the cubes offline

3) http://msdn2.microsoft.com/en-us/library/ms145581.aspx