Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by MalcolmW

  1. MalcolmW

    Null values

    Try... if Isnull ({ta_cla.street_type}) then 0 else {ta_cla.street_type} Malcolm Wynden Authorized Crystal Engineer http://www.wynden.net malcolm@wynden.net
  2. MalcolmW

    Don't want to insert duplicate records

    There are three set operators - UNION, INTERSECT, and EXCEPT. Union is by far the most common, but the others have uses too. SELECT StartDate,EndDate, [Town], Event, Description, Location, Time, Cost, Phone, PhoneAlt, 1 as active FROM tblInputEvents EXCEPT SELECT StartDate,EndDate...
  3. MalcolmW

    FInding most recent date/result

    A simple solution is just to use a conditional running total to get the max of the date for each patient ID, using the component ID as a condition. Repeat for various different component ID's. Sorry if that is a bit cryptic - it's my bedtime. Malcolm Wynden Authorized Crystal Engineer...
  4. MalcolmW

    is the order formulas are placed on report IMP??

    No, I stand corrected Ken, at least for current versions of the product. The last time I really had to get into this was before v5, and on reflection I think the evaluation order must have changed with the incorporation of sub sections in CR. I was curious about how you could determine the...
  5. MalcolmW

    Creating a report from a SQL query

    You could also just rewrite your WHERE clause as a derived table, then add it to the FROM clause. This is a compromise solution - better than a QRY file (anything is) ;-), but it is less transparent SQL than using a view/stored proc. Malcolm Wynden Authorized Crystal Engineer...
  6. MalcolmW

    How do you sort this?

    Oh, come on, the easiest way is to store numbers as numbers in the database in the first place :-) Malcolm Wynden Authorized Crystal Engineer http://www.wynden.net malcolm@wynden.net
  7. MalcolmW

    Can Grow

    What effect do you want to achieve? Malcolm Wynden Authorized Crystal Engineer http://www.wynden.net malcolm@wynden.net
  8. MalcolmW

    is the order formulas are placed on report IMP??

    If you mean the sequence in which the formulas are placed on the report, then there is no impact. If you mean the position on the report, then location (actually the top left corner of the formula object) determines priority. And unless the formulas share information (as Ken pointed out...
  9. MalcolmW

    16 Table Max in SQL 6.5

    The SQL statement that Crystal Reports uses must begin with SELECT. So just drop that CREATE VIEW part off.;-) Next, CR doesn't like you messing around with Grouping, so you need to can that also. Lastly, when doing UNIONS, the order of the fields is important, so a full select might be...
  10. MalcolmW

    How do you sort this?

    Create a formula that converts your field from text to numeric, and then sort on that formula field. Malcolm Wynden Authorized Crystal Engineer http://www.wynden.net malcolm@wynden.net
  11. MalcolmW

    Formula help....

    your conditions are all OR - are you sure about that? If so, this should work... If IsNull({people.last_name}) then Counter Else If (Not IsNull({refer.answer})) and {refer.answer} = "T" then Counter Else If Not IsNull({term2.idnumber}) then Counter Else Counter := Counter + 1 Malcolm...
  12. MalcolmW

    adding columns with null values

    The SUM function in SQL Server ignores null values, so just select SUM(col1 + col2 + col3) from table will do the trick. In general, aggregate functions ignore null values. COUNT is an exception, as it does count null values. Malcolm Wynden Authorized Crystal Engineer http://www.wynden.net...
  13. MalcolmW

    Left outer join...

    As you cannot edit the SELECT portion of the SQL statement in Crystal, the above wouldn't work. As well, the value for b.field would still be null, as appending another value to the result set wouldn't alter the value of b.field. SELECT a.somefield, b.field FROM Table1 AS a LEFT JOIN Table2 AS...
  14. MalcolmW

    Pushing Data Down to the Server with (LIKE)

    Two steps: 1 Record selection formula {%SQLExprSERIAL} like {?serial} 2 SQL Expression formula SQLExprSERIAL IIF (CCALL.`SERIAL` Is Null,'',CCALL.`SERIAL`) The result will be parsed like: SELECT blah, blah, IIF (CCALL.`SERIAL` Is Null,'',CCALL.`SERIAL`) FROM blah... WHERE IIF...
  15. MalcolmW

    Running Totals in CR7

    Are you using the CR running totals, or making your own? Are you checking for null values if you are making your own? Malcolm Wynden Authorized Crystal Engineer http://www.wynden.net malcolm@wynden.net
See more

Part and Inventory Search

Back
Top