Expression using AND, OR 2

[jgeneie]

hi all,

i have a problem displaying my results.

i have created a table with 3 fields: "customername","dealers" and "ISP"

-customername will store names of customer( obviously )
- ISP and dealers data type are just yes/no

lets say i have created a OR expression between "dealers" and "ISP". when i select "dealers" and "ISP".

customername dealers ISP
------------ ------- ---
bob yes yes
cat yes
dog yes

but all i want is BOB's name to appear.

But lets say i use AND expression instead and select the same 2 fields: dealers and ISP

customername dealers ISP
------------ ------- ---
bob yes yes

that will satify my criteria but lets say i only select "dealers", "bob" name will not appear but instead only
"dog" will appear.

thats not i expect, i expect "dog" and "Bob" to appear.

how do i solve this problem using expressions?

 

[JimHorton]

jgenie,
If you have as criteria "Where Dealers = True", then Bob and Dog should appear, but it looked like you had "Where Dealers = True AND ISP = True", in which case, only Bob should appear.

What is the exact sql?
--Jim

 

[jgeneie]

hi,
i am sorry but i am not good at this SQL thing.... i am using expression builder to define my query

As far as i know.... if use the "AND" compound and i select where "dealer" = true, only "bob" will appear. dog will go missing
becoz the above does not satify the expression: dealer "AND" ISP

maybe u can help me in the expression
thanks

 

[JimHorton]

jgeneie,
When the expression says 'AND ISP', it really means "AND ISP = TRUE".

This is not obvious, and I'm not sure if it's documented, but saying "IF" and then any expression, means the same as:
IF (expression) = True. Also "True" means "Not Zero" in most cases, so if you had a statment:

If 5 then
Msgbox "True"
Else
Msgbox "False"
End if

The msgbox would pop up saying "True". Technically, in Access, True is = -1, and False = 0, but any Non-Zero value will return True in expressions as above,
BUT...
in a query on a Yes/No field, you couldn't say "Where ISP = 5", and expect all the Yes's (the True's) to be returned, but if you had a Numeric field, and said "Where OrderNumber = True", then ALL order number that were not zero would be returned.

Bottom line--remove the "ISP" from the expression.
--Jim

 

[jgeneie]

hi, i have input the query in the query builder using expression builder

Name
----
Just to display names

dealers
------------
In row "criteria": [Forms]![frmcriteria]![chkdealers]

ISP
-------
In row "criteria": [Forms]![frmcriteria]![chkISP]

hope its not too confusing. all this is written in the query builder, using expression builder

so actually the 2 expressions are connected with an "AND" compound automatically rt??

in this case only names with "dealers" and "ISP" will be displayed but not the ones only with "dealers" by itself. rt???

so how can i change from here so that both BOB and DOG will display when i select only "dealers"


I am thinking about "UNION" in mathematics
hope u know what i mean

thanks

 

[JimHorton]

To get the behaviour you're looking for, you need to remove the chkISP from the criteria, since it's wanting the ISP to be False.

But you need to tell it when you want to use chkISP and when you don't--it's doing what you say, since you've told it to show where Dealers is True and ISP is False--the unchecked ISP did not tell it to ignore this field, but to use the value of 'unchecked', which is False/No.

You could add another check field, that says "use this", or somtheing, so when you check this field, it considers the checkbox, when you uncheck it, it doesn't:

[Forms]![frmcriteria]![chkISP] AND( [Forms]![frmcriteria]![chkUSE_ISP] = True)

--Jim

 

[jgeneie]

hi,
sorrie, but is there any other way out, becoz my form doesnt only contain 2 checkboxes. it would look weird if it has another chkbox that serve the same purpose (to a newuser)

thanks

 

[JimHorton]

jgeneie,
Set the checkbox to TripleState, and then the user can select the Null value of the checkbox (the greyed-out state). Then in the criteria:

[Forms]![frmcriteria]![chkISP] AND(isnull([Forms]![frmcriteria]![chkISP]) = False)

 

[jgeneie]

hi,
i have set the query correctly using a few chkboxes initially for testing. it work prefectly well!!

until i put in more chkboxes which mean my query became longer i got a msg box: the string returned by the builder was too long " the result will be truncated"

so what can i do to savage this

 

[JimHorton]

jgeneie,
I've never used the Expression Builder, so I don't know. The best bet is to do this in the normal query grid, using either the standard Design view, or the SQL view. Then the limit is, I believe, 64K or so.
--Jim

 

[jgeneie]

hi,
are u trying to tell me write the SQL in the sql view instead??
but what i create in the expression builder will be reflected in the SQL view. so what is the difference??

or do i have to write the SQL language itself??
any other way out

thanks

 

[JimHorton]

Jgeneie,
You don't have to write raw sql. If you're in the normal design view, you can have all of the Field's that have a corrensponding checkbox in the grid. Then under each one, you can type the:

[Forms]![frmcriteria]![chkISP] AND(isnull([Forms]![frmcriteria]![chkISP]) = False)
...under each corresponing field...changing the actual control name, ie. change each chkISP to chkDealer, etc. You could copy/paste the same expression into all the fields and then change it.

...however, I think the below will work better:
[Forms]![frmcriteria]![chkISP] or isnull([Forms]![frmcriteria]![chkISP])

This syntax says that if the checkbox is in the greyed out state, ignore the criteria, and select the record as if there were no criteria.
--Jim

 

[jgeneie]

hi,
but thats what i did in the first place.
the error says that my result will be truncated, when i save it.

help pls
thanks

 

[JimHorton]

jgeneie,
Something is not right here. Just for curiosity, I created a query, with 2 tables af 80 or so fields each, joined on multiple fields, and put the exact as above criteria in 25 fields, even put other criteria in 2 levels of the OR rows, and it saved just fine. The SQL view would paste into wordpad and create several pages of sql.

You need to make sure you're not using the 'expression builder', and are just typing into the criteria row of the query--one set of criteria per column, and check the parenthesis, and quotes, and for other criteria and make sure it's where it should be.

I have never seen this message you're getting, but as I said, I've never used the expression builder so I'm thinking it may have something to do with that. If you still get this, could you post the SQL here? To do that, just go to the query view button and choose the SQL option.
--Jim

 

[jgeneie]

hi,
how do i attach the file to u??

the query is pretty long

 

[jgeneie]

hi jim
do u have a email address so i can attach the query to u??
thanks

 

[JimHorton]

jgeneie,
You can send to:
jimojimo@hotmail.com
--Jim

 

[jgeneie]

hi jim,
i have sent the query to your email.

thanks alot

 

[jgeneie]

hi jim,
have u received my query??

regards

 

[JimHorton]

jgeneie,
Yes, I've sent 2 replys. One had a version of the query that worked, but you couldn't open in design view, tho everything worked fine. The other was a more complete solution, building the sql in a form. If you don't have it by now, I'll resend tonight (it's on my home machine).
--Jim