Martijn & Kevin,

How about:

H.Yr between 2001 and 2002 /*to use indices */
and ((H.Yr*100+H.mo) between 200108 and 200207) /* to narrow it down
further*/

yr must be a positive integer.
mo must be a positive integer < 100.

This way you get use of indices on yr if available. Less fuss than a UDF.

HIH

Jason Chapman
JAC2 Consultancy

Training - Development - Consultancy
Delphi, InterBase, Firebird, OOAD, Development lifecycle assistance,
Troubleshooting projects, QA.....
www: When I get round to it....
Mob: (+44) 07966 211 959 (preferred)
Tel: (+44) 01928 751088




""Martijn Tonies"" <m.tonies@...> wrote in message
news:000501c249f3$b56dd5b0$0a02a8c0@seal...

> Hi Kevin,
>
>
> > I have a history table with two fields: yr & mo (year and month)

defined

> as
> > small ints.
> >
> > Possibly have a design problem here.
> >
> > When running a report, a user wants to select the date range from 8/1/01
> > thru 7/31/2002.
> >
> > I've been trying various Selects but can't quite get it.
> >
> > The below does not select anything:
>
> Well that makes sense... (see below)
>
> > SELECT
> > H.CUSTNO, H.CUSTNAME,
> > SUM ( H.SALESAMT ) AS SALESAMT, SUM ( H.GROSSMARGIN ) AS GROSSMARGIN
> > FROM CUST_HIST H
> > WHERE
> > H.COMPANY = '01' AND DIVISION = '01'
> > and ((H.Yr >= 2001 and H.mo >=8) and (H.Yr <= 2002 and H.mo <=7))
>
> How can a H.Mo be >=8 and <=7 at the same time? Or at least, that's what
> I think is happening... Reading all your parenthesis makes be do this:
>
> and ( ( A and B ) and ( C and D ) )
>
> which, I believe, can be written as:
>
> and A and B and C and D
>
> And there you have it - (B and D) will never return True.
>
>
> ok - multiple possibilities here...
>
> One thing could be to adjust the table in such a way that it stores
> full dates - this way you could simply say:
>
> H.DateField > '2001/08/01' and H.DateField < '2002/07/01'
>
> That sure is the option I would go for.
>
> Can you change the table or is this a no-go?
>
> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase and Firebird
> http://www.interbaseworkbench.com
>
> Upscene Productions
> http://www.upscene.com
>
> "This is an object-oriented system.
> If we change anything, the users object."
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>