Parameters in Queries and Reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I created a parameter in my query, and which my report is based of the query.

This is all going through a form through a list box.

I was wondering, instead of selecting one of the values and generate the
report. I was wondering if there is a way to select all values to generate
the report.

Also, if I have multiple parameters in different fields in my queries then
goes through the form to be selected. Example, Select a Month, Select an
Item, and Select a Risk.

How do I select all for each example:
For example if I want to see all the months but only a specific risk and
item or I want to see all the items and risk, but only one month.

And how do I select everything for all the examples?
 
In the query criteria you can use

Where [Field Name] Like IIf(Forms![FormName]![FieldName] = "" Or
Forms![FormName]![FieldName] Is Null, "*", Forms![FormName]![FieldName])

Using Like "*" will return all values
Using Like (value) will return the records that match this value.

So, in the criteria we check if a value was entered to the field in the
form, if no value was selected the iif will return "*" which return all values
 
The problem with that approach is that it will not select any rows where the
field in question is Null.

To be able to handle that situation, make the criteria:

Where ([Field Name] = Forms![FormName]![FieldName] OR
Forms![FormName]![FieldName] IS NULL)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ofer said:
In the query criteria you can use

Where [Field Name] Like IIf(Forms![FormName]![FieldName] = "" Or
Forms![FormName]![FieldName] Is Null, "*", Forms![FormName]![FieldName])

Using Like "*" will return all values
Using Like (value) will return the records that match this value.

So, in the criteria we check if a value was entered to the field in the
form, if no value was selected the iif will return "*" which return all
values

--
\\// Live Long and Prosper \\//
BS"D


Lee said:
Hello,

I created a parameter in my query, and which my report is based of the
query.

This is all going through a form through a list box.

I was wondering, instead of selecting one of the values and generate the
report. I was wondering if there is a way to select all values to
generate
the report.

Also, if I have multiple parameters in different fields in my queries
then
goes through the form to be selected. Example, Select a Month, Select an
Item, and Select a Risk.

How do I select all for each example:
For example if I want to see all the months but only a specific risk and
item or I want to see all the items and risk, but only one month.

And how do I select everything for all the examples?
Click to expand...
Click to expand...
 
Hey Doug,

So I typed this in the criteria in my query.

Where([list_Preparer]=[Forms]![View Reports2]![list_Preparer] Or
[Forms]![View Reports 2]![list_Preparer] Is Null)

Then it says undefined function 'Where' in expression.

I am not sure what to do after this.

Douglas J. Steele said:
The problem with that approach is that it will not select any rows where the
field in question is Null.

To be able to handle that situation, make the criteria:

Where ([Field Name] = Forms![FormName]![FieldName] OR
Forms![FormName]![FieldName] IS NULL)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ofer said:
In the query criteria you can use

Where [Field Name] Like IIf(Forms![FormName]![FieldName] = "" Or
Forms![FormName]![FieldName] Is Null, "*", Forms![FormName]![FieldName])

Using Like "*" will return all values
Using Like (value) will return the records that match this value.

So, in the criteria we check if a value was entered to the field in the
form, if no value was selected the iif will return "*" which return all
values

--
\\// Live Long and Prosper \\//
BS"D


Lee said:
Hello,

I created a parameter in my query, and which my report is based of the
query.

This is all going through a form through a list box.

I was wondering, instead of selecting one of the values and generate the
report. I was wondering if there is a way to select all values to
generate
the report.

Also, if I have multiple parameters in different fields in my queries
then
goes through the form to be selected. Example, Select a Month, Select an
Item, and Select a Risk.

How do I select all for each example:
For example if I want to see all the months but only a specific risk and
item or I want to see all the items and risk, but only one month.

And how do I select everything for all the examples?
Click to expand...
Click to expand...
Click to expand...
 
You need a space between the word Where and the opening parenthesis.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lee said:
Hey Doug,

So I typed this in the criteria in my query.

Where([list_Preparer]=[Forms]![View Reports2]![list_Preparer] Or
[Forms]![View Reports 2]![list_Preparer] Is Null)

Then it says undefined function 'Where' in expression.

I am not sure what to do after this.

Douglas J. Steele said:
The problem with that approach is that it will not select any rows where the
field in question is Null.

To be able to handle that situation, make the criteria:

Where ([Field Name] = Forms![FormName]![FieldName] OR
Forms![FormName]![FieldName] IS NULL)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ofer said:
In the query criteria you can use

Where [Field Name] Like IIf(Forms![FormName]![FieldName] = "" Or
Forms![FormName]![FieldName] Is Null, "*", Forms![FormName]![FieldName])

Using Like "*" will return all values
Using Like (value) will return the records that match this value.

So, in the criteria we check if a value was entered to the field in the
form, if no value was selected the iif will return "*" which return all
values

--
\\// Live Long and Prosper \\//
BS"D


:

Hello,

I created a parameter in my query, and which my report is based of the
query.

This is all going through a form through a list box.

I was wondering, instead of selecting one of the values and generate the
report. I was wondering if there is a way to select all values to
generate
the report.

Also, if I have multiple parameters in different fields in my queries
then
goes through the form to be selected. Example, Select a Month, Select an
Item, and Select a Risk.

How do I select all for each example:
For example if I want to see all the months but only a specific risk and
item or I want to see all the items and risk, but only one month.

And how do I select everything for all the examples?
Click to expand...
Click to expand...
Click to expand...
 
Where ( [list_Preparer]=[Forms]![View Reports2]![list_Preparer] Or
[Forms]![View Reports 2]![list_Preparer] Is Null)

so I did that and it keeps deleting the spaces and giving back the error.

