Expressions for parameters in query

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

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?
 
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,

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.

John Spencer said:
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


Karen said:
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?
Click to expand...
Click to expand...
 
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.

John Spencer said:
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


Karen said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top