Can I add a list box to the option group?

  • Thread starter Thread starter Sylvie
  • Start date Start date
S

Sylvie

I have a form filtering my reports. I included a option group with 2 options
; opt1 dates between start and end and opt 2 not apply filter, which is
working great.
Can I add a combo or a list box to narrow to filter by class also? How can I
add to the simple condition below?
Here is the filter on the control source of the ReportFilter txtbox:
=Choose([grpFilterOptions],"[DateofTest] Between #" & Forms![View
Reports].[Beginning Visit Date] & "# AND #" & Forms![View Reports].[Ending
Visit Date] & "#","")
 
Hi Sylvie,

instead of using references to a form in your report filter, put a
command button on your form to open the report. Here is a generic
example of code to process your criteria and open the report:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.

If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for
the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use
the ISO 8601 representation of the date:

Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have such a clever equation in your current report filter, I
will leave it up to you to write criteria for your option frame -- but
if you need help, just ask

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

here is something you may want to read:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training


*
(: have an awesome day :)
*



I have a form filtering my reports. I included a option group with 2 options
; opt1 dates between start and end and opt 2 not apply filter, which is
working great.
Can I add a combo or a list box to narrow to filter by class also? How can I
add to the simple condition below?
Here is the filter on the control source of the ReportFilter txtbox:
=Choose([grpFilterOptions],"[DateofTest] Between #" & Forms![View
Reports].[Beginning Visit Date] & "# AND #" & Forms![View Reports].[Ending
Visit Date] & "#","")
Click to expand...
 
Hi Crystal,
thanks for your reply. I've been searching on the community for the code for
months and is really working. I am very new to coding, and I've tried the
code before and hasn't working either. My form has a list box displaying all
tests on left , then o the right I have a opt group with start date and end
date option1, and not filter as opt2. ANd after entered dates and choosing
tests the command button open the report. The filter is working great. I need
to add also another choice of class, which I don't know how to input on the
simple parameter. Any thoughts? TIA

strive4peace said:
Hi Sylvie,

instead of using references to a form in your report filter, put a
command button on your form to open the report. Here is a generic
example of code to process your criteria and open the report:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.

If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for
the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use
the ISO 8601 representation of the date:

Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have such a clever equation in your current report filter, I
will leave it up to you to write criteria for your option frame -- but
if you need help, just ask

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

here is something you may want to read:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training


*
(: have an awesome day :)
*



I have a form filtering my reports. I included a option group with 2 options
; opt1 dates between start and end and opt 2 not apply filter, which is
working great.
Can I add a combo or a list box to narrow to filter by class also? How can I
add to the simple condition below?
Here is the filter on the control source of the ReportFilter txtbox:
=Choose([grpFilterOptions],"[DateofTest] Between #" & Forms![View
Reports].[Beginning Visit Date] & "# AND #" & Forms![View Reports].[Ending
Visit Date] & "#","")
Click to expand...
Click to expand...
 
Hi Sylvie,

what I gave you builds the filter string

what you are doing now will just get more complicated as you decide you
want other criteria options. It is a better plan to build the filter
string in code then launch the report using the WHERE condition argument
to OpenReport -- then end result is the same: the report is filtered.

the advantage of building it in code is that you can ignore criteria
that is not filled and you don't end up with a long equation in the
report filter property.

"I am very new to coding"

Once you do it, it is not hard ... don't let code scare you off! anyone
who can put a complex equation like you did in the report filter
certainly has a logical mind to write VBA code ;)

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

~~~~~~~~~

turn on the property sheet in the design view of your [View
Reports] form

make a command button

Give it a good name, like --> cmd_RptWhatever
(best not to use spaces in names)

Caption --> Open Report
(you can put what you like as this is not too descriptive)

with the command button selected:
click in the 'On Click' event in the property sheet (Events tab).
Notice the the drop-down arrow on the right -- drop list and choose -->
[Event Procedure]

click the builder button [...] to the right to write the code

paste the code in below, substitute the right control and field names

compile and test <smile>

here is some code you can start with (don't forget to remove the filter
from your report)

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

if me.grpFilterOptions = 1 then

