Select based on a logical field

[Rena]

I am writing a report against a VFP data base. I need to select records based on a logical field being set to false. I displayed the field on the report and found that Crystal treats it as numeric - 0 or 1. I have tried selecting using the ToText function but I don't get any records. Anybody got any suggestions?

TIA,
Rena

 

[kenhamady]

You shouldn't need ToText.

Can't you use :

Field = 0

or

Field = 1

I seem to remember that there was a problem with VFP not getting valid values from the the logicals. Are you connnecting via ODBC or Datafile? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Rena]

I tried what you suggested and the Select Expert changed it to:
Field = 0.00
or
Field = 1.00

Since I didn't get any records when I did it this way, I tried the ToText to force it to leave off the decimal places.

I am connecting via ODBC.

Thanks.

 

[kenhamady]

Look at the following article from Seagate, it probably relates to your problem:

http://support.seagatesoftware.com/library/kbase/articles/c2003218.asp

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[Rena]

Hi Ken,

I checked out the article and will look into trying the dll it recommends. I'm not sure this is my problem because I ran the report with a different select and I put the logical field on the report just so I could see what the field contained and most of them were 1s. I just tried it again doing a select on another logical field: select field = 0 (and the editor changed it to 0.00) and it worked. So this makes it even more confusing. Why one works and the other doesn't. I'll keep playing. I am interested in any other suggestions you might have.

 

[kenhamady]

The extra zeros aren't the problem.

0.00 and 0 are the same.

I think it is that Crystal thinks it is a numeric, but when it gets to VFP it is looking for a logical value. Something isn't converting it correctly because under the hood logicals are just 1 and 0.

You might try looking at the SQL statemetn and see what Crystal is sending via ODBC. If one works you could compare them.

Database, Show SQL Query. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Rena]

Hi Ken,

Ok... I tried that. I put both conditions in the select and checked out the SQL stmt and they are the same.

Another interesting twist. I set the select to pull off records where field1 = 1 (this is the one that works). Ran the report and got the records I expected. Then I added field2 = 0 (the one that doesn't work), closed the select expert and chose the use-saved-data option and I get the records I expect. If I re-run the report and refresh the data then I get nothing.

We haven't found the solution yet but I appreciate all your help.
Rena

 

[kenhamady]

You have found the nub of the problem. The saved data (ie once it is in Crystal) is read as a different value than the database.

Can you use "=0" on the first one, the one that works with "=1" . Maybe it is the zero that is the problem. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Rena]

If select field1 (the one that works) =0 or =1, it works. If select field2 (the one that doesn't work) =0 or =1 it does not work. But here is what I found that works (ya know ya just get to the point of trying anything):

field2 <=0 works
field2 <> 0 works
field2 <> 1 works
field2 >=1 does not work

Go figure.

Thanks for all your help.

 

[kenhamady]

Maybe a rounding issue?
Glad that you got it to work. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)