Expressions for parameters in query

[Guest]

Hi,
I have created a form to pass parameters to a query. The first parameter is
the date range: Between [Forms]![frmSelectDateType]![BeginningDate] And
[Forms]![frmSelectDateType]![EndDate]. The next parameter I have on the form
is a combo box with two values, Type 1 and Type 2. If the user selects "Type
1", I need the query to run Len([Fund])>4 as the criteria for the Fund field.
If the user selects "Type 2", I need the query to run Len([Fund])<=4.

How can this expression be written?

 

[John Spencer]

Add a column to the query.
Field: LessThan4: Len(Fund) > 4
Criteria: Forms!frmSelectDateType!TxtType="Type1"

What this does is
--Calculates the Len of Fund being greater than 4 and returns True (or
False)
--Criteria returns True if "Type 1" is selected on the form or false
otherwise

So
if the Len(Fund) > 4 and type is "Type 1" (True = True evaluates to True)
only Fund with len > 4 are returned
if the Len(Fund) <= 4 and type is Not "Type 1" (False= False evaluates to
True) only Fund with len <= 4 are returned

 

[Guest]

Hi,

Thanks for the help. I tried your suggestion, but it doesn't seem to pull
the parameter correctly. I added the field and criteria you mentioned to the
query. I ran the query to check and it does not pull the information based
on the types from the drop down. Where you have mentioned TxtType, would
that be the name of the combo box that I am using and the value from the
list. In this case Forms![frmSelectDateType]![ReportType] = "Type 1". When
I viewed the query, the criteria was moved to its own column next to the
column I created called LessThan4.

Add a column to the query.
Field: LessThan4: Len(Fund) > 4
Criteria: Forms!frmSelectDateType!TxtType="Type1"

What this does is
--Calculates the Len of Fund being greater than 4 and returns True (or
False)
--Criteria returns True if "Type 1" is selected on the form or false
otherwise

So
if the Len(Fund) > 4 and type is "Type 1" (True = True evaluates to True)
only Fund with len > 4 are returned
if the Len(Fund) <= 4 and type is Not "Type 1" (False= False evaluates to
True) only Fund with len <= 4 are returned

Hi,
I have created a form to pass parameters to a query. The first parameter
is
the date range: Between [Forms]![frmSelectDateType]![BeginningDate] And
[Forms]![frmSelectDateType]![EndDate]. The next parameter I have on the
form
is a combo box with two values, Type 1 and Type 2. If the user selects
"Type
1", I need the query to run Len([Fund])>4 as the criteria for the Fund
field.
If the user selects "Type 2", I need the query to run Len([Fund])<=4.

How can this expression be written?

 

[John Spencer]

Yes, since I did not know the name of your control, I made up a name for the
control.

I can't test this right now, but you can try something like the following.

Field: CBool(Len(Fund & "") > 4)
Criteria: = CBool(Forms![frmSelectDateType]![ReportType] = "Type 1")

Hi,

Thanks for the help. I tried your suggestion, but it doesn't seem to pull
the parameter correctly. I added the field and criteria you mentioned to the
query. I ran the query to check and it does not pull the information based
on the types from the drop down. Where you have mentioned TxtType, would
that be the name of the combo box that I am using and the value from the
list. In this case Forms![frmSelectDateType]![ReportType] = "Type 1". When
I viewed the query, the criteria was moved to its own column next to the
column I created called LessThan4.

Add a column to the query.
Field: LessThan4: Len(Fund) > 4
Criteria: Forms!frmSelectDateType!TxtType="Type1"

What this does is
--Calculates the Len of Fund being greater than 4 and returns True (or
False)
--Criteria returns True if "Type 1" is selected on the form or false
otherwise

So
if the Len(Fund) > 4 and type is "Type 1" (True = True evaluates to True)
only Fund with len > 4 are returned
if the Len(Fund) <= 4 and type is Not "Type 1" (False= False evaluates to
True) only Fund with len <= 4 are returned

Hi,
I have created a form to pass parameters to a query. The first parameter
is
the date range: Between [Forms]![frmSelectDateType]![BeginningDate] And
[Forms]![frmSelectDateType]![EndDate]. The next parameter I have on the
form
is a combo box with two values, Type 1 and Type 2. If the user selects
"Type
1", I need the query to run Len([Fund])>4 as the criteria for the Fund
field.
If the user selects "Type 2", I need the query to run Len([Fund])<=4.

How can this expression be written?