selecting months in query

[flaviooooo]

Hey,

i have a query that contains 2 years, sorted by year - month:

2002 1
2002 2
2002 3
...
2003 1
...
You get the routine.

Now i need to select the months july - december of year 1, and january - june of year 2. If all months were in the query it would be simple, but sometimes a month isn't there (because there was no activity then, not a error in the query).
Any ideas on how to select the months 7 through 12 of year 2002 and 1 through 6 of year 2003?

Help ;-)

 

[xlbo]

If this is in Access, I would create an extra field (doesn't have to be shown) within the query, using the BUILD function

Formula would be

=right([year - month],len([year - month])-5)
Call this field "Month"

Create another field (also doesn't have to be shown) called Year
Formula for this would be

=left([year - month],4)

Then use 2 lines of criteria

Year Month
2003 >=1 and <=6
2002 >=7 and <=12

Criteria on 2 lines acts as an OR in the logic
Criteria on the same line acts like an AND
Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[flaviooooo]

Thanks for the quick help, but I see I have forgotten to mention an important thing; The problem is in Excel 97, and I can't get your solution to work with excel

Sorry :-(

greetz
flav

 

[xlbo]

How are you getting the data ??? MSQuery ???
Where is the base data held ?? Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[flaviooooo]

Indeed MSQuery.

The data is held in a dbase -file

 

[xlbo]

In that case, you can't build extra fields (well you can but it never seems to work for me)
I'd suggest building a year and month field onto the end of the table you are querying (on the dataabse side)

What I would do then is to build the query within the database (using the criteria mehtod I outlined before) and copy the SQL that is generated

Go into MSQuery and paste the copied SQL into the SQL editor there. You may need to delete the database references (dbo. etc) but apart form that, it should work fine Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde