Expressions for parameters in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen - 07 Aug 2006 17:10 GMT

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?

Reply to this Message

John Spencer - 07 Aug 2006 17:51 GMT

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
[quoted text clipped - 9 lines]
>
> How can this expression be written?

Reply to this Message

Karen - 07 Aug 2006 21:26 GMT

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
[quoted text clipped - 25 lines]
> >
> > How can this expression be written?

Reply to this Message

John Spencer - 08 Aug 2006 01:33 GMT

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,
>
[quoted text clipped - 36 lines]
> > >
> > > How can this expression be written?

Reply to this Message