Report for year to date

[michaelberrier]

I'm filtering a query for this year's records only and producing a
report. The report shows the proper records until I close it the first
time then it loses the filter. How can I make that filter stick or at
least recall it without having to do the report over again?

Thanks to all.

 

[Wayne-I-M]

Hi

Have a lok at the answer given by Arvin Meyer

http://groups.google.com/group/comp...5/fc6db4a36fc56305?lnk=st&q=#fc6db4a36fc56305

There are many ways to do what you want but I would not hard ocde it (don't
stipulate the year eg 2008 or 2009) or will have to do it again next year.

 

[aaron.kempf]

MDX supports a _LOT_ of functionality like this.

MDX is basically SQL on steroids.

MDX has a plethora of functions like YTD, etc

-Aaron

 

[Wayne-I-M]

Hi Arron

You make a valid point but as this is an access forumn and the question
asked refers to access it may be seen as slightly unhelpful to point out hat
another programme has a facility to automate a process without the need to a
formula.

I use sql for web based databases (most of the time) and this has the
ability to transfer data OnChange of an on-line form, but I also use access
(where it out performs sql in ease of use - as an example with asp formated
forms) and for that I need to write a small bit of code to import that data
to make it available off-line.

This is the same as your answer - one programme can do some things and
others do other things but - in this case - I would have hoped that someone
with your obvious skill could have been slightly more helpful to someone
askin a genuine question.

My mother always told me that if you have nothing nice or helpful to say.
Say nothing. It costs nothing. To be polite.

 

[aaron.kempf]

I'll try better. Just had to give my 2 cents

I just had to throw it out-- We have something in the neighborhood of
_200_ custom complex date formulas for dates-- along the lines of YTD,
etc-- and I'm just consistently amazed by MDX.

-Aaron

 

[Wayne-I-M]

I think maybe you are misundertanding me - sorry but for me english is still
difficult -. I have read some of your posts and, when you want to, you do
give very insightful and knowledgeable answers that I am sure questionaire's
really appreciate. You obvously have a good and deep knowledge of your
particular lines of programmes and I think that everyone will understand it
if you ( as I and everyone else does) have a bias towards your particular
favorite.

But, just becasue someone does not agree with you or your preference
towards which programme to work with does not make them any less (or more)
skillful. It simply means that you prefer one method and they prefer another.

There are also costs to think about. If you work for a large company (as I
do) you can simple install whatever programme you feel will do the specific
job the best (with you skill level). But if you are working in a small
company, non-profit setup, self employed, etc you don't have this luxuary and
you simply have to try and make what you have got do the job. I think this
is the basis of many question here in this forum. There are also many cases
where people are just thrown in at the deep end. Here you fix that database
- you know what I mean. It happens in many companies.

I know that someone with your skill level would be able to make my job much
simpler if I could just call you and ask when I have a problem with sql, but
obviously that is not the way forumns work. You put out a question and hope
that some will, free of charge, help.

So, this is the main point. People are just asking for help or in some
cases clarification. I am "not" an access expert like many people here (in
fact I give some really dumb answers now and again) but I do try and help
where possible if I happen to know the answer to a question. But - I don't
point out other people's failings for not knowing the answer - or for not
using my preference of programme.

If I am an expert in any programme I would ay it's correl. I have never met
anyone who can use it as well as I do - thats not being big headed as I'm
sure there are many, many people out that I just have not met. But most
people will say that Photoshop is better - I disagree for many reasns but now
is not the time or place for that. But I would not hassle people who ask
questions in the photoshop forumn by telling that they should stop using
photoshoop and go over to corel. What would be the point.

We all know that a way for some to be noticed is to be really good at what
they do - but there is an easier way (again which we all know) and that is to
be really bad. You still get noticed but for different reasons.

You obviouly like writting in forumns so can I just suggest that befor you
post anything you ask yourself would Wayne's mum think you were being nice or
helpful. If yes then go for it. If not then pick another quesiton to answer.

Good luck Arron.

 

