Multiple Query Criteria ... kind of ...

[JPeters]

Hey all,

I've been doing some searching and I can't find this because I can't really word it correctly in the search box in advanced search...

I'm trying to run a complicated query... but I can't figure out how to do it. Maybe someone can point me in the right direction .... I've got these charts in forms (graphs) that are run off of queries. The chart runs a simple SQL string that calls the data from a query that was created some 3 years ago by my boss. Chart wizard created the graph and SQL string.

The query has 3 fields. 1 Month, 1 year, and 1 Average of some aggregate data that is plotted over time on this graph.
Months are on the bottom, the lines on the graph are labeled via year. And the data is charted overtime along these months per each year. Well, we're running into a small snag - it's not too big of a deal, but a snag nonetheless.

The graphs look great, data is populated fine, HOWEVER ... when we get to the current month of the current year the data isn't full (not inputted / not complete), so the line for the current year PLUMMITS at the current month. It looks bad and inaccurate. We're trying to end the data of the graph to the previous month's end - with a complete month of data.

Remember how I told you that we have both Year and Month fields? Well, I've tried running a criteria that says under year &quot;<> Year(Date())&quot; and that gets rid of the entire year data, obvioulsy. I've tried under Month &quot;<>Month(Date())&quot; ... that gets rid of every month of August for all years plotted on the graph - I only want this year. I've tried these both, thinking maybe they'd combine by some mystical force ... of course not, I lose both the year 2002, and the 8th month for all 3 years graphed. I've tried so many different combinations, but I can't find a way to tell it to not graph the CURRENT month for the current year's data.

Any clues or ideas?

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[jebry]

Hi Josh!

What you have should work if you are using Or logic. If Year <> 2002 Or Month <> 8 should eliminate the current month's information. In QBE, this means putting the criteria on different criteria lines.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[cmmrfrds]

Assuming all your other where criteria is working correctly and you want less than the date in question, can you not just add an &quot;and yourdate < '08/01/2002'

where((your criteria ) and yourdate < '08/01/2002')

 

[JPeters]

Hi all,

I got it figured out, I ended up just using SQL to flag records of current year and current month, and if both were flagged they got an extra flag, meaning that the current mo/yr had this third flag only. And if this third flag exists, don't graph that data. Pretty simple actually, I just had to THINK about it. Damn brain. ;-)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------