Vb.net 2008 - Crystal Reports boolean data retrieval issue

[Robtyketto]

Greetings,

I'm writing an application for membership maintenance, part of this is to record if a member is deceased.

The data is stored in an access 2007 database (accdb), the deceased field being of 'Yes/No' datatype with no default value.

In Access I can write SQL to retrieve rows where deceased = true or deceased = false, the expected rows are retrieved.

The next step was to create a crystal report, adding the database connection and the report layout.

When running the preview WITHOUT ANY selection/paramater criteria ALL rows are retrieved with the deceased column printing the words True or False.

Now the headache (looked at this for over 24hrs now!! arghh) is if I use the select expert to find rows where deceased = true NOTHING is returned. Where deceased = false retrieves rows.

The same with selection formula for a record, retriews ROW if deceased clause isn't present or deceased = false.

Sometimes, the deceased = true clasue will bring back the true records ONLY, however if I refresh the row disappears.

Originally I thought this was a vb.net code issue until I stripped it back, testing the data retireval in crystal reports.

So I know 100% its connecting to the database, has rows with values of both True and false in the deceased column.

This is confirmed when ALL records are show on the report.

Can anyone think what the problem might be? A bug? A setup issue?

It just won't retrieve rows where deceased value is TRUE.
Happy to send application over for closer inspection.

Really frustrated and confused.

Thanks
Rob

[Robtyketto]

I decided to start over again so I created a test database with a Boolean column and a unique id field, containing a mixture of Yes/No values in access.

Setup a new crystal report based on said database and when I set values in the record selection for the Boolean to be TRUE, rows were retrieved for the FIRST time only.

I could not get the rows to be retrieved again, even closing vb.net down and restarting the same query/report preview.

Has anyone else experienced problems with Booleans like this before?

Anyone willing to create a two field database and then in vb.net setup a new crystal report and change the record selection so <data colum> = true and then false and then true again and examining the results?

Thanks
Rob

[Robtyketto]

Upon further investigation, when setting the record selection for the cDeceased column (Yes/No ms access column) to true/false it converts the values to 1 and 0.

This can be seen using the show SQL option in crystal reports:-

TRUE

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

FALSE

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`=0

In MS Access SQL the value of TRUE is -1 and NOT 1

I still haven't progressed in resolving this though, stressed .... :|