[Tony Toews [MVP]]

I think maybe you are misundertanding me - sorry but for me english is still
difficult -.

Wayne

I would never have known English isn't your first language. In all the time I've
been reading your postings that had never crossed my mind.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[aaron.kempf]

Wow.. I'd give anything to know more about graphics.. I really think
that would be incredibly practical for me

-Aaron

 

[Bob Quintal]

m:

I'll try better. Just had to give my 2 cents

Aaron,

please take your two cents, and go away.
Don't go away angry, just go away.

 

[Albert D. Kallal]

I'll try better. Just had to give my 2 cents

The problem is you did not even give 2 cents. You did not evaluate and
suggest if the technology was appropriate here (is it????). If the
suggestion
does not include intellectual thinking on your part as to how appropriate
the
MDX technology here is, then what help is it? You might as well tell me that
you like Motorcycles.

Perhaps if you mentioned there is a free edition of MDX, or perhaps a
personal use edition at low cost? And, how much does the product cost?
And, EVEN MORE important, can we use it with ms-access?
Again, you failed to make this thinking effort in any of these
regards here?

We can make your nit pic attitude go both ways here, and every single
post you make, we can nit pic about something in it.

This whole issue centers around YOUR ATTITUDE, and are you actually posting
here because you generally respect, and appreciate the ms-access community.
Are you posting here because you want to give back to this community, and
do you want to be a active contributor to this community?

Or, are you just posting here and trying to be a smart ass? There really is
a big difference, and virtually 99% of your posts are not really to help
people,
but simply ridicule them, make fun of them, try to attempt show how wrong
they
are, or simply a BAD attempt to show your smarter then them...

Really, your attitude is not winning you any friends here.....

I always appreciate people who want to contribute to this community, but at
least show that you really do care about using ms-access, and helping the
community here. In this regards, if your trying to help this community,
then I all on your side, and will support and respect your efforts to
help the community here. This is a community help place, not be an
Mr. snoty person place.

We all make mistakes, lots of our posts are not perfect, but we all here to
help each other. Got it...ok?

Try to be here for the community, and not just for your own personal ego
trip....

 

[Bob Quintal]

@e39g2000hsf.googlegroups.co
m:

Hey ya'll...remember me? I'm the guy who started all of this by
apparently asking the Hot Button Question of the day. I'd like to
thank Wayne who many posts ago offered some useful information by,
stragely enough, pointing me to an entirely different group.

I won't disagree that the one guy gave no help by pointing me an
entirley different program, but I can't say I got much from the
rest of you either.

Thanks, I think,
mb

Part of the problem is that Aaron's distractions serve only to
clutter up message threads so that people assume that the answer and
follow-ups are being responded to in a timely and efficient manner.

I apologise to have missed the fact that you don't seem to have been
given the right answers yet.

I've reread your original question, and I wonder how you are trying
to filter the report.

The usual ways to make a report are to build a query that includes
your parameters, which can be hard coded (so that they can be saved
in the query, or you use prompting parameters that pop up an input
box so the user can type in the relevant filter parameter at
runtime. You can also use a form to gather the filter parameters
then open the report from the form, with the filter applied via the
Docmd.OpenReport statement's WhereClause option.

Are you just opening the query, using the filter by form tool and
opening the report designer on this?

 

[michaelberrier]

Hey ya'll...remember me? I'm the guy who started all of this by
apparently asking the Hot Button Question of the day. I'd like to
thank Wayne who many posts ago offered some useful information by,
stragely enough, pointing me to an entirely different group.

I won't disagree that the one guy gave no help by pointing me an
entirley different program, but I can't say I got much from the rest
of you either.

Thanks, I think,
mb

 

[aaron_kempf]

Albert;

I don't need to explain myself.

I don't need to make friends. I need to educate the 'unclean'.

I just know that there are 'built in functions' that deal with YTD and
any other %TD you need..

Olap might cost some cash.. but www.olapreport.com - it's obvious to
me where all the MS DB Action is; and it's _NOT_ in MS Access.

I generally respect the Access community.
But I'd rather shit on my moms grave than to sit idly by when you guys
struggle with the basics. When MVPs around here spread _THREE_ lies
about SQL Server in a single thread-- I will no longer let you guys do
that.

I mean-- ADP solves -- 90% -- of the questions that you guys have to
help NooB with.
Is it my problem that everything that you guys 'help people with'
would go away if you guys were 'scared of SQL Server'?

I just find it funny.. 'oh, SQL Server is _SO_ much more complex'-- I
don't buy it.

SQL Server -- relational- doesnt' cost a cent; and it hasn't for a
decade... If you do it correctly.

A single proc desktop with decent hard drives and quad core-- can
easily push enough data around for most medium sized businesses..
without spending a _PENNY_ on SQL Server.

I just don't think that you guys 'nit-pick'.

90% of the posts I see are hostile-- most of you guys say that I say
'incorrect things about SQL Server'-- and I dont' think that I've
_EVER_ said anything false about SQL Server.

I'm not positive that I've said anything false about MDB / ACCDB...
But i'm not willing to promise that I won't lie cheat and steal to
discredit MDB / ACCDB.

Because you guys do the same thing about SQL Server.

And saying things like 'I lied about SQL Server' are just not true.

When you guys become _TOLERANT_ of _ADO_ and _TOLERANT_ of _ADP_ and
_TOLERANT_ of SQL Server... then maybe I'll relax a little bit.

As it is; most people say crap like 'oh, but it's just too complex'.

ALL FLAVORS OF SQL SERVER ARE CONSIDERABLY _LESS_ COMPLEX THAN
ANYTHING IN MDB / ACCDB.

You guys have the mental capacity to be successful in the db world.
To me-- that means leaving MDB / ACCDB where it belongs-- the recycle
bin.

I just don't think that you guys have really evaluated SQL Server..
and I'd like to know _WHY_.

_WHY_ do you guys claim that it is 'super-dooper complex'?

It is _SUPERIOR_ in every every every every feature and fashion.

Sorry-- but those are the facts.

With Access being dumbed down to not allow for replication-- or
security-- I thinkt hat you guys should open your minds to people with
different opinions--

Instead of just blindly attack them with FUD.

Most MVPs around here are blatantly hostile to ADO.
Most MVPs around here are blatantly hostile to SQL Server -- even
though it is free, simple, better, bigger, faster _AND_ more efficient
(does MDB support Dual-Core _AT_ALL_? ).
Most MVPs around here are blatantly ignorant to SQL Server.

I don't need to be nice and peachy--
Dorks like you guys--- that are stuck in the 90s-- are why most of the
real jobs are getting outsourced to India.

When you guys can start meeting the DB needs of _SMALL_ _OR_ _MEDIUM_
businesses-- then maybe i'll lay off.
If Microsoft increased the limit of ACCDB to 10gb-- then maybe I'd lay
off.

But moving from one POS cry baby database (MDB) to another (ACCDB)--
is not going to help anything.

And adding 12 layers of SharePoint junk-- that's not going to help
either.

Thanks & Good luck in seeing the light

-Aaron
www.microsoft.com/sql

 

[aaron_kempf]

Bob;

I don't need to go away.

You just need to graduate from the 'first grade of the db world'.

-Aaron

 

[Wayne-I-M]

Hi Michael

Soory but the question seems to have been forgoted.

There are a number of ways you can filter a report for the YTD. As an
example you could use a form button on click event to pass the filter -
something like
Year([ReportDate])=me.SomeTextBoxName (athough in this case you may be
better using a ..... format([datefield],"yyyy").....in a calculated column in
the querry as then you could use the after update of a text box as the
filter, which may be simpler for a user to operate.

To get you on your way - hopefully - you could try using a simple prompt (in
the query the report in based on) like this

Create a caulculated column in the query like this
SelectYear: DatePart("yyyy",[TableName]![DateField])
In the criteria row of this column put this
[Select a year]


Good luck