Create new field group from range of vaules in another field.

[Guest]

I really need some help with this! I have a table with a field called
[days.out]. It contains values from 1 to about 350, non sequencial with
duplicates. I would like to run a query that would create a new field called
[days.grouped]. This field would contain the word "1-15" if the value was
between 1 and 15 in field [days.out] and "15-30" if value was between 15 and
30 in field [days.out], etc....

Hope someone can help me!!

 

[Guest]

In your query, put the following field

days.grouped:IIf([days_out]>=1 And [days_out]<=15,"1 -
15",IIf([days_out]>=16 And [days_out]<=30,"16 - 30",0))

just keep adding a new IIF where the 0 is, altering the >= and <= amounts.
However there is a limit to how many chars and how many 'nested' iif stms you
can have Can't remember off the top of my head but is in the help menu) so I
usually break it down into several intermediate fields. Hope this helps you
get started.

 

[Guest]

Thanks so much for your help, I will give it a try. Where do I put the
statement in my query?? In the Field: or the Criteria: area?

In your query, put the following field

days.grouped:IIf([days_out]>=1 And [days_out]<=15,"1 -
15",IIf([days_out]>=16 And [days_out]<=30,"16 - 30",0))

just keep adding a new IIF where the 0 is, altering the >= and <= amounts.
However there is a limit to how many chars and how many 'nested' iif stms you
can have Can't remember off the top of my head but is in the help menu) so I
usually break it down into several intermediate fields. Hope this helps you
get started.

I really need some help with this! I have a table with a field called
[days.out]. It contains values from 1 to about 350, non sequencial with
duplicates. I would like to run a query that would create a new field called
[days.grouped]. This field would contain the word "1-15" if the value was
between 1 and 15 in field [days.out] and "15-30" if value was between 15 and
30 in field [days.out], etc....

Hope someone can help me!!

 

[Guest]

Hi Shawn - you put it in the Field: area. You would use the Criteria: if you
were doing things like selecting only the records between 1 and 15, or 16 to
30, etc. Criteria is a filtering mechanism. Give a shout if you get stuck
with the IIF statement, I have done a few in my time and use it in many
locations just as you are - to display text dependent on something in another
field.
Remember - you can also use your IIF statements on forms or reports to do
the same thing - print text depending on what a field contains so you might
not do it in the query at all.
Hope this makes sense!

Yours - Dika

Thanks so much for your help, I will give it a try. Where do I put the
statement in my query?? In the Field: or the Criteria: area?

In your query, put the following field

days.grouped:IIf([days_out]>=1 And [days_out]<=15,"1 -
15",IIf([days_out]>=16 And [days_out]<=30,"16 - 30",0))

just keep adding a new IIF where the 0 is, altering the >= and <= amounts.
However there is a limit to how many chars and how many 'nested' iif stms you
can have Can't remember off the top of my head but is in the help menu) so I
usually break it down into several intermediate fields. Hope this helps you
get started.

I really need some help with this! I have a table with a field called
[days.out]. It contains values from 1 to about 350, non sequencial with
duplicates. I would like to run a query that would create a new field called
[days.grouped]. This field would contain the word "1-15" if the value was
between 1 and 15 in field [days.out] and "15-30" if value was between 15 and
30 in field [days.out], etc....

Hope someone can help me!!