unable to pass an "OR" statement to a report from a Form

B

bond007taz

I am not sure if i am going the correct route, but I have a form that
creates a text string based upon what the user selects so that it
opens a report. The issue is that I have 4 checkboxes in the table
that the report is reporting on and I need to be able to allow the
user to select a combination of the checkboxes so that the report
shows all records that have those combination of checkboxes.

I have this string that works and it passes the needed values to a
report, this works fine:

[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1

the string above works just fine and it shows all the records that
meet the above criteria AND if checkbox 1 is checked.

Ok, so if the user selects CHECKBOX2 then the string changes to:
[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1AND[CHECKBOX2]=-1

The problem with above string is that is shows only the records that
have checkbox1 and checkbox2 selected but I want it to show all the
records that may only have checkbox1 selected but not checkbox2 AND
show all the records that may have checkbox2 selected but not
checkbox1 - make sense? I thought an "OR" statement would work but
apparently not.

so I tried creating an OR statement that looks like this:

[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1OR[CHECKBOX2]=-1

The issue with the above string is that it shows all the records up to
the OR command then shows all the records that have checkbox2 selected
but it no longer takes into account the first few fields, like GFY,
BUSINESS_UNIT and ACCTs

I then tried to create a statement like the one below:
[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1AND[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX2]=-1

notice the above string is the same string put together with AND but
one has CHECKBOX1 and the 2nd has CHECKBOX2 but this string errors out
and does not work. I tried putting in "(" at the beginning and ")" at
the end with no sucess.

argh, the end seems so close!!!

Is it possible to do what I want?

If so, what am I missing?
 
B

bond007taz

I am not sure if i am going the correct route, but I have a form that
creates a text string based upon what the user selects so that it
opens a report. The issue is that I have 4 checkboxes in the table
that the report is reporting on and I need to be able to allow the
user to select a combination of the checkboxes so that the report
shows all records that have those combination of checkboxes.

I have this string that works and it passes the needed values to a
report, this works fine:

[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1

the string above works just fine and it shows all the records that
meet the above criteria AND if checkbox 1 is checked.

Ok, so if the user selects CHECKBOX2 then the string changes to:
[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1AND[CHEC­KBOX2]=-1

The problem with above string is that is shows only the records that
have checkbox1 and checkbox2 selected but I want it to show all the
records that may only have checkbox1 selected but not checkbox2 AND
show all the records that may have checkbox2 selected but not
checkbox1 - make sense? I thought an "OR" statement would work but
apparently not.

so I tried creating an OR statement that looks like this:

[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1OR[CHECK­BOX2]=-1

The issue with the above string is that it shows all the records up to
the OR command then shows all the records that have checkbox2 selected
but it no longer takes into account the first few fields, like GFY,
BUSINESS_UNIT and ACCTs

I then tried to create a statement like the one below:
[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1AND[GFY]­=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX2]=-1

notice the above string is the same string put together with AND but
one has CHECKBOX1 and the 2nd has CHECKBOX2 but this string errors out
and does not work. I tried putting in "(" at the beginning and ")" at
the end with no sucess.

argh, the end seems so close!!!

Is it possible to do what I want?

If so, what am I missing?
Click to expand...

found the issue, it was syntax error

[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1OR[GFY]­
=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX2]=-1
 
M

mcescher

Hmmm.  I don't know why your string errored out.  This *should* work:
([GFY]=8 AND [BUSINESS_UNIT]='FEDEX' AND [ACCTs]='01AA' AND [CHECKBOX1]=-1)
OR ([GFY]=8 AND [BUSINESS_UNIT]='FEDEX' AND [ACCTs]='01AA' AND
[CHECKBOX2]=-1)

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L




I am not sure if i am going the correct route, but I have a form that
creates a text string based upon what the user selects so that it
opens a report. The issue is that I have 4 checkboxes in the table
that the report is reporting on and I need to be able to allow the
user to select a combination of the checkboxes so that the report
shows all records that have those combination of checkboxes.
Click to expand...
I have this string that works and it passes the needed values to a
report, this works fine:
[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1
Click to expand...

the string above works just fine and it shows all the records that
meet the above criteria AND if checkbox 1 is checked.
Click to expand...
Ok, so if the user selects CHECKBOX2 then the string changes to:
[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1AND[CHEC­KBOX2]=-1
Click to expand...
The problem with above string is that is shows only the records that
have checkbox1 and checkbox2 selected but I want it to show all the
records that may only have checkbox1 selected but not checkbox2 AND
show all the records that may have checkbox2 selected but not
checkbox1 - make sense? I thought an "OR" statement would work but
apparently not.
Click to expand...
so I tried creating an OR statement that looks like this:
[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1OR[CHECK­BOX2]=-1
Click to expand...

The issue with the above string is that it shows all the records up to
the OR command then shows all the records that have checkbox2 selected
but it no longer takes into account the first few fields, like GFY,
BUSINESS_UNIT and ACCTs
Click to expand...
I then tried to create a statement like the one below:
[GFY]=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX1]=-1AND[GFY]­=8AND[BUSINESS_UNIT]='FEDEX'AND[ACCTs]='01AA'AND[CHECKBOX2]=-1
Click to expand...
notice the above string is the same string put together with AND but
one has CHECKBOX1 and the 2nd has CHECKBOX2 but this string errors out
and does not work. I tried putting in "(" at the beginning and ")" at
the end with no sucess.
Click to expand...
argh, the end seems so close!!!
Click to expand...
Is it possible to do what I want?
Click to expand...
If so, what am I missing?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Single out the OR part of the statement with parenthesis.

[GFY]=8 AND [BUSINESS_UNIT]='FEDEX' AND [ACCTs]='01AA' AND
([CHECKBOX1]=-1 OR [CHECKBOX2]=-1)

If you have four check boxes, you can fit all four inside the
parenthesis.

[GFY]=8 AND [BUSINESS_UNIT]='FEDEX' AND [ACCTs]='01AA' AND
([CHECKBOX1]=-1 OR [CHECKBOX2]=-1 OR [CHECKBOX3]=-1 OR [CHECKBOX4]=-1)

Hope this helps,
Chris M
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Setting Checkboxes 2
Incone & Expenses Statement 0
if statement in an if statement 7
Cumulative formula for word table 0
Looping through checkboxes 1
VBA set range according to find 0
Form Checkbox 2
Checkbox help 1

Top