parameter query data format problem

[Betrock]

This is probably very simple, but I just can't see my way thru it.....

Short version: keyed values(numeric) in a lookup table are stored in a
main table. They are displayed as text values - the keyed values
description. I need a parameter query to return a range of these
values. The parameter input won't accept the 'text' version. You can
do it with 'Find' because you can select 'as formatted'. But I need a
range. Please, any guidance would be appreciated.

Long Version:
Background:
I have 2 LU tables 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 2 Hardiness Zone fields
in the Plants TBL - one for the plants most 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 fields 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:
Zone LU Table
PrimaryKey
ZoneValue Zone Description
(number-single) (text)
9.0 9
9.25 9A
9.75 9B
10.25 10A
etc. etc.

Plants Table
Primary Key ZoneNorth ZoneSouth
PlantID ZoneLU Combobx [same]
(auto-increment) stored as number [same]
displayed as Text [same]
5 stored: 9.0
displayed: 9
6 stored: 9.25
displayed:9A
Only Column 'ZDescpt' 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
displayed 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. If the 'Find' feature allows it, it must be
possible thru some other route. I can write simple code
fairly well if needed, I just can't seem to think through this.

Once I fathom how to do this, I'll probably have it display in a
datasheet form and/or a report.

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

Thanks,
Anne

[DFS]

Try using the DLookup function:
SELECT *
FROM Table
WHERE ZoneValue >= DLookup("ZoneVa lue","ZoneLUTab le","ZoneDescri ption = '" &
Forms.FormName. FindBox & "'")

"Betrock" <Be*****@yahoo. com> wrote in message
news:7a******** *************** ***@posting.goo gle.com...

This is probably very simple, but I just can't see my way thru it.....

Short version: keyed values(numeric) in a lookup table are stored in a
main table. They are displayed as text values - the keyed values
description. I need a parameter query to return a range of these
values. The parameter input won't accept the 'text' version. You can
do it with 'Find' because you can select 'as formatted'. But I need a
range. Please, any guidance would be appreciated.

Long Version:
Background:
I have 2 LU tables 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 2 Hardiness Zone fields
in the Plants TBL - one for the plants most 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 fields 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:
Zone LU Table
PrimaryKey
ZoneValue Zone Description
(number-single) (text)
9.0 9
9.25 9A
9.75 9B
10.25 10A
etc. etc.

Plants Table
Primary Key ZoneNorth ZoneSouth
PlantID ZoneLU Combobx [same]
(auto-increment) stored as number [same]
displayed as Text [same]
5 stored: 9.0
displayed: 9
6 stored: 9.25
displayed:9A
Only Column 'ZDescpt' 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
displayed 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. If the 'Find' feature allows it, it must be
possible thru some other route. I can write simple code
fairly well if needed, I just can't seem to think through this.

Once I fathom how to do this, I'll probably have it display in a
datasheet form and/or a report.

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

Thanks,
Anne

[Betrock]

Thanks, I'll try it. Completely forgot about DLookup. Too many
things swirling around in very limited brain! Thanks again, Anne
"DFS" <no****@nospam. com> wrote in message news:<10******* ******@corp.sup ernews.com>...

Try using the DLookup function:
SELECT *
FROM Table
WHERE ZoneValue >= DLookup("ZoneVa lue","ZoneLUTab le","ZoneDescri ption = '" &
Forms.FormName. FindBox & "'")