Formula based on Query (.qry) changes field value!!

[dgenne]

I have a formula that is designed to place a 0 or a 1 in a column:
if (({Query.STRTDATE}) < Date({?Year of Query},{?Month Of Query},01)) AND (({Query.INACTIVE}=0) OR (({Query.INACTIVE}=1) AND ({Query.DEMPINAC}>=Date({?Year of Query},{?Month Of Query},01))) )then 1

This works as I want in a report based on an ODBC Data source. However when I use this exact formula in a report that has a Crystal SQL Query (.qry) as it's source, the value of the {Query.INACTIVE} field is actually changed. Why? I have not discovered a pattern in the values placed in the field, but they are in the hundreds of thousands-millions range. In the database and if I just pull the Query itself the values are only 1 or 0.

Why is it doing this and what am I doing wrong?
Thanks!
Douglas Genne
Palm Management Corporation

 

[synapsevampire]

I avoid Crystal Queries, and suggest others do so too.

I believe that they're to be phased out, as CR 9 allows for REAL SQL to be created.

My answer is the thing you're doing wrong is using Crystal Queries

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgenne]

Oh well. I was hoping that there would be an explanation, or at least something to use as a workaround; like a better way to format the equations or something. Looks like my next best bet is to try and get access to the Pervasive SQL server and figure out how to create Stored Procedures...
Thanks though!
Doug

 

[willisa]

I actually had a very similar thing happen, the way I fixed it was...

I deleted the offending formula re verified the database and then re created teh formula and it seemed to fix it.

 

[kenhamady]

When you say it changed the field value, I am assuming you don't mean changed in the database. If you mean that it doesn't read some values correctly from the database, the problem is usually a change in databse, the driver, or in the QRY.

If you verify the database in the QRY and then Verify the database in the Report, you should get clean data.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[willisa]

That is true about the verifying database thing, however every now and again crystal reports gets a real bug in its bonnet because in my case re verifying the database alone didn't work, I had to also delete the last formula I added that was based on the field that was having problems. Only when I deleted the formula re verifying worked.