Query Age range

[amy]

Hi to Everyone:
I need big help on how to query the Age range.
Age field is text data type, Age are from 0 wk, 1 wk, 2wk.....up to 15
wk. Try to set up query in Query desing mode with criteria is 0-4wk, i
put in this: "0 wk" Or "1 wk" Or "2 wk" Or "3 wk" Or "4 wk". The query
result includes ages between1-4wk but also10-15 wk. Even I change the
Age field to number data type, still the same problem.

I am not a access programmer but just an end user. Need help to solve
the problem

[tina]

Re: Query Age range

is the data in the Age field *just* the numbers? or does it include the
space and the letters wk?

hth


"amy" <amyguang@yahoo .com> wrote in message
news:1145675090 .669646.166670@ v46g2000cwv.goo glegroups.com.. .[color=blue]
> Hi to Everyone:
> I need big help on how to query the Age range.
> Age field is text data type, Age are from 0 wk, 1 wk, 2wk.....up to 15
> wk. Try to set up query in Query desing mode with criteria is 0-4wk, i
> put in this: "0 wk" Or "1 wk" Or "2 wk" Or "3 wk" Or "4 wk". The query
> result includes ages between1-4wk but also10-15 wk. Even I change the
> Age field to number data type, still the same problem.
>
> I am not a access programmer but just an end user. Need help to solve
> the problem
>[/color]

[John Mishefske]

Re: Query Age range

amy wrote:[color=blue]
> Hi to Everyone:
> I need big help on how to query the Age range.
> Age field is text data type, Age are from 0 wk, 1 wk, 2wk.....up to 15
> wk. Try to set up query in Query desing mode with criteria is 0-4wk, i
> put in this: "0 wk" Or "1 wk" Or "2 wk" Or "3 wk" Or "4 wk". The query
> result includes ages between1-4wk but also10-15 wk. Even I change the
> Age field to number data type, still the same problem.[/color]

Why include the measurement type in the field (i.e. "wk")? You should just be storing the
quantity/amount. You can always append the " wk" later in the form/report before the user
sees it.

Create another table that defines your ranges:

AgeRange:
- ID (AutoNumber)
- MinAge (Number - Integer)
- MaxAge (Number - Integer)
- Title (Text(25))

and populate with your range values
0,4, "0 wk - 4 wk"
4,6, "4 wk - 6 wk"
6,8, "6 wk - 8 wk"
8,10, "8 wk - 10 wk"
10,15, "10 wk - 15 wk"

or whatever your ranges are. You can then join to this table and get
a range definition ([Title]) back.

The SQL would look something like this:

SELECT yourTable.Age, yourTable.*, AgeRange.Title
FROM yourTable INNER JOIN AgeRange ON (yourTable.Age < AgeRange.MaxAge ) AND
(yourTable.Age => AgeRange.MinAge );

The Titles for the ranges are wrong (in this example an Age of 4 is assigned "4 wk - 6 wk"
and no entries in "0 wk - 4 wk" would actually equal 4) but this is quick air code to give
you some ideas on how to implement a flexible range solution in SQL.

--
'---------------
'John Mishefske
'---------------

[amy]

Re: Query Age range

Data in AGE field is number+space+wk . like this: 4 wk

Can I do the age query in access 2000 query design mode without
writting any sql code, because i don't know how and where to put the
code in.

thanks

amy

[tina]

Re: Query Age range

yes, you can "fix" the query in Design view, with making changes in SQL
View. open the query in Design view, and create a calculated field by typing
the following in the first blank column in the design grid, as

WeekNum: Val([Age])

the Val() function extracts the numeric value from the data in the Age
field. to return records where the week is 1 or 2 or 3 or 4, add the
following criteria to the WeekNum field in the query design grid, as

Between 1 And 4

recommend you consider updating the data in the Age field of the table to
the week number only, without the additional text, and changing the field's
data type from Text to Number, with Field Size of Byte. then you can query
the field directly, without having to use a calculated field as a stand-in.

hth


"amy" <amyguang@yahoo .com> wrote in message
news:1145731397 .462158.3120@i3 9g2000cwa.googl egroups.com...[color=blue]
> Data in AGE field is number+space+wk . like this: 4 wk
>
> Can I do the age query in access 2000 query design mode without
> writting any sql code, because i don't know how and where to put the
> code in.
>
> thanks
>
> amy
>[/color]