Criteria/Field Problem

[Lyle Caldwell]

Hello, I've done all the complicated stuff just fine, and now I have one
simple task left. Of course it's the "simple" thing that has me stopped in
my tracks.

I have a form that allows users to generate different reports depending on
whether a customer has a positive or a zero balance. The user selects which
type from a pull down list, and an unbound text box stores a different value
depending on which type is selected. This value will then (hopefully)
become the criteria for a field on a query contained within the form.

To be specific, if the user selects a customer with a zero balance, the
unbound text box stores the value "<=0", else it stores the value ">=0".
These are the criteria I need for the Balance field in the query, to
determine which records go to the report.

But this isn't working. If I put "<=0" (without quotes) directly in the
criteria box for the Balance field in the query, the query displays the data
I want. I have other fields in the query using form field contents as
criteria, and their content is enclosed in quotes in the formula that fills
the form field contents.

What am I missing, please? Thanks in advance.

Stuff that might help:

The form is called CmpRpts.
The combo box that lets the user select a customer type is called
[DonorTypeList].
The text box on the form that should store the criteria for the query field
is called [DonorTypeHolder]. Currently it stores the values "<=0" or ">=0"
depending on the content of [DonorTypeList]. The formula I'm using (called
from an AfterUpdate event) is:

If Me![DonorTypeList] = "Completed Pledges" Then
Me![DonorTypeHolder] = "<=0"
Else
Me![DonorTypeHolder] = ">=1"
End If

I also tried an IIf statement for the Control Source of ![DonorTypeHolder] ,
which worked exactly the same (not at all).

The field in the query is called Balance. Its criteria is set to
[Forms]![CmpRpts].[DonorTypeHolder].

 

[Chris]

Lyle,

It would appear that your unbound text box is containing a
value that doesn't work in the query. It may be storing a
string rather than a number (it sounds like your query is
looking for >=0, but your value is a string ">-0").

I have a workaround if you are only comparing two values.
You can set the unbound text box to a yes/no field, or
even a text box that contains "yes" or "no". You can then
use and iif statement in your query to determine your
criteria.

For example:

=iif([unbound text box]="yes",>0,<=)

It may just be your description, but you show ">=0
and "<=0" as your two options. Both would trigger the
resulf if the value IS zero.


hope this helps

Chris

-----Original Message-----
Hello, I've done all the complicated stuff just fine, and now I have one
simple task left. Of course it's the "simple" thing that has me stopped in
my tracks.

I have a form that allows users to generate different reports depending on
whether a customer has a positive or a zero balance. The user selects which
type from a pull down list, and an unbound text box stores a different value
depending on which type is selected. This value will then (hopefully)
become the criteria for a field on a query contained within the form.

To be specific, if the user selects a customer with a zero balance, the
unbound text box stores the value "<=0", else it stores the value ">=0".
These are the criteria I need for the Balance field in the query, to
determine which records go to the report.

But this isn't working. If I put "<=0" (without quotes) directly in the
criteria box for the Balance field in the query, the query displays the data
I want. I have other fields in the query using form field contents as
criteria, and their content is enclosed in quotes in the formula that fills
the form field contents.

What am I missing, please? Thanks in advance.

Stuff that might help:

The form is called CmpRpts.
The combo box that lets the user select a customer type is called
[DonorTypeList].
The text box on the form that should store the criteria for the query field
is called [DonorTypeHolder]. Currently it stores the

depending on the content of [DonorTypeList]. The formula I'm using (called
from an AfterUpdate event) is:

If Me![DonorTypeList] = "Completed Pledges" Then
Me![DonorTypeHolder] = "<=0"
Else
Me![DonorTypeHolder] = ">=1"
End If

I also tried an IIf statement for the Control Source of ! [DonorTypeHolder] ,
which worked exactly the same (not at all).

The field in the query is called Balance. Its criteria is set to
[Forms]![CmpRpts].[DonorTypeHolder].

--
Lyle Caldwell




.