If not IsNull(me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & me.[Beginning Visit Date] & "#"
end if

If not IsNull(me.[Ending Visit Date] ) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & me.[Ending Visit Date] & "#"
end if
end if

If not IsNull(me.class_controlname ) Then
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I am assuming that your Class is stored as text (although I would make
it a numeric key that specifies a Classname in a Classes table)

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal
remote programming and training



*
(: have an awesome day :)
*



Hi Crystal,
thanks for your reply. I've been searching on the community for the code for
months and is really working. I am very new to coding, and I've tried the
code before and hasn't working either. My form has a list box displaying all
tests on left , then o the right I have a opt group with start date and end
date option1, and not filter as opt2. ANd after entered dates and choosing
tests the command button open the report. The filter is working great. I need
to add also another choice of class, which I don't know how to input on the
simple parameter. Any thoughts? TIA

strive4peace said:
Hi Sylvie,

instead of using references to a form in your report filter, put a
command button on your form to open the report. Here is a generic
example of code to process your criteria and open the report:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.

If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for
the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use
the ISO 8601 representation of the date:

Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have such a clever equation in your current report filter, I
will leave it up to you to write criteria for your option frame -- but
if you need help, just ask

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

here is something you may want to read:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal
remote programming and training


*
(: have an awesome day :)
*



I have a form filtering my reports. I included a option group with 2 options
; opt1 dates between start and end and opt 2 not apply filter, which is
working great.
Can I add a combo or a list box to narrow to filter by class also? How can I
add to the simple condition below?
Here is the filter on the control source of the ReportFilter txtbox:
=Choose([grpFilterOptions],"[DateofTest] Between #" & Forms![View
Reports].[Beginning Visit Date] & "# AND #" & Forms![View Reports].[Ending
Visit Date] & "#","")
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your patience Chrystal, you are very good on explain it!!
I tried this code before and I did not work. When I choose(from my list
box) the ReportA and enter the dates (option group) it works great. But if I
choose ReportB, it shows me ReportA. I think somehow on the code line:
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
I need to not put the report name but let be the one the user choose (out of
15 or so). I do not if it is possible. As for the class I have a combo inside
group and did not work either. IF is a combo, how can I enter classfield name
and control name? On the Row Source? I have: SELECT Class.ClassID,
Class.ClassName FROM Class ORDER BY [ClassName];
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"

Maybe if I take the combo out of the option group. Any ideas?
Have a great day =^..^=

strive4peace said:
Hi Sylvie,

what I gave you builds the filter string

what you are doing now will just get more complicated as you decide you
want other criteria options. It is a better plan to build the filter
string in code then launch the report using the WHERE condition argument
to OpenReport -- then end result is the same: the report is filtered.

the advantage of building it in code is that you can ignore criteria
that is not filled and you don't end up with a long equation in the
report filter property.

"I am very new to coding"

Once you do it, it is not hard ... don't let code scare you off! anyone
who can put a complex equation like you did in the report filter
certainly has a logical mind to write VBA code ;)

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

~~~~~~~~~

turn on the property sheet in the design view of your [View
Reports] form

make a command button

Give it a good name, like --> cmd_RptWhatever
(best not to use spaces in names)

Caption --> Open Report
(you can put what you like as this is not too descriptive)

with the command button selected:
click in the 'On Click' event in the property sheet (Events tab).
Notice the the drop-down arrow on the right -- drop list and choose -->
[Event Procedure]

click the builder button [...] to the right to write the code

paste the code in below, substitute the right control and field names

compile and test <smile>

here is some code you can start with (don't forget to remove the filter
from your report)

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

if me.grpFilterOptions = 1 then

If not IsNull(me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & me.[Beginning Visit Date] & "#"
end if

If not IsNull(me.[Ending Visit Date] ) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & me.[Ending Visit Date] & "#"
end if
end if

If not IsNull(me.class_controlname ) Then
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I am assuming that your Class is stored as text (although I would make
it a numeric key that specifies a Classname in a Classes table)

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal
remote programming and training



*
(: have an awesome day :)
*



Hi Crystal,
thanks for your reply. I've been searching on the community for the code for
months and is really working. I am very new to coding, and I've tried the
code before and hasn't working either. My form has a list box displaying all
tests on left , then o the right I have a opt group with start date and end
date option1, and not filter as opt2. ANd after entered dates and choosing
tests the command button open the report. The filter is working great. I need
to add also another choice of class, which I don't know how to input on the
simple parameter. Any thoughts? TIA

strive4peace said:
Hi Sylvie,

instead of using references to a form in your report filter, put a
command button on your form to open the report. Here is a generic
example of code to process your criteria and open the report:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.

If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for
the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use
the ISO 8601 representation of the date:

Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have such a clever equation in your current report filter, I
will leave it up to you to write criteria for your option frame -- but
if you need help, just ask

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

here is something you may want to read:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
Click to expand...
Click to expand...
Click to expand...
 
Hi Sylvie,

thank you and you're welcome ;)

the combo should not be in your option group -- just make it apply if it
is filled out -- if not, then ignore it

~~

"But if I choose ReportB, it shows me ReportA. "

that won't happen if you change the code :) Yes, this is the statement
that is opening whatever report is specified:

you could use a variable for the report name instead of literally
putting a name on this line:

DoCmd.OpenReport "Your Report Name", acViewPreview, , mfilter


It is customary to declare variables at the top of a procedure (after
the statement to set up the error handler)

'~~~~~~~~~~~~~~~
Dim mReportname as string
'~~~~~~~~~~~~~~~

then, after the criteria is processed:
'~~~~~~~~~~~~~~~

if something = true then
mReportname = "Report A"
else
mReportname = "Report B"
end if

if not IsNull(mfilter) then
DoCmd.OpenReport mReportname, acViewPreview, , mfilter
else
DoCmd.OpenReport mReportname, acViewPreview
endif

'~~~~~~~~~~~~~~~

how are you selecting Report A or Report B?

Are the fieldnames the same in both reports that would be using the
criteria?

oops, I goofed on the statement ADD the Class to the filter
anyway...Replace what I gave you with this:

'~~~~~~~~~~~~~~~
If not IsNull(me.class_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[ClassID]= " & me.class_controlname
end if
'~~~~~~~~~~~~~~~

WHERE
class_controlname is the Name property of your combobox to pick a Class
-- open the property sheet, click on the combo, select the ALL tab in
the Properties window and see what is in the NAME property. If it is
ambiguous, like combo143, change it to something that makes sense, like
cboClassID

and then, so the criteria can make sure that ClassID is ON the report
even if the Visible property is No

~~~

once you change your procedure, compile it. Look at each line. If
anything does not make sense, post the entire procedure as you have
modified it, then ask about specific lines and we will help you
understand them

go back to the first post I made to this thread -- print it out and read
it a few times as it has more comments

you can do this!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for your patience Chrystal, you are very good on explain it!!
I tried this code before and I did not work. When I choose(from my list
box) the ReportA and enter the dates (option group) it works great. But if I
choose ReportB, it shows me ReportA. I think somehow on the code line:
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
I need to not put the report name but let be the one the user choose (out of
15 or so). I do not if it is possible. As for the class I have a combo inside
group and did not work either. IF is a combo, how can I enter classfield name
and control name? On the Row Source? I have: SELECT Class.ClassID,
Class.ClassName FROM Class ORDER BY [ClassName];
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"

Maybe if I take the combo out of the option group. Any ideas?
Have a great day =^..^=

strive4peace said:
Hi Sylvie,

what I gave you builds the filter string

what you are doing now will just get more complicated as you decide you
want other criteria options. It is a better plan to build the filter
string in code then launch the report using the WHERE condition argument
to OpenReport -- then end result is the same: the report is filtered.

the advantage of building it in code is that you can ignore criteria
that is not filled and you don't end up with a long equation in the
report filter property.

"I am very new to coding"

Once you do it, it is not hard ... don't let code scare you off! anyone
who can put a complex equation like you did in the report filter
certainly has a logical mind to write VBA code ;)

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

~~~~~~~~~

turn on the property sheet in the design view of your [View
Reports] form

make a command button

Give it a good name, like --> cmd_RptWhatever
(best not to use spaces in names)

Caption --> Open Report
(you can put what you like as this is not too descriptive)

with the command button selected:
click in the 'On Click' event in the property sheet (Events tab).
Notice the the drop-down arrow on the right -- drop list and choose -->
[Event Procedure]

click the builder button [...] to the right to write the code

paste the code in below, substitute the right control and field names

compile and test <smile>

here is some code you can start with (don't forget to remove the filter
from your report)

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

if me.grpFilterOptions = 1 then

If not IsNull(me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & me.[Beginning Visit Date] & "#"
end if

If not IsNull(me.[Ending Visit Date] ) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & me.[Ending Visit Date] & "#"
end if
end if

If not IsNull(me.class_controlname ) Then
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I am assuming that your Class is stored as text (although I would make
it a numeric key that specifies a Classname in a Classes table)

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal
remote programming and training



*
(: have an awesome day :)
*



Hi Crystal,
thanks for your reply. I've been searching on the community for the code for
months and is really working. I am very new to coding, and I've tried the
code before and hasn't working either. My form has a list box displaying all
tests on left , then o the right I have a opt group with start date and end
date option1, and not filter as opt2. ANd after entered dates and choosing
tests the command button open the report. The filter is working great. I need
to add also another choice of class, which I don't know how to input on the
simple parameter. Any thoughts? TIA

:

Hi Sylvie,

instead of using references to a form in your report filter, put a
command button on your form to open the report. Here is a generic
example of code to process your criteria and open the report:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.

If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria

make sure that the referenced fields are in the underlying RecordSet for
the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname]>= #" & me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[DateFieldname] <= #" & me.controlname_for_date2 & "#"
end if

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you may wish to use
the ISO 8601 representation of the date:

Format(me.date_controlname, "\#yyyy\-mm\-dd\#")
instead of
"#" & me.date_controlname & "#"

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have such a clever equation in your current report filter, I
will leave it up to you to write criteria for your option frame -- but
if you need help, just ask

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(Me.Filter,""))) > 0 then
me.Label_Criteria.Caption = Me.Filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Difference between + and &
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

here is something you may want to read:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
Click to expand...
Click to expand...
Click to expand...
 
Chrystal,

I'll read tomorrow ("I have a baby 1/2day off") :)
I tried the code, but before to answer your question:
how are you selecting Report A or Report B? From a list box

Are the fieldnames the same in both reports that would be using the
criteria? yes (I'll doublechecke)
So here is the code:
Private Sub cmdOpenReport_Click()
'tell Access you are going to create a variable
Dim mFilter As Variant
Dim mReportname As String

'initialize the variable
mFilter = Null

If Me.grpFilterOptions = 1 Then

End If

If Not IsNull(Me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & Me.[Beginning Visit Date] & "#"
End If

If Not IsNull(Me.[Ending Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & Me.[Ending Visit Date] & "#"
End If

If Not IsNull(Me.ChooseStudy) Then
mFilter = (mFilter + " AND ") _
& "[StudyID]= " & Me.ChooseStudy
End If

If something = True Then
mReportname = "ReportDipstick"
Else
mReportname = "ReportPregTest"
End If

If Not IsNull(mFilter) Then
DoCmd.OpenReport mReportname, acViewPreview, , mFilter
Else
DoCmd.OpenReport mReportname, acViewPreview

End If

End Sub

Good night!


strive4peace said:
Hi Sylvie,

thank you and you're welcome ;)

the combo should not be in your option group -- just make it apply if it
is filled out -- if not, then ignore it

~~

"But if I choose ReportB, it shows me ReportA. "

that won't happen if you change the code :) Yes, this is the statement
that is opening whatever report is specified:

you could use a variable for the report name instead of literally
putting a name on this line:

DoCmd.OpenReport "Your Report Name", acViewPreview, , mfilter


It is customary to declare variables at the top of a procedure (after
the statement to set up the error handler)

'~~~~~~~~~~~~~~~
Dim mReportname as string
'~~~~~~~~~~~~~~~

then, after the criteria is processed:
'~~~~~~~~~~~~~~~

if something = true then
mReportname = "Report A"
else
mReportname = "Report B"
end if

if not IsNull(mfilter) then
DoCmd.OpenReport mReportname, acViewPreview, , mfilter
else
DoCmd.OpenReport mReportname, acViewPreview
endif

'~~~~~~~~~~~~~~~

how are you selecting Report A or Report B?

Are the fieldnames the same in both reports that would be using the
criteria?

oops, I goofed on the statement ADD the Class to the filter
anyway...Replace what I gave you with this:

'~~~~~~~~~~~~~~~
If not IsNull(me.class_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[ClassID]= " & me.class_controlname
end if
'~~~~~~~~~~~~~~~

WHERE
class_controlname is the Name property of your combobox to pick a Class
-- open the property sheet, click on the combo, select the ALL tab in
the Properties window and see what is in the NAME property. If it is
ambiguous, like combo143, change it to something that makes sense, like
cboClassID

and then, so the criteria can make sure that ClassID is ON the report
even if the Visible property is No

~~~

once you change your procedure, compile it. Look at each line. If
anything does not make sense, post the entire procedure as you have
modified it, then ask about specific lines and we will help you
understand them

go back to the first post I made to this thread -- print it out and read
it a few times as it has more comments

you can do this!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for your patience Chrystal, you are very good on explain it!!
I tried this code before and I did not work. When I choose(from my list
box) the ReportA and enter the dates (option group) it works great. But if I
choose ReportB, it shows me ReportA. I think somehow on the code line:
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
I need to not put the report name but let be the one the user choose (out of
15 or so). I do not if it is possible. As for the class I have a combo inside
group and did not work either. IF is a combo, how can I enter classfield name
and control name? On the Row Source? I have: SELECT Class.ClassID,
Class.ClassName FROM Class ORDER BY [ClassName];
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"

Maybe if I take the combo out of the option group. Any ideas?
Have a great day =^..^=

strive4peace said:
Hi Sylvie,

what I gave you builds the filter string

what you are doing now will just get more complicated as you decide you
want other criteria options. It is a better plan to build the filter
string in code then launch the report using the WHERE condition argument
to OpenReport -- then end result is the same: the report is filtered.

the advantage of building it in code is that you can ignore criteria
that is not filled and you don't end up with a long equation in the
report filter property.

"I am very new to coding"

Once you do it, it is not hard ... don't let code scare you off! anyone
who can put a complex equation like you did in the report filter
certainly has a logical mind to write VBA code ;)

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

~~~~~~~~~

turn on the property sheet in the design view of your [View
Reports] form

make a command button

Give it a good name, like --> cmd_RptWhatever
(best not to use spaces in names)

Caption --> Open Report
(you can put what you like as this is not too descriptive)

with the command button selected:
click in the 'On Click' event in the property sheet (Events tab).
Notice the the drop-down arrow on the right -- drop list and choose -->
[Event Procedure]

click the builder button [...] to the right to write the code

paste the code in below, substitute the right control and field names

compile and test <smile>

here is some code you can start with (don't forget to remove the filter
from your report)

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

if me.grpFilterOptions = 1 then

If not IsNull(me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & me.[Beginning Visit Date] & "#"
end if

If not IsNull(me.[Ending Visit Date] ) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & me.[Ending Visit Date] & "#"
end if
end if

If not IsNull(me.class_controlname ) Then
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I am assuming that your Class is stored as text (although I would make
it a numeric key that specifies a Classname in a Classes table)

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal
remote programming and training



*
(: have an awesome day :)
*




Sylvie wrote:
Hi Crystal,
thanks for your reply. I've been searching on the community for the code for
months and is really working. I am very new to coding, and I've tried the
code before and hasn't working either. My form has a list box displaying all
tests on left , then o the right I have a opt group with start date and end
date option1, and not filter as opt2. ANd after entered dates and choosing
tests the command button open the report. The filter is working great. I need
to add also another choice of class, which I don't know how to input on the
simple parameter. Any thoughts? TIA

:

Hi Sylvie,

instead of using references to a form in your report filter, put a
command button on your form to open the report. Here is a generic
example of code to process your criteria and open the report:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited
Click to expand...
Click to expand...
Click to expand...
 
Hi Sylvie,

since you said you only want to use date criteria if your option frame
is 1, then the date stuff should go INSIDE the If...End If like this:

'~~~~~~~~~~~~~~~~
If Me.grpFilterOptions = 1 Then

If Not IsNull(Me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & Me.[Beginning Visit Date] & "#"
End If

If Not IsNull(Me.[Ending Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & Me.[Ending Visit Date] & "#"
End If

End If
'~~~~~~~~~~~~~~~~

I set it up so that:

1. if only [Beginning Visit Date] is filled, it gets everything on or
after that date
2. if only [Ending Visit Date] is filled, it gets everything on or
before that date
3. if they are both filled, it gets everything between and including
those dates


If the name of the report you want is the value of your list box then:

'~~~~~~~~~~~~~~~~
if isnull(me.listbox_controlname) then
msgbox "You must choose a report to display" _
,, "Need to specify report"
exit sub
end if
mReportname = me.listbox_controlname

'~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Chrystal,

I'll read tomorrow ("I have a baby 1/2day off") :)
I tried the code, but before to answer your question:
how are you selecting Report A or Report B? From a list box

Are the fieldnames the same in both reports that would be using the
criteria? yes (I'll doublechecke)
So here is the code:
Private Sub cmdOpenReport_Click()
'tell Access you are going to create a variable
Dim mFilter As Variant
Dim mReportname As String

'initialize the variable
mFilter = Null

If Me.grpFilterOptions = 1 Then

End If

If Not IsNull(Me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & Me.[Beginning Visit Date] & "#"
End If

If Not IsNull(Me.[Ending Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & Me.[Ending Visit Date] & "#"
End If

If Not IsNull(Me.ChooseStudy) Then
mFilter = (mFilter + " AND ") _
& "[StudyID]= " & Me.ChooseStudy
End If

If something = True Then
mReportname = "ReportDipstick"
Else
mReportname = "ReportPregTest"
End If

If Not IsNull(mFilter) Then
DoCmd.OpenReport mReportname, acViewPreview, , mFilter
Else
DoCmd.OpenReport mReportname, acViewPreview

End If

End Sub

Good night!


strive4peace said:
Hi Sylvie,

thank you and you're welcome ;)

the combo should not be in your option group -- just make it apply if it
is filled out -- if not, then ignore it

~~

"But if I choose ReportB, it shows me ReportA. "

that won't happen if you change the code :) Yes, this is the statement
that is opening whatever report is specified:

you could use a variable for the report name instead of literally
putting a name on this line:

DoCmd.OpenReport "Your Report Name", acViewPreview, , mfilter


It is customary to declare variables at the top of a procedure (after
the statement to set up the error handler)

'~~~~~~~~~~~~~~~
Dim mReportname as string
'~~~~~~~~~~~~~~~

then, after the criteria is processed:
'~~~~~~~~~~~~~~~

if something = true then
mReportname = "Report A"
else
mReportname = "Report B"
end if

if not IsNull(mfilter) then
DoCmd.OpenReport mReportname, acViewPreview, , mfilter
else
DoCmd.OpenReport mReportname, acViewPreview
endif

'~~~~~~~~~~~~~~~

how are you selecting Report A or Report B?

Are the fieldnames the same in both reports that would be using the
criteria?

oops, I goofed on the statement ADD the Class to the filter
anyway...Replace what I gave you with this:

'~~~~~~~~~~~~~~~
If not IsNull(me.class_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[ClassID]= " & me.class_controlname
end if
'~~~~~~~~~~~~~~~

WHERE
class_controlname is the Name property of your combobox to pick a Class
-- open the property sheet, click on the combo, select the ALL tab in
the Properties window and see what is in the NAME property. If it is
ambiguous, like combo143, change it to something that makes sense, like
cboClassID

and then, so the criteria can make sure that ClassID is ON the report
even if the Visible property is No

~~~

once you change your procedure, compile it. Look at each line. If
anything does not make sense, post the entire procedure as you have
modified it, then ask about specific lines and we will help you
understand them

go back to the first post I made to this thread -- print it out and read
it a few times as it has more comments

you can do this!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Thanks for your patience Chrystal, you are very good on explain it!!
I tried this code before and I did not work. When I choose(from my list
box) the ReportA and enter the dates (option group) it works great. But if I
choose ReportB, it shows me ReportA. I think somehow on the code line:
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
I need to not put the report name but let be the one the user choose (out of
15 or so). I do not if it is possible. As for the class I have a combo inside
group and did not work either. IF is a combo, how can I enter classfield name
and control name? On the Row Source? I have: SELECT Class.ClassID,
Class.ClassName FROM Class ORDER BY [ClassName];
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"

Maybe if I take the combo out of the option group. Any ideas?
Have a great day =^..^=

:

Hi Sylvie,

what I gave you builds the filter string

what you are doing now will just get more complicated as you decide you
want other criteria options. It is a better plan to build the filter
string in code then launch the report using the WHERE condition argument
to OpenReport -- then end result is the same: the report is filtered.

the advantage of building it in code is that you can ignore criteria
that is not filled and you don't end up with a long equation in the
report filter property.

"I am very new to coding"

Once you do it, it is not hard ... don't let code scare you off! anyone
who can put a complex equation like you did in the report filter
certainly has a logical mind to write VBA code ;)

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

~~~~~~~~~

turn on the property sheet in the design view of your [View
Reports] form

make a command button

Give it a good name, like --> cmd_RptWhatever
(best not to use spaces in names)

Caption --> Open Report
(you can put what you like as this is not too descriptive)

with the command button selected:
click in the 'On Click' event in the property sheet (Events tab).
Notice the the drop-down arrow on the right -- drop list and choose -->
[Event Procedure]

click the builder button [...] to the right to write the code

paste the code in below, substitute the right control and field names

compile and test <smile>

here is some code you can start with (don't forget to remove the filter
from your report)

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

if me.grpFilterOptions = 1 then

If not IsNull(me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & me.[Beginning Visit Date] & "#"
end if

If not IsNull(me.[Ending Visit Date] ) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & me.[Ending Visit Date] & "#"
end if
end if

If not IsNull(me.class_controlname ) Then
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I am assuming that your Class is stored as text (although I would make
it a numeric key that specifies a Classname in a Classes table)

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal
remote programming and training



*
(: have an awesome day :)
*




Sylvie wrote:
Hi Crystal,
thanks for your reply. I've been searching on the community for the code for
months and is really working. I am very new to coding, and I've tried the
code before and hasn't working either. My form has a list box displaying all
tests on left , then o the right I have a opt group with start date and end
date option1, and not filter as opt2. ANd after entered dates and choosing
tests the command button open the report. The filter is working great. I need
to add also another choice of class, which I don't know how to input on the
simple parameter. Any thoughts? TIA

:

Hi Sylvie,

instead of using references to a form in your report filter, put a
command button on your form to open the report. Here is a generic
example of code to process your criteria and open the report:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria

If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if


if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have used:

TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited
Click to expand...
Click to expand...
Click to expand...
 
Hi Chrystal,
YOU ROCK!!! It worked like a charm!!! Many Thanks!!!

strive4peace said:
Hi Sylvie,

since you said you only want to use date criteria if your option frame
is 1, then the date stuff should go INSIDE the If...End If like this:

'~~~~~~~~~~~~~~~~
If Me.grpFilterOptions = 1 Then

If Not IsNull(Me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & Me.[Beginning Visit Date] & "#"
End If

If Not IsNull(Me.[Ending Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & Me.[Ending Visit Date] & "#"
End If

End If
'~~~~~~~~~~~~~~~~

I set it up so that:

1. if only [Beginning Visit Date] is filled, it gets everything on or
after that date
2. if only [Ending Visit Date] is filled, it gets everything on or
before that date
3. if they are both filled, it gets everything between and including
those dates


If the name of the report you want is the value of your list box then:

'~~~~~~~~~~~~~~~~
if isnull(me.listbox_controlname) then
msgbox "You must choose a report to display" _
,, "Need to specify report"
exit sub
end if
mReportname = me.listbox_controlname

'~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Chrystal,

I'll read tomorrow ("I have a baby 1/2day off") :)
I tried the code, but before to answer your question:
how are you selecting Report A or Report B? From a list box

Are the fieldnames the same in both reports that would be using the
criteria? yes (I'll doublechecke)
So here is the code:
Private Sub cmdOpenReport_Click()
'tell Access you are going to create a variable
Dim mFilter As Variant
Dim mReportname As String

'initialize the variable
mFilter = Null

If Me.grpFilterOptions = 1 Then

End If

If Not IsNull(Me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & Me.[Beginning Visit Date] & "#"
End If

If Not IsNull(Me.[Ending Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & Me.[Ending Visit Date] & "#"
End If

If Not IsNull(Me.ChooseStudy) Then
mFilter = (mFilter + " AND ") _
& "[StudyID]= " & Me.ChooseStudy
End If

If something = True Then
mReportname = "ReportDipstick"
Else
mReportname = "ReportPregTest"
End If

If Not IsNull(mFilter) Then
DoCmd.OpenReport mReportname, acViewPreview, , mFilter
Else
DoCmd.OpenReport mReportname, acViewPreview

End If

End Sub

Good night!


strive4peace said:
Hi Sylvie,

thank you and you're welcome ;)

the combo should not be in your option group -- just make it apply if it
is filled out -- if not, then ignore it

~~

"But if I choose ReportB, it shows me ReportA. "

that won't happen if you change the code :) Yes, this is the statement
that is opening whatever report is specified:

you could use a variable for the report name instead of literally
putting a name on this line:

DoCmd.OpenReport "Your Report Name", acViewPreview, , mfilter


It is customary to declare variables at the top of a procedure (after
the statement to set up the error handler)

'~~~~~~~~~~~~~~~
Dim mReportname as string
'~~~~~~~~~~~~~~~

then, after the criteria is processed:
'~~~~~~~~~~~~~~~

if something = true then
mReportname = "Report A"
else
mReportname = "Report B"
end if

if not IsNull(mfilter) then
DoCmd.OpenReport mReportname, acViewPreview, , mfilter
else
DoCmd.OpenReport mReportname, acViewPreview
endif

'~~~~~~~~~~~~~~~

how are you selecting Report A or Report B?

Are the fieldnames the same in both reports that would be using the
criteria?

oops, I goofed on the statement ADD the Class to the filter
anyway...Replace what I gave you with this:

'~~~~~~~~~~~~~~~
If not IsNull(me.class_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[ClassID]= " & me.class_controlname
end if
'~~~~~~~~~~~~~~~

WHERE
class_controlname is the Name property of your combobox to pick a Class
-- open the property sheet, click on the combo, select the ALL tab in
the Properties window and see what is in the NAME property. If it is
ambiguous, like combo143, change it to something that makes sense, like
cboClassID

and then, so the criteria can make sure that ClassID is ON the report
even if the Visible property is No

~~~

once you change your procedure, compile it. Look at each line. If
anything does not make sense, post the entire procedure as you have
modified it, then ask about specific lines and we will help you
understand them

go back to the first post I made to this thread -- print it out and read
it a few times as it has more comments

you can do this!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




Sylvie wrote:
Thanks for your patience Chrystal, you are very good on explain it!!
I tried this code before and I did not work. When I choose(from my list
box) the ReportA and enter the dates (option group) it works great. But if I
choose ReportB, it shows me ReportA. I think somehow on the code line:
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
I need to not put the report name but let be the one the user choose (out of
15 or so). I do not if it is possible. As for the class I have a combo inside
group and did not work either. IF is a combo, how can I enter classfield name
and control name? On the Row Source? I have: SELECT Class.ClassID,
Class.ClassName FROM Class ORDER BY [ClassName];
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"

Maybe if I take the combo out of the option group. Any ideas?
Have a great day =^..^=

:

Hi Sylvie,

what I gave you builds the filter string

what you are doing now will just get more complicated as you decide you
want other criteria options. It is a better plan to build the filter
string in code then launch the report using the WHERE condition argument
to OpenReport -- then end result is the same: the report is filtered.

the advantage of building it in code is that you can ignore criteria
that is not filled and you don't end up with a long equation in the
report filter property.

"I am very new to coding"

Once you do it, it is not hard ... don't let code scare you off! anyone
who can put a complex equation like you did in the report filter
certainly has a logical mind to write VBA code ;)

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

~~~~~~~~~

turn on the property sheet in the design view of your [View
Reports] form

make a command button

Give it a good name, like --> cmd_RptWhatever
(best not to use spaces in names)

Caption --> Open Report
(you can put what you like as this is not too descriptive)

with the command button selected:
click in the 'On Click' event in the property sheet (Events tab).
Notice the the drop-down arrow on the right -- drop list and choose -->
[Event Procedure]

click the builder button [...] to the right to write the code

paste the code in below, substitute the right control and field names

compile and test <smile>

here is some code you can start with (don't forget to remove the filter
from your report)

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

if me.grpFilterOptions = 1 then

If not IsNull(me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & me.[Beginning Visit Date] & "#"
end if

If not IsNull(me.[Ending Visit Date] ) Then
mFilter = (mFilter + " AND ") _
Click to expand...
Click to expand...
Click to expand...
 
you're welcome, Sylvie ;) happy to help

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Hi Chrystal,
YOU ROCK!!! It worked like a charm!!! Many Thanks!!!

strive4peace said:
Hi Sylvie,

since you said you only want to use date criteria if your option frame
is 1, then the date stuff should go INSIDE the If...End If like this:

'~~~~~~~~~~~~~~~~
If Me.grpFilterOptions = 1 Then

If Not IsNull(Me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & Me.[Beginning Visit Date] & "#"
End If

If Not IsNull(Me.[Ending Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & Me.[Ending Visit Date] & "#"
End If

End If
'~~~~~~~~~~~~~~~~

I set it up so that:

1. if only [Beginning Visit Date] is filled, it gets everything on or
after that date
2. if only [Ending Visit Date] is filled, it gets everything on or
before that date
3. if they are both filled, it gets everything between and including
those dates


If the name of the report you want is the value of your list box then:

'~~~~~~~~~~~~~~~~
if isnull(me.listbox_controlname) then
msgbox "You must choose a report to display" _
,, "Need to specify report"
exit sub
end if
mReportname = me.listbox_controlname

'~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*



Chrystal,

I'll read tomorrow ("I have a baby 1/2day off") :)
I tried the code, but before to answer your question:
how are you selecting Report A or Report B? From a list box

Are the fieldnames the same in both reports that would be using the
criteria? yes (I'll doublechecke)
So here is the code:
Private Sub cmdOpenReport_Click()
'tell Access you are going to create a variable
Dim mFilter As Variant
Dim mReportname As String

'initialize the variable
mFilter = Null

If Me.grpFilterOptions = 1 Then

End If

If Not IsNull(Me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & Me.[Beginning Visit Date] & "#"
End If

If Not IsNull(Me.[Ending Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest] <= #" & Me.[Ending Visit Date] & "#"
End If

If Not IsNull(Me.ChooseStudy) Then
mFilter = (mFilter + " AND ") _
& "[StudyID]= " & Me.ChooseStudy
End If

If something = True Then
mReportname = "ReportDipstick"
Else
mReportname = "ReportPregTest"
End If

If Not IsNull(mFilter) Then
DoCmd.OpenReport mReportname, acViewPreview, , mFilter
Else
DoCmd.OpenReport mReportname, acViewPreview

End If

End Sub

Good night!


:

Hi Sylvie,

thank you and you're welcome ;)

the combo should not be in your option group -- just make it apply if it
is filled out -- if not, then ignore it

~~

"But if I choose ReportB, it shows me ReportA. "

that won't happen if you change the code :) Yes, this is the statement
that is opening whatever report is specified:

you could use a variable for the report name instead of literally
putting a name on this line:

DoCmd.OpenReport "Your Report Name", acViewPreview, , mfilter


It is customary to declare variables at the top of a procedure (after
the statement to set up the error handler)

'~~~~~~~~~~~~~~~
Dim mReportname as string
'~~~~~~~~~~~~~~~

then, after the criteria is processed:
'~~~~~~~~~~~~~~~

if something = true then
mReportname = "Report A"
else
mReportname = "Report B"
end if

if not IsNull(mfilter) then
DoCmd.OpenReport mReportname, acViewPreview, , mfilter
else
DoCmd.OpenReport mReportname, acViewPreview
endif

'~~~~~~~~~~~~~~~

how are you selecting Report A or Report B?

Are the fieldnames the same in both reports that would be using the
criteria?

oops, I goofed on the statement ADD the Class to the filter
anyway...Replace what I gave you with this:

'~~~~~~~~~~~~~~~
If not IsNull(me.class_controlname ) Then
mFilter = (mFilter + " AND ") _
& "[ClassID]= " & me.class_controlname
end if
'~~~~~~~~~~~~~~~

WHERE
class_controlname is the Name property of your combobox to pick a Class
-- open the property sheet, click on the combo, select the ALL tab in
the Properties window and see what is in the NAME property. If it is
ambiguous, like combo143, change it to something that makes sense, like
cboClassID

and then, so the criteria can make sure that ClassID is ON the report
even if the Visible property is No

~~~

once you change your procedure, compile it. Look at each line. If
anything does not make sense, post the entire procedure as you have
modified it, then ask about specific lines and we will help you
understand them

go back to the first post I made to this thread -- print it out and read
it a few times as it has more comments

you can do this!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




Sylvie wrote:
Thanks for your patience Chrystal, you are very good on explain it!!
I tried this code before and I did not work. When I choose(from my list
box) the ReportA and enter the dates (option group) it works great. But if I
choose ReportB, it shows me ReportA. I think somehow on the code line:
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
I need to not put the report name but let be the one the user choose (out of
15 or so). I do not if it is possible. As for the class I have a combo inside
group and did not work either. IF is a combo, how can I enter classfield name
and control name? On the Row Source? I have: SELECT Class.ClassID,
Class.ClassName FROM Class ORDER BY [ClassName];
mfilter = "[ClassFieldname]= '" & me.class_controlname & "'"

Maybe if I take the combo out of the option group. Any ideas?
Have a great day =^..^=

:

Hi Sylvie,

what I gave you builds the filter string

what you are doing now will just get more complicated as you decide you
want other criteria options. It is a better plan to build the filter
string in code then launch the report using the WHERE condition argument
to OpenReport -- then end result is the same: the report is filtered.

the advantage of building it in code is that you can ignore criteria
that is not filled and you don't end up with a long equation in the
report filter property.

"I am very new to coding"

Once you do it, it is not hard ... don't let code scare you off! anyone
who can put a complex equation like you did in the report filter
certainly has a logical mind to write VBA code ;)

read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

~~~~~~~~~

turn on the property sheet in the design view of your [View
Reports] form

make a command button

Give it a good name, like --> cmd_RptWhatever
(best not to use spaces in names)

Caption --> Open Report
(you can put what you like as this is not too descriptive)

with the command button selected:
click in the 'On Click' event in the property sheet (Events tab).
Notice the the drop-down arrow on the right -- drop list and choose -->
[Event Procedure]

click the builder button [...] to the right to write the code

paste the code in below, substitute the right control and field names

compile and test <smile>

here is some code you can start with (don't forget to remove the filter
from your report)

'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant

'initialize the variable
mFilter = null

if me.grpFilterOptions = 1 then

If not IsNull(me.[Beginning Visit Date]) Then
mFilter = (mFilter + " AND ") _
& "[DateofTest]>= #" & me.[Beginning Visit Date] & "#"
end if

If not IsNull(me.[Ending Visit Date] ) Then
mFilter = (mFilter + " AND ") _
Click to expand...
Click to expand...
Click to expand...
 
Back
Top