Vb.net 2008: Issue retrieving data where Boolean = True

[rw409168]

Greetings,

I have created a crystal report in vb.net 2008 where the data source connection is via OleDbConnection = Microsoft.ACE.OLEDB.12.0 connecting to access 2007 database .accdb.

The report displays ALL of the data without a problem in the standard template built through the wizard.

When using the field 'deceased' an access Yes/No (Boolean) field for the record selection criteria, it ONLY retrieves rows where the value is FALSE.

I did notice that MS Access SQL equates the Yes/no field values to TRUE = -1 and FALSE = 0 whereas when showing the SQL view in crystal reports TRUE = 1 and FALSE = 0.

Changing the select criteria to use either the boolean value of TRUE or number value of 1 then NO rows are retrieved.

I can see rows exists as without the record selection criteria the deceased field is printed in the report with rows with a TRUE and FALSE values.

When I said setting the record selection criteria to True always brings back NO rows, well it sometimes does the first time then never again, when I refresh no rows appears or If I reload vb.net and run the preview again.

It's like the boolean value is automatically changed to false.

I set up a test database and I cannot access records based on the boolean value of a ms access yes/no field if equals TRUE.

It only works where the field value is FALSE.

I've been looking at this on/off for 2 days and have also posted in the microsoft public crystal reports newsgruop without success.

A googe search was fruitless too, worried as I thought if working with access and yes/no fields caused issues there would be others experiencing this issue.

Happy to attach any files if necessary.

Thanks
Rob

 

[lbass]

If you right click on the field->browse, what datatype do you see and what values are shown--within Crystal?

-LB

 

[rw409168]

Thanks for the reply, as per your instruction the type is Boolean and the values False and True are shown (in that order).

 

[lbass]

Your record selection formula should work. I wonder whether this has to do either with linking or with the syntax of your entire record selection formula. Can you paste the show SQL query here and also paste the selection formula (in case it doesn't pass to the query) from report->selection formula->record?

PS. When you are adding the value in the selection criteria, you are not enclosing the true in quotes, are you?

-LB

 

[rw409168]

Report selection formula, no quotes just the word true which appears in blue (indicating it is valid), allows saving without any error warnings.

{Members.cDeceased} = true

This equates to the following SQL from 'show SQL query' option.

SELECT `Members`.`cMembershipId`, `Members`.`cTitle`, `Members`.`cSurname`, `Members`.`cForeNames`, `Members`.`cWRorBranch`, `Members`.`cDateLastVisited`, `Members`.`cHouseNum`, `Members`.`cAddressLine1`, `Members`.`cAddressLine2`, `Members`.`cTown`, `Members`.`cCity`, `Members`.`cCounty`, `Members`.`cPostcode`, `Members`.`cTelephone`, `Members`.`cMobile`, `Members`.`cDeceased`
FROM `Members` `Members`
WHERE `Members`.`cDeceased`=1


The one thing I noticed is that the FROM line repeats table Members twice, is this usual?

Does confuse me when the field selection shows report fields and also the database connection with tables and columns.

 

[lbass]

The second table reference is an alias, and is standard syntax.

What happens if you create a formula in the field explorer like this:

//{@Deceased}:
if {Members.cDeceased} = true then 1

...and then you use a record selection formula (report->selection formula->record like this:

{@Deceased} = 1

You might be right about the underlying conversion being the problem.

-LB

 

[rw409168]

Following your instructions it does appear to be a conversion problem.

It printed out the rows where the deceased field is true, printing out the value 1.00 and also if change the selection formula printed out 0.00 records too, lastly with both records together.

So would it be possible to use a nested if in the formula so if the value is 1 print True else print False?

Very suprised not to have found other people experiencing the same issue.

 

[lbass]

Are you saying that you left {@Deceased} as is and simply set it to:

{@Deceased} = 0

...and it returned BOTH true and false records?

-LB

 

[rw409168]

Sorry for the confusion.

If {@Deceased} = 0 it prints out the false records.
If {@Deceased} = 1 it prints out the true records.

So it retrieves all the correct rows

Remove the selection critera the formula fields prints both 0.00 and 1.00 on the report as expected.

So would the way forward be a forumla along the lines of if {@Deceased} = 0 then print false else print true?

I've only been testing in crystal reports, starting at the very root of the issue.

Within my application I'm passing the parameter via vb.net form I will have to ensure that when I convert the checkboxed.checked value it is set to 1 rather than -1 when checked.

 

[rw409168]

I've got myself confused now as the checkbox on the form will be include deceased.

If it is ticked then I want to select ALL rows if unticked then select only rows where the deceased value is FALSE or 0.

With my current parameter setup it was a case of select if ALIVE or DEAD only and not one for everyone.

Is this possible using a single parameter?

 

[lbass]

I'm not familiar with using a vb.net form. In Crystal, you would set up a parameter with values 0 and 1 and then use a record selection formula like this:

{@Deceased} = {?Parm}

-LB

 

[rw409168]

Thanks for all your help I think i've got it all covered.

Im using a checkbox and using the formula below to make sure the include deceased checkbox functions correctly.

Formual field
if {Members.cDeceased} = true then 1 else if {Members.cDeceased} = false then 0

Record selection
if {?paramDeceased} = true then {@@Deceased} = 0 or {@@Deceased} = 1 else if {?paramDeceased} = false then {@@Deceased} = 0

Cheers, spent a while trying to get my head around things

I can later recode if they want an option just to see the deceased.

 

[rw409168]

Actually Im still in the dark now I had to use a formula field ({?paramDeceased}) to convert {Members.cDeceased} boolean to 0 or 1 how I can print the words Yes/No or True/False on the report.

 

[lbass]

Your record selection formula doesn't make sense to me, since you are saying if deceased, then show both 0 and 1 values. And {?paramDeceased} is not a formula, it is a parameter.

Regardless, you can change the display of a field using format field->display string->x+2 by entering:

if currentfieldvalue = 0 then "False" else
if currentfieldvalue = 1 then "True"

The result in the display string formula area must always be a string.

-LB

 

[rw409168]

Thanks, the logic works fine and used radio buttons on the vb.net form to allow coverage for all predicaments (Deceased = True, Deceased = False, All records)

Cheers for all your help, my third day of using crystal reports and learning plenty every day