Queries and Parameters

[Joe]

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 butt 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

[Allen Browne]

To figure out the best data structure involves handling all the weird stuff
that happens in the real world, so can I ask a sideways question? What do yo
want to do when an inspection is late? If it is supposed to be inspected
monthly, but no-one gets there for a week (e.g. because of a disaster
elsewhere), is it then due:
a) a month from the last inspection, or
b) in 3 weeks, becuase you need to stay on the original schedule.

If (a), I suggest you use 2 fields rather than your T99 Field:
Freq Number (Long)
PeriodType Text (4 characters)
The PeriodType will be a combo listing of the characters that work in
DateDiff():
"d";"m";"q";"yyyy"
You can therefore determine when the next inspection is due by looking up
the last inspection, and adding the period like this:
DateAdd([PeriodType], [Freq], [LastInspection])

If (b), things are a bit more involved. You need a table of numbers and a
cartesian product which gives you a list of all the dates when the
inspection is acually due, and a way to record which of those was actually
done in any particular inspection.

BTW, looking up the date of last inspection will probably involve a
subquery. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Joe" <jo*******@charter.netwrote in message
news:_g*************@newsfe02.lga...

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 butt 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

[tina]

well, assuming that you are also tracking the date when each test is
completed (in addition to when each test is due), i would probably have a
table of intervals, as

tblIntervals
intID (primary key)
intCode (one record each for A03, S03, Q03, etc)
intMonths (number of months for each interval; for instance, for A03 the
value would be 12, for S03 the value would be 6, etc.)

add a field to the Accounts table, to store the interval for each account.
the field will be a foreign key from tblIntervals.

now, i'll assume that you have a separate table (linked to the Accounts
table) to record each test visit for each account, including the date the
test is completed for that visit. create a query based on the AccountTests
table, the Accounts table, and tblIntervals. pull into the query grid the
test date from the AccountTests table, the interval field from the Accounts
table, and the intMonths field from tblIntervals. create a calculated field
in the query to add the number of interval months to the test date, as

NextTestDate: Format(DateAdd("m", tblIntervals.intMonth,
AccountTests.TestDate), "mmyy")

the above expression goes all on one line in the query, regardless of
line-wrap here. next, add criteria to the calculated query field to pull
only the records where the date is next month, as

Format(DateAdd("m", 1, Date()), "mmyy")

actually, i would make the query more dynamic by using a reference to a
control on a form, as the criteria. that way you can choose what future
month of tests you want to see, without having to change the query at all.
one way to do that is to add two textbox controls to a form, i'll call them
txtMonths and txtMonthYear. make txtMonthYear invisible, and set its'
ControlSource to

=Format(DateAdd("m",[txtMonths],Date()),"mmyy")

txtMonths should be visible, that's where you'll type the number of months
you want add to the current month. this is February, so to see tests due in
March, type a 1; to see tests due in April, type a 2, etc.

in the query, change the criteria from what i posted above, to a simple
control reference, as

[Forms]![MyForm]![txtMonthYear]

note that the form will have to be open when you run the query (or the
report bound to the query).

hth
"Joe" <jo*******@charter.netwrote in message
news:_g*************@newsfe02.lga...

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 butt 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

[Lyle Fairfield]

On Feb 25, 6:50 pm, Joe <[e-mail]:

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 butt 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

[Joe]

Lyle Fairfield wrote:

On Feb 25, 6:50 pm, Joe <[e-mail]:

>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 butt 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

[Lyle Fairfield]

On Feb 26, 7:33 pm, Joe <[e-mail]:

Lyle Fairfield wrote:

On Feb 25, 6:50 pm, Joe <[e-mail]:

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 butt 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.

[Joe]

Lyle Fairfield wrote:

On Feb 26, 7:33 pm, Joe <[e-mail]:

>Lyle Fairfield wrote:

>>On Feb 25, 6:50 pm, Joe <[e-mail]:
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 butt 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.