Qry-data format issue

[Betrock52]

Background:
I have a LU table used to input Cold Hardiness zones for
plants in a table of Plant species. The values are
similar to 9, 9A, 9B, 10, 10A, etc. One needs to select
plants by Zone - qry to return plants with a hardiness
Zone > 9A AND < 10B, for example. [BTW - info only: for
clarification- there are actually 2 Hardiness Zone field
in the Plants TBL - one for the northern range, one for
the southern range. So a query of >9b in one field and
<10B in the other field will return the needed results -
except for the data format problem. See below.]

Even though the values are alpha numeric, in order to sort
properly (9A is colder than 10B), they must be numeric in
the underlying data. But they must appear alpha-numeric.
The user would never see (or understand) the vaule of 9A =
9.25, of 10B = 10.75 for example.

Structure:
The Zone field in the Plant TBL is a 'single' number. It's
type is a Lookup Wizard, which is a keyed table of 2
columns as follows:
PK
Fld1 fLD2
9.0 9
9.25 9A
9.75 9B
etc. etc.

Only Column 'fld2' shows. So you, in effect, type or
select a Text value which displays as a text value, but is
recorded as a number value. Life is good. The table will
sort just fine.

Problem:
If you do a 'Find' on the field, you have the option to
select 'as formatted' and if you type into the Find box:
9B if will return all 9Bs. Again, life is good. It accepts
the text value, and returns the appropriate number values
as text.

However what I need is for it to return a range, there
fore I need a parameter query. The problem is that you
can't enter >=9B for example because the underlying values
are numeric. You get that lovely message saying too
complex, etc.

I know there is a way to do this, I just can't fathom what
it might be. Use a form as input for the query
parameters? Would the form allow the text input as a
search criteria for what is, in fact a number? If
the 'Find' feature allows it, it must be possible thru
some other route. I can write simple code fairly well, I
just can't seem to think through this.

I'd sure appreciate any guidance, thoughts or ideas.

Thanks,
Anne

 

[Ted Allen]

Hi Anne,

I think that your idea of using a form to provide the
input parameters is a good one. I would create a form
with two combo boxes, each without a control source. Set
the rowsource, bound column, and column width settings so
that the combo boxes display the text, but are bound to
the number (but again, not linked to any control source
in an underlying table or query).

In your query, reference each of the form controls (such
as Forms![YourFormName]![YourFieldName]) in your criteria
against the numeric zone values.

You can place a button on the input form to launch the
query. The query will use the values of the combo boxes
automatically in the criteria and display the matching
results.

Hope that helps. Post back if you have questions.

-Ted Allen

-----Original Message-----
Background:
I have a LU table used to input Cold Hardiness zones for
plants in a table of Plant species. The values are
similar to 9, 9A, 9B, 10, 10A, etc. One needs to select
plants by Zone - qry to return plants with a hardiness
Zone > 9A AND < 10B, for example. [BTW - info only: for
clarification- there are actually 2 Hardiness Zone field
in the Plants TBL - one for the northern range, one for
the southern range. So a query of >9b in one field and
<10B in the other field will return the needed results -
except for the data format problem. See below.]

Even though the values are alpha numeric, in order to sort
properly (9A is colder than 10B), they must be numeric in
the underlying data. But they must appear alpha- numeric.
The user would never see (or understand) the vaule of 9A =
9.25, of 10B = 10.75 for example.

Structure:
The Zone field in the Plant TBL is a 'single' number. It's
type is a Lookup Wizard, which is a keyed table of 2
columns as follows:
PK
Fld1 fLD2
9.0 9
9.25 9A
9.75 9B
etc. etc.

Only Column 'fld2' shows. So you, in effect, type or
select a Text value which displays as a text value, but is
recorded as a number value. Life is good. The table will
sort just fine.

Problem:
If you do a 'Find' on the field, you have the option to
select 'as formatted' and if you type into the Find box:
9B if will return all 9Bs. Again, life is good. It accepts
the text value, and returns the appropriate number values
as text.

However what I need is for it to return a range, there
fore I need a parameter query. The problem is that you
can't enter >=9B for example because the underlying values
are numeric. You get that lovely message saying too
complex, etc.

I know there is a way to do this, I just can't fathom what
it might be. Use a form as input for the query
parameters? Would the form allow the text input as a
search criteria for what is, in fact a number? If
the 'Find' feature allows it, it must be possible thru
some other route. I can write simple code fairly well, I
just can't seem to think through this.

I'd sure appreciate any guidance, thoughts or ideas.

Thanks,
Anne
.

 

[Betrock]

Thanks, Ted. I will give the form a try. Sound slike it
ought to solve my problem.

Thanks again,
Anne

-----Original Message-----
Hi Anne,

I think that your idea of using a form to provide the
input parameters is a good one. I would create a form
with two combo boxes, each without a control source. Set
the rowsource, bound column, and column width settings so
that the combo boxes display the text, but are bound to
the number (but again, not linked to any control source
in an underlying table or query).

In your query, reference each of the form controls (such
as Forms![YourFormName]![YourFieldName]) in your criteria
against the numeric zone values.

You can place a button on the input form to launch the
query. The query will use the values of the combo boxes
automatically in the criteria and display the matching
results.

Hope that helps. Post back if you have questions.

-Ted Allen

-----Original Message-----
Background:
I have a LU table used to input Cold Hardiness zones for
plants in a table of Plant species. The values are
similar to 9, 9A, 9B, 10, 10A, etc. One needs to select
plants by Zone - qry to return plants with a hardiness
Zone > 9A AND < 10B, for example. [BTW - info only: for
clarification- there are actually 2 Hardiness Zone field
in the Plants TBL - one for the northern range, one for
the southern range. So a query of >9b in one field and
<10B in the other field will return the needed results -
except for the data format problem. See below.]

Even though the values are alpha numeric, in order to sort
properly (9A is colder than 10B), they must be numeric in
the underlying data. But they must appear alpha- numeric.
The user would never see (or understand) the vaule of 9A =
9.25, of 10B = 10.75 for example.

Structure:
The Zone field in the Plant TBL is a 'single' number. It's
type is a Lookup Wizard, which is a keyed table of 2
columns as follows:
PK
Fld1 fLD2
9.0 9
9.25 9A
9.75 9B
etc. etc.

Only Column 'fld2' shows. So you, in effect, type or
select a Text value which displays as a text value, but is
recorded as a number value. Life is good. The table will
sort just fine.

Problem:
If you do a 'Find' on the field, you have the option to
select 'as formatted' and if you type into the Find box:
9B if will return all 9Bs. Again, life is good. It accepts
the text value, and returns the appropriate number values
as text.

However what I need is for it to return a range, there
fore I need a parameter query. The problem is that you
can't enter >=9B for example because the underlying values
are numeric. You get that lovely message saying too
complex, etc.

I know there is a way to do this, I just can't fathom what
it might be. Use a form as input for the query
parameters? Would the form allow the text input as a
search criteria for what is, in fact a number? If
the 'Find' feature allows it, it must be possible thru
some other route. I can write simple code fairly well, I
just can't seem to think through this.

I'd sure appreciate any guidance, thoughts or ideas.

Thanks,
Anne
.

.