Using 'OR' for criteria

[Paul B.]

Can someone advise how best to create a link criteria that would allow a form
to display records that match several criteria?

I want to search my tblVehicles for VehLocation that match a numeric value
of 241 or 242 or 244 or 261 or 262.

Thanks in advance

 

[Sandra Daigle]

Are you opening this form from another form using the WhereCondition
parameter? You would either build a complex criteria statement or use the
In() criteria. Examples of both are shown below:

DoCmd.OpenForm "frmVehicles", , , "VehLocation=241 or VehLocation=242 or
VehLocation=244 or VehLocation=261 or VehLocation=262"
DoCmd.OpenForm "frmVehicles", , , "Vehlocation in(241,242,244,261,262)"

The above examples use hardcoded lists of values, typically you would be
getting the test values from a listbox or some other way. Postback if you
need help with this.

 

[fredg]

Can someone advise how best to create a link criteria that would allow a form
to display records that match several criteria?

I want to search my tblVehicles for VehLocation that match a numeric value
of 241 or 242 or 244 or 261 or 262.

Thanks in advance

A "link Criteria"? Is this being done in a form event or directly in a
query?

Directly in a query:

Assuming VehLocation datatype is Number:
Select * From Your Table
Where tblVehicles.VehLocation = 241 OR tblVehicles.VehLocation = 242
Or tblVehicles.VehLocation = 244 or etc.. ;

If you have a longer consecutive run of numbers you can use:

Where tblVehicles.VehLocation Between 200 and 210

In a form event:

stLinkCriteria = "VehLocation = 241 OR VehLocation = 242 Or
VehLocation = 244 or etc.. "

DoCmd.openForm "FormName", , , stLinkCriteria

 

[Paul B.]

Thanks for the help with the IN ( ) criteria. I had not come accross that one
yet.

However, now I am getting an error, when I click on the button to search, I
get 'The OpenForm action was canceled' . If I take out the criteria, the form
opens and shows all records, but using both of your suggestions, generates
the same error. The code is exactly as you suggested, double checked in case
my fat thumbs got in the way....

Are you opening this form from another form using the WhereCondition
parameter? You would either build a complex criteria statement or use the
In() criteria. Examples of both are shown below:

DoCmd.OpenForm "frmVehicles", , , "VehLocation=241 or VehLocation=242 or
VehLocation=244 or VehLocation=261 or VehLocation=262"
DoCmd.OpenForm "frmVehicles", , , "Vehlocation in(241,242,244,261,262)"

The above examples use hardcoded lists of values, typically you would be
getting the test values from a listbox or some other way. Postback if you
need help with this.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Can someone advise how best to create a link criteria that would
allow a form to display records that match several criteria?

I want to search my tblVehicles for VehLocation that match a numeric
value of 241 or 242 or 244 or 261 or 262.

Thanks in advance

 

[Sandra Daigle]

Hi Paul,

Please post your code - I'm not sure what might be causing that error. Also,
please indicate which line is highlighted when the error occurs when you go
into debug mode.

One other thing, does the code compile?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for the help with the IN ( ) criteria. I had not come accross
that one yet.

However, now I am getting an error, when I click on the button to
search, I get 'The OpenForm action was canceled' . If I take out the
criteria, the form opens and shows all records, but using both of
your suggestions, generates the same error. The code is exactly as
you suggested, double checked in case my fat thumbs got in the way....

Are you opening this form from another form using the WhereCondition
parameter? You would either build a complex criteria statement or
use the In() criteria. Examples of both are shown below:

DoCmd.OpenForm "frmVehicles", , , "VehLocation=241 or
VehLocation=242 or VehLocation=244 or VehLocation=261 or
VehLocation=262"
DoCmd.OpenForm "frmVehicles", , , "Vehlocation
in(241,242,244,261,262)"

The above examples use hardcoded lists of values, typically you
would be getting the test values from a listbox or some other way.
Postback if you need help with this.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Can someone advise how best to create a link criteria that would
allow a form to display records that match several criteria?

I want to search my tblVehicles for VehLocation that match a numeric
value of 241 or 242 or 244 or 261 or 262.

Thanks in advance

 

[Paul B.]

Hi Sandra,

Ignore the last part, stupid 'new guy' mistake, I had not removed the code
from the previous attempt at creating the command button.

So now I actually have it working, it looks like this:

DoCmd.OpenForm stDocName, , , "[VehLocation]=" & "'242' OR [VehLocation]=" &
"'243'"

Thanks for pointing me in the right direction.....

Cheers

Hi Paul,

Please post your code - I'm not sure what might be causing that error. Also,
please indicate which line is highlighted when the error occurs when you go
into debug mode.

One other thing, does the code compile?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for the help with the IN ( ) criteria. I had not come accross
that one yet.

However, now I am getting an error, when I click on the button to
search, I get 'The OpenForm action was canceled' . If I take out the
criteria, the form opens and shows all records, but using both of
your suggestions, generates the same error. The code is exactly as
you suggested, double checked in case my fat thumbs got in the way....

Are you opening this form from another form using the WhereCondition
parameter? You would either build a complex criteria statement or
use the In() criteria. Examples of both are shown below:

DoCmd.OpenForm "frmVehicles", , , "VehLocation=241 or
VehLocation=242 or VehLocation=244 or VehLocation=261 or
VehLocation=262"
DoCmd.OpenForm "frmVehicles", , , "Vehlocation
in(241,242,244,261,262)"

The above examples use hardcoded lists of values, typically you
would be getting the test values from a listbox or some other way.
Postback if you need help with this.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Paul B. wrote:
Can someone advise how best to create a link criteria that would
allow a form to display records that match several criteria?

I want to search my tblVehicles for VehLocation that match a numeric
value of 241 or 242 or 244 or 261 or 262.

Thanks in advance

 

[Sandra Daigle]

Hi Paul,

No problem - glad you got it solved (and you figured out the embedded
quotes by yourself )!

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra,

Ignore the last part, stupid 'new guy' mistake, I had not removed the
code from the previous attempt at creating the command button.

So now I actually have it working, it looks like this:

DoCmd.OpenForm stDocName, , , "[VehLocation]=" & "'242' OR
[VehLocation]=" & "'243'"

Thanks for pointing me in the right direction.....

Cheers

Hi Paul,

Please post your code - I'm not sure what might be causing that
error. Also, please indicate which line is highlighted when the
error occurs when you go into debug mode.

One other thing, does the code compile?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for the help with the IN ( ) criteria. I had not come accross
that one yet.

However, now I am getting an error, when I click on the button to
search, I get 'The OpenForm action was canceled' . If I take out the
criteria, the form opens and shows all records, but using both of
your suggestions, generates the same error. The code is exactly as
you suggested, double checked in case my fat thumbs got in the
way....



:

Are you opening this form from another form using the
WhereCondition parameter? You would either build a complex
criteria statement or use the In() criteria. Examples of both are
shown below:

DoCmd.OpenForm "frmVehicles", , , "VehLocation=241 or
VehLocation=242 or VehLocation=244 or VehLocation=261 or
VehLocation=262"
DoCmd.OpenForm "frmVehicles", , , "Vehlocation
in(241,242,244,261,262)"

The above examples use hardcoded lists of values, typically you
would be getting the test values from a listbox or some other way.
Postback if you need help with this.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Paul B. wrote:
Can someone advise how best to create a link criteria that would
allow a form to display records that match several criteria?

I want to search my tblVehicles for VehLocation that match a
numeric value of 241 or 242 or 244 or 261 or 262.

Thanks in advance

 

[Paul B.]

You know....sometimes I wonder what I got myself into!

Figuring out these quotes has been more hit and miss, trying to use the Help
File is just plain frustrating. I have relied on this newsgroup since I found
it.

Thanks to you and the other MVP's that take the time to answer our posts.

Cheers

Hi Paul,

No problem - glad you got it solved (and you figured out the embedded
quotes by yourself )!

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra,

Ignore the last part, stupid 'new guy' mistake, I had not removed the
code from the previous attempt at creating the command button.

So now I actually have it working, it looks like this:

DoCmd.OpenForm stDocName, , , "[VehLocation]=" & "'242' OR
[VehLocation]=" & "'243'"

Thanks for pointing me in the right direction.....

Cheers

Hi Paul,

Please post your code - I'm not sure what might be causing that
error. Also, please indicate which line is highlighted when the
error occurs when you go into debug mode.

One other thing, does the code compile?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Paul B. wrote:
Thanks for the help with the IN ( ) criteria. I had not come accross
that one yet.

However, now I am getting an error, when I click on the button to
search, I get 'The OpenForm action was canceled' . If I take out the
criteria, the form opens and shows all records, but using both of
your suggestions, generates the same error. The code is exactly as
you suggested, double checked in case my fat thumbs got in the
way....



:

Are you opening this form from another form using the
WhereCondition parameter? You would either build a complex
criteria statement or use the In() criteria. Examples of both are
shown below:

DoCmd.OpenForm "frmVehicles", , , "VehLocation=241 or
VehLocation=242 or VehLocation=244 or VehLocation=261 or
VehLocation=262"
DoCmd.OpenForm "frmVehicles", , , "Vehlocation
in(241,242,244,261,262)"

The above examples use hardcoded lists of values, typically you
would be getting the test values from a listbox or some other way.
Postback if you need help with this.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Paul B. wrote:
Can someone advise how best to create a link criteria that would
allow a form to display records that match several criteria?

I want to search my tblVehicles for VehLocation that match a
numeric value of 241 or 242 or 244 or 261 or 262.

Thanks in advance

 

[Sandra Daigle]

Believe me, I understand your sentiments regarding the Help file! It can be
useful but figuring out how to use it is a Black Art in and of itself.
Newsgroups are alot easier! Glad to have been helpful!