Building Complex Criteria for Queries in Access 2007 (Part II)

[Hector Ibarra]

The following code recommended to me by a colleague of the discussion group
really worked (Please See the Code below). Thanks!!!. How can I a build a
similar code for columns that have more than 10-15 variables? If I try to
replicate the code to account for all variables then I run out of space.

IIf([Forms]![Parameters].[secType] Is Not
Null,[Forms]![Parameters].[secType],"B") Or
IIf([Forms]![Parameters].[secType] Is Not
Null,[Forms]![Parameters].[secType],"I")

 

[Jeff Boyce]

Hector

Just to be sure I'm understanding, are you saying that you have a column
that has multiple values across the many records/rows? For example, a
[PostalCode] column would store ?!1000's?! of postal codes for address
records for a large number of ... customers...

Depending on what you are trying to do, one option would be to use something
like (untested):

Not In('B', 'I', 'R', ...)

in the Selection Criterion.

I guess I'm having trouble visualizing both your underlying data and your
form...

Regards

Jeff Boyce
Microsoft Access MVP

 

[Hector Ibarra]

Jeff:

I apologize for the lack of clarity. I have build a database with prices of
a specific type of financial securities. I am preparing the database to be
used for external consultation purposes within my company and clients. Most
of my colleagues and clients are not very familiar with Access so I have
generated a base query that summarizes all the relevant information they
might be interested to look for. In essence I have created a form (called
parameters), where the external user can input the definition of the
variables they want to include in the query. Just for simplicity purposes,
the list of key variables in the data base are the following:

[StartDate]
[EndDate]
[secType]
[secZone]
[secID]
[priceType]
[MarketID]

For example, the variable called secZone incoporates 15 Categories (ex. US
Earthquake, US Hurricane, Japan Hurricane, etc.) The user can input a
specific selection, let's say US Hurricane and the query will display all the
prices available for securities that are related to US Hurricanes. My problem
is that in teh current form, if teh user would like to see all Categories and
theerfore decides to leave the input in teh form in "blank>, the query
returns and empty result. What I was looking for with the introduction of a
logic function for the criteria was to specify that if the input is "Blanjk",
the query should return all results from all teh categories within a
specififc variable/column.

In the example I used initially I only had two categories (B=Bonds,
I=ILW's). Now I was looking to expand this same example to variables that
have mutliple values. xample I used

 

[Jeff Boyce]

Hector

Most folks are not familiar with Access or with the concepts of
normalization/relational database design ... nor do they need to be! The
use of tools like Word and Excel is relatively easy because most folks work
with words and numbers in their everyday lives. Normal folks don't work
with application development tools and relational databases <g>!

How depends on what ... and I don't have a clear picture of what you are
using as your query. Please post the SQL statement your query uses.

Are you saying that you use a "query", or that your form dynamically builds
a SQL statement, based on the selection criteria entered by the users on the
form?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

Jeff:

I apologize for the lack of clarity. I have build a database with prices
of
a specific type of financial securities. I am preparing the database to
be
used for external consultation purposes within my company and clients.
Most
of my colleagues and clients are not very familiar with Access so I have
generated a base query that summarizes all the relevant information they
might be interested to look for. In essence I have created a form (called
parameters), where the external user can input the definition of the
variables they want to include in the query. Just for simplicity purposes,
the list of key variables in the data base are the following:

[StartDate]
[EndDate]
[secType]
[secZone]
[secID]
[priceType]
[MarketID]

For example, the variable called secZone incoporates 15 Categories (ex. US
Earthquake, US Hurricane, Japan Hurricane, etc.) The user can input a
specific selection, let's say US Hurricane and the query will display all
the
prices available for securities that are related to US Hurricanes. My
problem
is that in teh current form, if teh user would like to see all Categories
and
theerfore decides to leave the input in teh form in "blank>, the query
returns and empty result. What I was looking for with the introduction of
a
logic function for the criteria was to specify that if the input is
"Blanjk",
the query should return all results from all teh categories within a
specififc variable/column.

In the example I used initially I only had two categories (B=Bonds,
I=ILW's). Now I was looking to expand this same example to variables that
have mutliple values. xample I used

The following code recommended to me by a colleague of the discussion
group
really worked (Please See the Code below). Thanks!!!. How can I a build a
similar code for columns that have more than 10-15 variables? If I try to
replicate the code to account for all variables then I run out of space.

IIf([Forms]![Parameters].[secType] Is Not
Null,[Forms]![Parameters].[secType],"B") Or
IIf([Forms]![Parameters].[secType] Is Not
Null,[Forms]![Parameters].[secType],"I")