Source language: Translate to:

Access DB using Yes/No Field

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Access DB using Yes/No Field

Postby datadon » Wed Mar 05, 2014 9:39 am

I'm using an Access DB and trying to load a table that has had records "selected" perviously by putting a check into the Yes/No field. Some records have checks and others not.

When I try to open the table I run this query before displaying the info.

Code: Select all
dbpQuery "CSI" "48Sections" "Choose = Yes"


The field is called Choose and when I look at the table in Access it shows the values to be Yes/No and not true or false or 1 or 0

But it always shows all the records. It gives me no error message. I've tried using 'Yes' but that gives error. I also tried like instead of = still no cigar.

Anyone come across this before with solution? Or obvious....

Thanks
Don
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Re: Access DB using Yes/No Field

Postby Gaev » Wed Mar 05, 2014 10:41 am

Don:

1) If you are loading from a csv file, what are the values in the csv file ?

2) How is the Choose field defined ? ... string/text ? ... boolean ? ... other ?

3) Try one of ...

"Choose IS TRUE"
"Choose IS FALSE"
"Choose IS 1"
"Choose IS 0"
"Choose IS NULL"
"Choose IS NOT NULL"
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Access DB using Yes/No Field

Postby datadon » Wed Mar 05, 2014 10:58 am

I may have said it wrong, loading is not correct, I am just opening an Access Database. No CSV involved. Data is already in. Access has a logical field that can be set to Yes/No or True/False etc. Mine is set to Yes/No

I tried the "Choose IS Yes" but says it cannot get valued for closed dataset or something like that.

I'll try a few other variations.

Thanks
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Re: Access DB using Yes/No Field

Postby Rasl » Wed Mar 05, 2014 1:03 pm

I used the instruction with yes/no and brings the results correctly, you should check your database
Code: Select all
dbpQuery "CSI" "48Sections" "Choose = Yes"
Rasl
 
Posts: 129
Joined: Sat Apr 02, 2005 8:25 am
Location: Buenos Aires, Argentina

Re: Access DB using Yes/No Field

Postby datadon » Wed Mar 05, 2014 1:11 pm

Hmmm

Does your database show a check box when viewing the data for this field?

Is your database a .accdb or mdb?

Thanks for your help
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Re: Access DB using Yes/No Field

Postby Gaev » Wed Mar 05, 2014 3:03 pm

Don:

I setup an Access (mdb) Table with a field called Choose (of Type Boolean).

This query ...
Code: Select all
dbpQuery "DataDon" "DataDon" "Choose = True"
... worked for me.
Does your database show a check box when viewing the data for this field?

Yes ... and when clicked (within the Grid), it alternates between a "check mark" and "nothing".

P.S. Make sure you are using a consistent case for the field reference ... Choose IS NOT SAME AS choose
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Access DB using Yes/No Field

Postby datadon » Wed Mar 05, 2014 3:36 pm

I'm using Access 2007 which has filename.accdb

Absolutely nothing is working with this. I've checked all the caps etc. all is good. The database works fine except for this.

In doing some research, it says the values for Yes/No are -1 for Yes and 0 for No. I even tried those values and nothing.

Some comments say they are having similar problems when trying to query with sql outside of Access.

I don't recall having a problem with this when using .mdb files. Maybe that is the issue.

Maybe someone has had this issue and resolved it using the .accdb file. If not I will go to another method to choose records.

Thanks for the help.
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Re: Access DB using Yes/No Field

Postby Rasl » Wed Mar 05, 2014 4:17 pm

I use both types of database with identical results
Rasl
 
Posts: 129
Joined: Sat Apr 02, 2005 8:25 am
Location: Buenos Aires, Argentina

Re: Access DB using Yes/No Field

Postby datadon » Wed Mar 05, 2014 4:58 pm

sigh....I'm cursed. I knew it I knew it...:-)
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Re: Access DB using Yes/No Field

Postby Gaev » Wed Mar 05, 2014 8:23 pm

Cursed ... I mean Don ... try these ...

Choose = -1
Choose <> 0
Choose

... the last one is not a typo ... just the name of the Boolean field

Also, try and use dbpExecSQL "database id" "dbpExecSQL "DataDon" "Select * from DataDon where xxx" "" ... where xxx is one of ...

Choose = -1
Choose <> 0
Choose

If still cursed, tell us what values are returned for [yourdatabase.yourtable.Choose] ... for checked and unchecked values ... and what values are placed in an exported csv file.
User avatar
Gaev
 
Posts: 3735
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Access DB using Yes/No Field

Postby datadon » Thu Mar 06, 2014 5:07 am

Gaev

I did export to csv and the values came out as.....TRUE AND FALSE. Even though Access clearly says the values are Yes/No or -1 and 0.

Microsoft?

Anyway I went ahead and (again) tried every possible combination of query string I could think of including all those suggested. I even tried it on different machines and op systems. I used single quotes and [#91][#93] combinations etc. Still no cigar.

I even exported to a mdb version and same results.

I'm gonna put this file and table online and put a link here for anyone that cares to try it. I'll even put a small app to run that has the same code as me.

Then we'll see......yes then we'll see says the mad man. :-)

Back in a bit after I post it somewhere...

Don
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Re: Access DB using Yes/No Field

Postby datadon » Thu Mar 06, 2014 6:33 am

If anyone wants to try them out here is the link

http://www.ezsellphotos.com/csischedule.zip

It has both file types .accdb and .mdb

The table is called 48Sections

The text file has the open and display NB code and the query that "does not work"

I really appreciate any help. Thanks

Don
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Re: Access DB using Yes/No Field

Postby Wrangler » Thu Mar 06, 2014 7:52 am

Note that a boolean checkbox with DBPro uses true or false, which are CASE SENSITIVE. You should use True or False. Also, it appears that when the checkbox is first checked, Neobook marks it as 'checked'. When the database is saved, this changes to True or False. I get around this by querying for both checked AND True.

Don't know if this will help you any. But it's good to know. :)
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Re: Access DB using Yes/No Field

Postby datadon » Thu Mar 06, 2014 8:51 am

Thanks Wrangler, but still no cigar.

What exactly does "Cannot perform this operation on a closed dataset" mean.

Is it trying to say the table is closed or ?
Don

'If you want to get a brontosaurus from 'a' to 'b' then you ride the dinosaur - you don't carry it!'
User avatar
datadon
 
Posts: 389
Joined: Sun Apr 03, 2005 7:55 pm
Location: Lorena Texas

Re: Access DB using Yes/No Field

Postby Wrangler » Thu Mar 06, 2014 8:59 am

Are you properly opening the database and then open the table? Seems I got this error in the past because I opened the db, but not the table.

Or maybe you closed the table in your code somewhere and then forgot to re-open it?

There is also the possiblity that the database is corrupt. This can happen sometimes when you're trying code on a db and did something it didn't like. Create a new test db and see if it works.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Next

Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron