using boolean as criteria in SQL

**pietlinden@hotmail.com** · May 10 '06, 08:05 PM

Re: using boolean as criteria in SQL

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

**salad** · May 11 '06, 04:55 AM

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.

**Daron** · May 11 '06, 01:05 PM

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_CurrentFiel ds].[Column](1)") AS
[Current Field], quni_Master.STU _DISTRICT_CODE,
quni_Master.STU _STUDENT_ID,
Eval("[Forms]![frm_Setup]![lbo_CurrentFiel ds].[Column](2)") AS ErrWarn,

Eval("[Forms]![frm_Setup]![lbo_CurrentFiel ds].[Column](0)") AS
TestField,
quni_Master.STU _DATE_OF_ENTRY_ GRADE_9 AS [Current Value],
[Forms]![frm_Setup]![sub_FieldValida tion].[Form]![mem_Error] AS
strReason,
fun_CheckDateFo rmat([STU_DATE_OF_ENT RY_GRADE_9])
FROM quni_Master
WHERE (((quni_Master. STU_DATE_OF_ENT RY_GRADE_9) Is Not Null) AND
((fun_CheckDate Format([STU_DATE_OF_ENT RY_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_CheckDateFo rmat(), 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"?

**Daron** · May 11 '06, 01:05 PM

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_CurrentFiel ds].[Column](1)") AS
[Current Field], quni_Master.STU _DISTRICT_CODE,
quni_Master.STU _STUDENT_ID,
Eval("[Forms]![frm_Setup]![lbo_CurrentFiel ds].[Column](2)") AS ErrWarn,
Eval("[Forms]![frm_Setup]![lbo_CurrentFiel ds].[Column](0)") AS
TestField,
quni_Master.STU _DATE_OF_ENTRY_ GRADE_9 AS [Current Value],
[Forms]![frm_Setup]![sub_FieldValida tion].[Form]![mem_Error] AS
strReason,
fun_CheckDateFo rmat([STU_DATE_OF_ENT RY_GRADE_9])
FROM quni_Master
WHERE (((quni_Master. STU_DATE_OF_ENT RY_GRADE_9) Is Not Null) AND
((fun_CheckDate Format([STU_DATE_OF_ENT RY_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_CheckDateFo rmat(), 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"?

**salad** · May 11 '06, 05:05 PM

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_CurrentFiel ds].[Column](1)") AS
> [Current Field], quni_Master.STU _DISTRICT_CODE,
> quni_Master.STU _STUDENT_ID,
> Eval("[Forms]![frm_Setup]![lbo_CurrentFiel ds].[Column](2)") AS ErrWarn,
>
> Eval("[Forms]![frm_Setup]![lbo_CurrentFiel ds].[Column](0)") AS
> TestField,
> quni_Master.STU _DATE_OF_ENTRY_ GRADE_9 AS [Current Value],
> [Forms]![frm_Setup]![sub_FieldValida tion].[Form]![mem_Error] AS
> strReason,
> fun_CheckDateFo rmat([STU_DATE_OF_ENT RY_GRADE_9])
> FROM quni_Master
> WHERE (((quni_Master. STU_DATE_OF_ENT RY_GRADE_9) Is Not Null) AND
> ((fun_CheckDate Format([STU_DATE_OF_ENT RY_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_CheckDateFo rmat(), 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_CheckDateFo rmat(varDate As Variant) As Boolean

What happens if you did
WHERE fun_CheckDateFo rmat([STU_DATE_OF_ENT RY_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.

**Daron** · May 11 '06, 07:15 PM

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_CheckDateFo rmat() function. Any ideas as to why I was getting the
error?

- Daron

**Bob Quintal** · May 11 '06, 10:15 PM

Re: using boolean as criteria in SQL

"Daron" <Daron.Lowell@g mail.com> wrote in
news:1147374632 .676782.303830@ y43g2000cwc.goo glegroups.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_CheckDateFo rmat() function. Any ideas as to why
> I was getting the error?
>
> - Daron
>[/color]

WHERE (((quni_Master. STU_DATE_OF_ENT RY_GRADE_9) Is Not Null) AND
((fun_CheckDate Format([STU_DATE_OF_ENT RY_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.

using boolean as criteria in SQL

using boolean as criteria in SQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment