By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,272 Members | 1,045 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,272 IT Pros & Developers. It's quick & easy.

Query Age range

P: n/a
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

Apr 22 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
is the data in the Age field *just* the numbers? or does it include the
space and the letters wk?

hth
"amy" <am******@yahoo.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
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

Apr 22 '06 #2

P: n/a
amy wrote:
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.


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
'---------------
Apr 22 '06 #3

P: n/a
amy
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

Apr 22 '06 #4

P: n/a
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" <am******@yahoo.com> wrote in message
news:11********************@i39g2000cwa.googlegrou ps.com...
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

Apr 22 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.