Douglas J Steele said:
You need a space between the word Where and the opening parenthesis.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lee said:
Hey Doug,

So I typed this in the criteria in my query.

Where([list_Preparer]=[Forms]![View Reports2]![list_Preparer] Or
[Forms]![View Reports 2]![list_Preparer] Is Null)

Then it says undefined function 'Where' in expression.

I am not sure what to do after this.

Douglas J. Steele said:
The problem with that approach is that it will not select any rows where the
field in question is Null.

To be able to handle that situation, make the criteria:

Where ([Field Name] = Forms![FormName]![FieldName] OR
Forms![FormName]![FieldName] IS NULL)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In the query criteria you can use

Where [Field Name] Like IIf(Forms![FormName]![FieldName] = "" Or
Forms![FormName]![FieldName] Is Null, "*", Forms![FormName]![FieldName])

Using Like "*" will return all values
Using Like (value) will return the records that match this value.

So, in the criteria we check if a value was entered to the field in the
form, if no value was selected the iif will return "*" which return all
values

--
\\// Live Long and Prosper \\//
BS"D


:

Hello,

I created a parameter in my query, and which my report is based of the
query.

This is all going through a form through a list box.

I was wondering, instead of selecting one of the values and generate the
report. I was wondering if there is a way to select all values to
generate
the report.

Also, if I have multiple parameters in different fields in my queries
then
goes through the form to be selected. Example, Select a Month, Select an
Item, and Select a Risk.

How do I select all for each example:
For example if I want to see all the months but only a specific risk and
item or I want to see all the items and risk, but only one month.

And how do I select everything for all the examples?
Click to expand...
Click to expand...
Click to expand...
 
There is a misunderstanding here. Doug thinks you are looking at SQL view I
think.

If you are using the query by grid, just put that in the criteria row under
your field (minus the word Where)


--
Joan Wild
Microsoft Access MVP
Where ( [list_Preparer]=[Forms]![View Reports2]![list_Preparer] Or
[Forms]![View Reports 2]![list_Preparer] Is Null)

so I did that and it keeps deleting the spaces and giving back the
error.

Douglas J Steele said:
You need a space between the word Where and the opening parenthesis.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Lee said:
Hey Doug,

So I typed this in the criteria in my query.

Where([list_Preparer]=[Forms]![View Reports2]![list_Preparer] Or
[Forms]![View Reports 2]![list_Preparer] Is Null)

Then it says undefined function 'Where' in expression.

I am not sure what to do after this.

:

The problem with that approach is that it will not select any rows
where the field in question is Null.

To be able to handle that situation, make the criteria:

Where ([Field Name] = Forms![FormName]![FieldName] OR
Forms![FormName]![FieldName] IS NULL)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In the query criteria you can use

Where [Field Name] Like IIf(Forms![FormName]![FieldName] = "" Or
Forms![FormName]![FieldName] Is Null, "*",
Forms![FormName]![FieldName])

Using Like "*" will return all values
Using Like (value) will return the records that match this value.

So, in the criteria we check if a value was entered to the field
in the form, if no value was selected the iif will return "*"
which return all values

--
\\// Live Long and Prosper \\//
BS"D


:

Hello,

I created a parameter in my query, and which my report is based
of the query.

This is all going through a form through a list box.

I was wondering, instead of selecting one of the values and
generate the report. I was wondering if there is a way to
select all values to generate
the report.

Also, if I have multiple parameters in different fields in my
queries then
goes through the form to be selected. Example, Select a Month,
Select an Item, and Select a Risk.

How do I select all for each example:
For example if I want to see all the months but only a specific
risk and item or I want to see all the items and risk, but only
one month.

And how do I select everything for all the examples?
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Joan.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Joan Wild said:
There is a misunderstanding here. Doug thinks you are looking at SQL view I
think.

If you are using the query by grid, just put that in the criteria row under
your field (minus the word Where)


--
Joan Wild
Microsoft Access MVP
Where ( [list_Preparer]=[Forms]![View Reports2]![list_Preparer] Or
[Forms]![View Reports 2]![list_Preparer] Is Null)

so I did that and it keeps deleting the spaces and giving back the
error.

Douglas J Steele said:
You need a space between the word Where and the opening parenthesis.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hey Doug,

So I typed this in the criteria in my query.

Where([list_Preparer]=[Forms]![View Reports2]![list_Preparer] Or
[Forms]![View Reports 2]![list_Preparer] Is Null)

Then it says undefined function 'Where' in expression.

I am not sure what to do after this.

:

The problem with that approach is that it will not select any rows
where the field in question is Null.

To be able to handle that situation, make the criteria:

Where ([Field Name] = Forms![FormName]![FieldName] OR
Forms![FormName]![FieldName] IS NULL)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In the query criteria you can use

Where [Field Name] Like IIf(Forms![FormName]![FieldName] = "" Or
Forms![FormName]![FieldName] Is Null, "*",
Forms![FormName]![FieldName])

Using Like "*" will return all values
Using Like (value) will return the records that match this value.

So, in the criteria we check if a value was entered to the field
in the form, if no value was selected the iif will return "*"
which return all values

--
\\// Live Long and Prosper \\//
BS"D


:

Hello,

I created a parameter in my query, and which my report is based
of the query.

This is all going through a form through a list box.

I was wondering, instead of selecting one of the values and
generate the report. I was wondering if there is a way to
select all values to generate
the report.

Also, if I have multiple parameters in different fields in my
queries then
goes through the form to be selected. Example, Select a Month,
Select an Item, and Select a Risk.

How do I select all for each example:
For example if I want to see all the months but only a specific
risk and item or I want to see all the items and risk, but only
one month.

And how do I select everything for all the examples?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top