Yes/No Control VB Syntax 1

[jt643]

I am having a tough time figuring out the correct syntax to pull back all records from a table that have a yes/no box checked on a table.

I thought the following would work:

Set SelTotalTableDb = CurrentDb
Set SelTotalTableRst = SelTotalTableDb.OpenRecordset("select * from COSelTotalTable WHERE Contract = 'Yes'")

I also tried:

stSQLWhere = "Yes"

Set SelTotalTableDb = CurrentDb
Set SelTotalTableRst = SelTotalTableDb.OpenRecordset("select * from COSelTotalTable WHERE Contract = stSQLWhere")

I keep getting the following error message:

Run-Tim Error '3061': Too few parameters. Expected 1
*It is 'Expected 2' when I try the string syntax above.

Thanks in advance!

 

[GingerR]

For a Yes/No data type,

Checked = Yes = -1 = True
Unchecked = No = 0 = False

Yes and No above are not in quotation marks. When you do that, it's evaluated as a string.

Your options would be:

"...WHERE Contract = True"
"...WHERE Contract = Yes"
"...WHERE Contract = -1"

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jt643]

Thanks GingerR!

It is usually something simple like that, but that made all the difference in the world.

 

[jt643]

Yesterday, I was able to get the SQL to work making the above suggested corrections.

However, today, I am getting errors with the following VB:

Set SelTotalTableDb = CurrentDb
Set SelTotalTableRst = SelTotalTableDb.OpenRecordset("select * from COSelTotalTable WHERE Subdivision = '" & TempRst!Subdivision & "' AND Category = '" & Me.Category & "' AND Order = True")

I know that there should be records as I ran a query with similar SQL, and it pulled back 2 records.

The specific error message I am getting is:

Run-time error '3075':

Syntax error (missing operator) in query expression 'Subdivision = 'Test-71205' AND Category = 'ELECTRICAL' AND'.

I have tried various other combinations - including where I just tried to pull all records from COSelTotalTable where the Order check box is selected. I can't get that to work with an error that my WHERE syntax is incorrect.

...WHERE Order = True"

Thanks in advance.

 

[GingerR]

Try putting square brakets around "ORDER". Maybe it's a system keyword and it's interpreting it incorrectly.

If it still doesn't work, does it work without the "ORDER = " clause?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[jt643]

That was perfect! Thanks for your help.

 

[abaldwin]

FYI

Some very of the very basic SQL and VBA reserved words that should NEVER be used as field names.


Select
From
Where
Order
By
Group
Having
sum
date
now

I am not trying to sound anti-social or anything but one of the biggest mistakes I see made during database developement besides the basic structure, is the naming conventions used. Since the end user should never see the field names it is not important to make them "Reader friendly". Instead of a field called Order it should really be called "SortOrder" .

Leave out the spaces too. SQL does not like that at all.

I am in no way trying to flame you just pointing out a few basics that seem to pop up in database design quite a bit lately.

Andy



Andy Baldwin

"Testing is the most overlooked programming language on the books!