December 3, 2007 at 4:24 pm
How do I use a where clause when the field is a yes/no check box
when I use
Select *
from (table)
Where field = "yes"
I get an error: Data Type Mismatch in criteria expression.
December 3, 2007 at 6:07 pm
try....
Select *
from (table)
Where field = 1
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
December 3, 2007 at 7:54 pm
if it's access....try -1 instead.
On SQL Server true=1 false=0
Access, true=-1, false=0
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 3, 2007 at 9:08 pm
thanks for the help.. but I researched...
and the value for Yes is "-1" and the value for No is "0"
I substituted the above values and the query ran fine..
Thanks for thew help anyways...
December 5, 2007 at 5:06 am
Or cleaner, since MS Access recognizes Booleans (unlike SQL Server), simply say "where field". (Where takes a Boolean, and field (displayed with check boxes) is a Boolean.)
From a checkbox named "On" in a form name "form1", you can do something like:
SELECT [aTbl].[Name], [aTbl].HasReps
FROM [aTbl]
WHERE ((([aTbl].HasReps)=[forms]![form1]![On]));
December 6, 2007 at 6:04 am
You can also use WHERE Field = True but in any event, beware of nulls - if your checkbox is tri-state then your results may be incorrect.
December 6, 2007 at 6:50 am
You can say field = true, but it is redundant. Field is either true, false, or null; in which case a test against true simply returns the value of the field. Testing a Boolean flag with if flag = true accomplishes nothing. (But, for languages like T-SQL, which do not recognize Booleans, you do need to test flag = -1.)
BTW, if the original poster to know how Access represents true, simply put " Expr1: True " into a new query and show the result.
December 6, 2007 at 7:42 am
That is not redundant. I agree that the shortcut method works in dynamic SQL strings, but it is not a best practice. To see what I mean, create a query in the SQL window like this:
SELECT SomeTextField, SomeBooleanField
FROM SomeTable WHERE SomeBooleanField;
and then switch into Design View - observe that Jet has added <>False to the Criteria row under the SomeBooleanField. Now, is that what you meant? Even if you save the query, Jet will continue to display the different WHERE expression when switching between SQL and Design views.
December 6, 2007 at 7:48 am
WILLIAM MITCHELL (12/6/2007)
You can also use WHERE Field = True but in any event, beware of nulls - if your checkbox is tri-state then your results may be incorrect.
Don't forget that (at least the older versions of) Access doesn't allow for Booleans to be null. Booleans are two-states only. If you create a nullable boolean field in SQL Server in a table, and link that table in, then that table will go read-only the minute you insert a record with a null value in the boolean field. You also get some really "interesting" error if you should try (red herring of some kind).
I haven't had the pleasure of playing in Acc2007 to see if that has changed, but that is certainly true in Acc2003. (not talking ADP/ADE - "native" Access).
That's one of those ugly gotchas.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 11:39 am
Interesting. In fact, in Access 2003 this query (where [isDate] is a check box field):
SELECT [Elems - CPMIS].Name, [Elems - CPMIS].Typ
FROM [Elems - CPMIS]
WHERE IsDate;
works fine.
But the Designer view does add a column for IsDate (with the show checkbox cleared) and a <>False condtion. (The SQL view is unchanged, but I expect if I changed anything in the Designer view, the <>False test would, as you suggest, get into the query.)
A checkbox on a form can be set to be triple state, and I thought I recalled that was available in a table also. But I can't recall where I found that, or remember how to do it. You may be right.
As a programming generalization, whenever I see a Boolean flag used in some test like "if flag = true..." I suspect it was written by a junior programmer.
December 6, 2007 at 1:04 pm
I'm curious about your programming generalization. Is there another language that allows you to use the shortcut method, other than Access and classic VB or VBA? For examples...
T-SQL: CASE BooleanField WHEN 1 THEN 'True' ELSE 'False' END
C#: if (ObjectName.ToString == "True") {
dBase: IF FieldName = .T.
or am I missing something.
December 6, 2007 at 1:52 pm
Jim Russell (12/6/2007)
Interesting. In fact, in Access 2003 this query (where [isDate] is a check box field):SELECT [Elems - CPMIS].Name, [Elems - CPMIS].Typ
FROM [Elems - CPMIS]
WHERE IsDate;
works fine.
But the Designer view does add a column for IsDate (with the show checkbox cleared) and a <>False condtion. (The SQL view is unchanged, but I expect if I changed anything in the Designer view, the <>False test would, as you suggest, get into the query.)
A checkbox on a form can be set to be triple state, and I thought I recalled that was available in a table also. But I can't recall where I found that, or remember how to do it. You may be right.
As a programming generalization, whenever I see a Boolean flag used in some test like "if flag = true..." I suspect it was written by a junior programmer.
Unbound controls can be tri-state.
Boolean Data fields can be defined with NULL as the default value, but when you insert a row, the NULL will be converted to false (=0).
There are "features" in just about every implementation of DAO and ADO within access making it important to specify long hand for boolean values (i.e. [boolean expression]=TRUE|FALSE), in particular in the VBA code, since you would end up with inconsistent results, expecially when IT ran into NULL values in its variables.
In Access VBA - NOT finding specific value comparisons is usually a sign of someone not familiar with the syntax, because they haven't yet been bitten with the boolean math bug....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 7, 2007 at 4:50 am
While I would hardly consider omitting the "=true" a shortcut, perhaps I can be accused of over generalizing. T-SQL is the first language that I recall that did not allow a Boolean result value in a conditional expression. On the other hand, my recollections have a half life measured in weeks nowadays. Since I don't have current access to all the languages I have programmed in (APL, J, Forth, COBOL, PERL, AWK, Java, Javascript, assembly language(s), EDL, PowerBuilder, KSH, C, etc.), I can't test my assertion. I'll remember to suspend judgment until I make sure the "inexperienced programmer" has no reason to test his Boolean against "True".
I was able to locate a very interesting Blog entry I had enjoyed a while back related to the subject:
http://drj11.wordpress.com/2007/05/25/iversons-convention-or-what-is-the-value-of-x-y/
As always, there is more to it than meets the eye.
December 7, 2007 at 6:35 pm
Matt, as far as Access 2007, you can still create a table with a Yes/No field that does not have a default value, but as you said when you create a new record that field will default to No.
An unbound checkbox can still be tri-state in 2007.
But there is so much not to like about 2007. Instead of the database window, there is a navigation pane (or should I say pain) at the left side that presents one long list of all your objects - you can collapse each object type, but if you have 100's of queries or whatever you have to scroll down that long list to find something. There is no more multi-column display -- if you select the Details view, the created & modified dates appear just below the object names.
The Access ribbon is really lame. I've played around with 2007 for a couple months, but I just started trying to do some actual design work and it feels like playing the piano wearing boxing gloves - nothing seems to work the same, hardly anything is where you expect to find it.
Finally, if you stay with the 2000-2002 .mdb format, 2007 still supports workgroup security and replication, but if you convert to the new 2007 .accdb format those features are not available.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply