Re: Queries and Parameters

Lyle Fairfield wrote:
On Feb 26, 7:33 pm, Joe <joerai...@xxxxxxxxxxx> wrote:
Lyle Fairfield wrote:
On Feb 25, 6:50 pm, Joe <joerai...@xxxxxxxxxxx> wrote:
Hi, I've lurked for a bit and certainly hope you folks can be as much
help to me as you have been to others.
My problem is basically trying to query several results in a single
field ("or" type) but I would like to do so as a parameter so it prompts
me when I open the report.
I am working with MS Access 2000.
Here is my situation.
I am dealing with a database that has 400+ records. It is of accounts
that I have to visit (testing of fire systems) at various times of the year.
Some accounts I test once a year and some monthly with several
variations in between. In addition to keeping the information on each
account up to date and easily sending reports up the chain showing the
progress for the year I also use it to give me a list of accounts coming
up for test the next month.
I have a field called test interval (not visible in any reports) and use
codes such as A03 (annual test in march), S03 (semi-annual starting in
march and again six months later in Sep.) Q03 (quarterly in march, june,
sep and dec).... You get the idea I also use T## for three times a year,
B(1 or 2) for bi-monthly(every other month) and M for monthly
This way I have been using a basic query and calling up my report each
month. For example, to see the tests I have to do in October I have to
modify my existing query with "test interval" to equal M, A10, S04, Q01,
T02 and B2. Not a huge pain in the *** but it is cumbersome and I have
to go in every month and modify it.
I have recently started using parameters and think it would be
marginally easier to fill out prompts upon opening the report. I tried
a "=[x]" on several lines but it still only prompted me once. I tried
using a 'getparameter' statement which I heard of but that did not work
(no claim that I did it right).
Is there a way to get the multiple prompts?
OR
Can I make 12 queries and have the report prompt me for which query to
use when I open it?
OR
Am I going about this like a lunkhead and everyone reading this is
seeing a better way of doing this entire process? I have no problems
adding new fields to my table if it ends up being more efficient.
Thanks for your time.
Joe
OTTOMH (while DSAPWWMITMOTN) perhaps you could make a table of months
and a table that linked codes to the tables of months. M, A10, S04,
Q01,
T02 and B2 would all map to October. B2 would link to Feb, Apr etc. M
would link to all months. So a query using a join could take a single
parameter, October, and return all the accounts requiring an
inspection for that month.
Is doing a fire inspection challenging?
If not then, IMO, those up the chain are wasting a resource (you).
DSAPWWMITMOTN = dog sitting a pup who woke me in the middle of the
night
I work for an alarm company, and no, fire Inspections are not all that
challenging. I used to work in the installation department. Most fire
inspectors just test the systems. They are trying something different
with me - I test also, but if anything doesn't work I fix it on the spot
rather than report it for someone else to fix later. I have also
spotted quite a few problems with systems that the other testers never
noticed because they didn't have the depth of experience that a former
installer has. I also test/repair Burglar Alarms, card access systems
and Camera systems. The other testers work on one type of system.

The most challenging part is trying to convince the guy running the
department to use computers. He doesn't like them (and he's younger
than me - I'm 45). Maybe, If I can show this database saves time...

Thanks for your idea, that sounds like the simplest solution yet. Some
of the other ideas sound good, but are a bit more complicated than I
feel comfortable with. I am saving them for possible future use.

Thanks to everyone for your time and ideas.

Joe- Hide quoted text -

- Show quoted text -

There are many developers, programmers, database administrators who
post here, who seem misplaced. They cannot express their ideas. Their
understanding of their subject is minimal. They're lazy. They have no
sense of logic. And not a few are just plain stupid. Sometimes they
mention their "clients" and I shudder.

I'm happy that someone who seems capable is testing fire alarms. I'm
sure it's a worthwhile occupation and it's a job that needs doing
well. Probably you're happy in your work. But you could venture into
this computer world. Many of us did so when we were your age. Mind
you, this happened just when personal computers became common, so you
it was a special opportunity for our generation.

Years ago I showed a secretary how to write a function in Excel. She
was hooked. She took some classes. She got a certificate. She worked
for People Soft. She left them and worked for an insurance company.
Her salary got to be double mine. Now she free lances. She's happy,
challenged, respected and well off. She was about forty when she
began.

Probably you're completely happy in what you're doing. But you could
think about what I said.





Thanks for the encouragement but for now I think I'll stay put. I made the move into inspecting fire alarms instead of installing them after my oldest son (18) was killed in an early morning house fire. The house he was in did not have working smoke detectors - so its a bit personal.

As far as computers go, I like to play around and use the different programs in the manner the programmers intended by taking advantage of all the bells and whistles.

I have had the opportunity a few times in the past to make this sort of thing my primary job (in the military) but that has always taken the fun out of it.
.