using boolean as criteria in SQL

In my SQL I have a column that returns a boolean value (-1 or 0), no
problem.

I need to be able to only see the records that return a 0 (False). I
can sort and bring these to the top.

When I attempt to create a criteria on the QBE or filter (right click
menu), I am confronted with a ""This expression is typed incorrectly,
or is too complex to be evaluated"" error.

I have tried filtering on "0", "False", "Not True", "<>-1", ".-1", all
with the same error.

What am I doing wrong?

- Daron

re: using boolean as criteria in SQL

you should be able to use True or False (without quotes).

re: using boolean as criteria in SQL

Daron wrote:
[color=blue]
> In my SQL I have a column that returns a boolean value (-1 or 0), no
> problem.
>
> I need to be able to only see the records that return a 0 (False). I
> can sort and bring these to the top.
>
> When I attempt to create a criteria on the QBE or filter (right click
> menu), I am confronted with a ""This expression is typed incorrectly,
> or is too complex to be evaluated"" error.
>
> I have tried filtering on "0", "False", "Not True", "<>-1", ".-1", all
> with the same error.
>
> What am I doing wrong?
>
> - Daron
>[/color]
May we assume you aren't entering the values like "False" with quotes?

In the table, is the field Yes/No? Is it numeric? Is it a returned
value from a function? Are you linking multiple tables in the query?
If your query isn't too complex (iow lengthy with lots of joins), you
might want to post it here.

If this is coming from multiple tables, create a query with just that
table, a couple of fields and the true/false field and filter on it.
Does that work OK?


This is from help
(Error 3071)
Possible causes:

· You typed an expression that has invalid syntax. For example, an
operand or operator may be missing, you may have typed an invalid
character or comma, or you may have typed text without enclosing it with
quotation marks ( " ). Check the expression to make sure you typed it
correctly.
· You typed an expression that is too complex. For example, a numeric
expression may contain too many complicated elements. Try simplifying
the expression by assigning some parts to variables.


From the above, I suggest we are missing some information to help you.

re: using boolean as criteria in SQL

Thanks for taking the time to help!

Lets start with the sQL, and go from there:

SELECT Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](1)") AS
[Current Field], quni_Master.STU_DISTRICT_CODE,
quni_Master.STU_STUDENT_ID,
Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](2)") AS ErrWarn,

Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](0)") AS
TestField,
quni_Master.STU_DATE_OF_ENTRY_GRADE_9 AS [Current Value],
[Forms]![frm_Setup]![sub_FieldValidation].[Form]![mem_Error] AS
strReason,
fun_CheckDateFormat([STU_DATE_OF_ENTRY_GRADE_9])
FROM quni_Master
WHERE (((quni_Master.STU_DATE_OF_ENTRY_GRADE_9) Is Not Null) AND
((fun_CheckDateFormat([STU_DATE_OF_ENTRY_GRADE_9]))=False));

(Additional info: The quotes where included in the original post only
for this message, not used in the SQL. The final SQL that is run has an
INSERT clause to create an error report.)

What I am doing is using Access to validate text files before they are
passed on to an Oracle data warehouse. All fields are being treated as
text for validation purposes.

In these files, the date fields must have a specific format
(YYYY-MM-DD), so I have a function, fun_CheckDateFormat(), that checks
this format, and returns a boolean. True is a correct format and a
valid date, False is not.

The query works fine without the final WHERE clause to show only False
responses. I can see the column listing 0's and -1's. As soon as I
include that criteria, I get the error message "This expression is
typed incorrectly, or is too complex to be evaluated".

As you can see, this is a fairly straight forward SQL.

The question I have is: how do show only the False records? How does
this one additional test make this expression "too complex"?

re: using boolean as criteria in SQL

Thanks for taking the time to help!

Lets start with the SQL, and go from there:

SELECT Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](1)") AS
[Current Field], quni_Master.STU_DISTRICT_CODE,
quni_Master.STU_STUDENT_ID,
Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](2)") AS ErrWarn,
Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](0)") AS
TestField,
quni_Master.STU_DATE_OF_ENTRY_GRADE_9 AS [Current Value],
[Forms]![frm_Setup]![sub_FieldValidation].[Form]![mem_Error] AS
strReason,
fun_CheckDateFormat([STU_DATE_OF_ENTRY_GRADE_9])
FROM quni_Master
WHERE (((quni_Master.STU_DATE_OF_ENTRY_GRADE_9) Is Not Null) AND
((fun_CheckDateFormat([STU_DATE_OF_ENTRY_GRADE_9]))=False));

(Additional info: The quotes where included in the original post only
for this message, not used in the SQL. The final SQL that is run has an
INSERT clause to create an error report.)

What I am doing is using Access to validate text files before they are
passed on to an Oracle data warehouse. All fields are being treated as
text for validation purposes.

In these files, the date fields must have a specific format
(YYYY-MM-DD), so I have a function, fun_CheckDateFormat(), that checks
this format, and returns a boolean. True is a correct format and a
valid date, False is not.

The query works fine without the final WHERE clause to show only False
responses. I can see the column listing 0's and -1's. As soon as I
include that criteria, I get the error message "This expression is
typed incorrectly, or is too complex to be evaluated".

As you can see, this is a fairly straight forward SQL.

The question I have is: how do show only the False records? How does
this one additional test make this expression "too complex"?

re: using boolean as criteria in SQL

Daron wrote:[color=blue]
> Thanks for taking the time to help!
>
> Lets start with the sQL, and go from there:
>
> SELECT Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](1)") AS
> [Current Field], quni_Master.STU_DISTRICT_CODE,
> quni_Master.STU_STUDENT_ID,
> Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](2)") AS ErrWarn,
>
> Eval("[Forms]![frm_Setup]![lbo_CurrentFields].[Column](0)") AS
> TestField,
> quni_Master.STU_DATE_OF_ENTRY_GRADE_9 AS [Current Value],
> [Forms]![frm_Setup]![sub_FieldValidation].[Form]![mem_Error] AS
> strReason,
> fun_CheckDateFormat([STU_DATE_OF_ENTRY_GRADE_9])
> FROM quni_Master
> WHERE (((quni_Master.STU_DATE_OF_ENTRY_GRADE_9) Is Not Null) AND
> ((fun_CheckDateFormat([STU_DATE_OF_ENTRY_GRADE_9]))=False));
>
> (Additional info: The quotes where included in the original post only
> for this message, not used in the SQL. The final SQL that is run has an
> INSERT clause to create an error report.)
>
> What I am doing is using Access to validate text files before they are
> passed on to an Oracle data warehouse. All fields are being treated as
> text for validation purposes.
>
> In these files, the date fields must have a specific format
> (YYYY-MM-DD), so I have a function, fun_CheckDateFormat(), that checks
> this format, and returns a boolean. True is a correct format and a
> valid date, False is not.
>
> The query works fine without the final WHERE clause to show only False
> responses. I can see the column listing 0's and -1's. As soon as I
> include that criteria, I get the error message "This expression is
> typed incorrectly, or is too complex to be evaluated".
>
> As you can see, this is a fairly straight forward SQL.
>
> The question I have is: how do show only the False records? How does
> this one additional test make this expression "too complex"?
>[/color]
It's hard to say without seeing your function. When you pass the date
to the function, does the function take the date as a variant? It
should, since the date field could be null.

Public Function fun_CheckDateFormat(varDate As Variant) As Boolean

What happens if you did
WHERE fun_CheckDateFormat([STU_DATE_OF_ENTRY_GRADE_9])=False;
instead?

You will have to check for the date being null in your function.

I hope it's as simple as that.

re: using boolean as criteria in SQL

Salad,

Your my saviour! (Not really, you can't replace Christ :) )

My function was taking the date as a string, not a variant. Changing it
as you suggested, plus explicitly testing for Null and setting the
return as False for the null values did the trick.

Now my curiosity is piqued. In my example I was filtering out the
records with a Null value, so these should never have been sent to my
fun_CheckDateFormat() function. Any ideas as to why I was getting the
error?

- Daron

re: using boolean as criteria in SQL

"Daron" <Daron.Lowell@gmail.com> wrote in
news:1147374632.676782.303830@y43g2000cwc.googlegr oups.com:
[color=blue]
> Salad,
>
> Your my saviour! (Not really, you can't replace Christ :) )
>
> My function was taking the date as a string, not a variant.
> Changing it as you suggested, plus explicitly testing for Null
> and setting the return as False for the null values did the
> trick.
>
> Now my curiosity is piqued. In my example I was filtering out
> the records with a Null value, so these should never have been
> sent to my fun_CheckDateFormat() function. Any ideas as to why
> I was getting the error?
>
> - Daron
>[/color]

WHERE (((quni_Master.STU_DATE_OF_ENTRY_GRADE_9) Is Not Null) AND
((fun_CheckDateFormat([STU_DATE_OF_ENTRY_GRADE_9]))=False));

evaluates to Where True AND #ERROR, so throws up the message you
reported, which is not at all helpful.

SQL needs to evaluate both conditions. It has no way of knowing
that the second condition is dependent on the first. You could
write something like

WHERE firstname is not null And isMale = false

in which the two parts are independent of each other.

With your modified function you can drop the first part of your
where clause

--
Bob Quintal

PA is y I've altered my email address.