How To Steamline My Queries

[Sky Warren]

Hello To All,

I have to many queries and want to know how to reduce them by streamlining
the actions into a more practical event module. Here's my problem. I have 12
different queries which return anniversary dates. The 12 anniversary queries
are all called by the main form which uses MSysObjects with the Like
statement that grabs everything with Anniversary as part of it's file name
and loads them into a combo box. The user then selects one of them and gets
back all people who meet the selected criterior. Here's the code for the main
form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND ((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by number,
following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from 1
through 12 and generate a query based on their selection. That way, I could
eliminate all 12 anniversary queries. By the way, I have queries for
Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a programmer.
Anyone care to tackle this one?

 

[tina]

try changing one of your anniversary queries to:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Month(Clients.[Policy Date])) = Forms!MainFormName!ComboboxName))
ORDER BY DatePart("d",[Policy Date]);

substitute the correct form and control names, of course. instead of using a
combobox, you could simply use a textbox with a validation rule of
Between 1 and 12
and allow the user to enter the number for the month s/he wants to see. you
may want to consider continuing to use the combobox, though, with the names
of the months for the user to choose from in the droplist. to set that up,
change the combobox's RowSourceType to ValueList. enter the RowSource as
1;January;2;February;3;March;4;April (etc, etc, etc)
set the ColumnCount to 2, and the ColumnWidths to 0";1"
BoundColumn to 1, and the ListRows to 12

with either textbox or combobox solution, you could use a command button to
open the query, or use the control's AfterUpdate event to open it.

hth

Hello To All,

I have to many queries and want to know how to reduce them by streamlining
the actions into a more practical event module. Here's my problem. I have 12
different queries which return anniversary dates. The 12 anniversary queries
are all called by the main form which uses MSysObjects with the Like
statement that grabs everything with Anniversary as part of it's file name
and loads them into a combo box. The user then selects one of them and gets
back all people who meet the selected criterior. Here's the code for the main
form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND ((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by number,
following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from 1
through 12 and generate a query based on their selection. That way, I could
eliminate all 12 anniversary queries. By the way, I have queries for
Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a programmer.
Anyone care to tackle this one?

 

[Nikos Yannacopoulos]

Suppose you have a form called frmQueryParameters, with a listbox (or
combo) called lstMonth, rows 1,2,3...12, and one query:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (Month(Clients.[Policy Date]) = Forms!frmQueryParameters!lstMonth
ORDER BY DatePart("d",[Policy Date]);

Just select a month in the listbox (or combo), and the query will "read"
your selection from the from. Once you're happy with this working, just
add a command button on the same form, using the wizard, to open the
query for you! How better does it get?

HTH,
Nikos

 

[Sky Warren]

Thanks to Tina and Nikos,

I used code from both of you and it works really great! I only have one
small issue though. When I select more than one anniversary month only the
current month will display until I close that window. If I have the query for
say January already open, and then select February from the drop-down nothing
happens unless I close the query window for January first.

Is there a way to have the different months open in their own seperate
window? It's not a major issue if it can't be done, but it's nice to have
that option.

At any rate, the both of you have helped me tremendously and I can't thank
you enough. As a token of appreciation though, I have commented my forms by
giving credit to both you. That way should anyone ever read it, they will
know who the really smart people are and where to find them.

-Sky

 

[tina]

AFAIK, you can't open the same query multiple times concurrently. i'm not
sure, because i never let my user see a query - in a single-table query,
data is exposed to addition/modification/deletion, so i only expose data in
forms, where i can control what the user does to it.

if the purpose of opening your query is just to "look" at a dataset, rather
than "make changes" to it, one possible alternative is to create a report
bound to the query and have the user open the report instead. you can use
grouping in a report to handle one or multiple months of data, and you can
modify the form and query criteria to support that.

hth

Thanks to Tina and Nikos,

I used code from both of you and it works really great! I only have one
small issue though. When I select more than one anniversary month only the
current month will display until I close that window. If I have the query for
say January already open, and then select February from the drop-down nothing
happens unless I close the query window for January first.

Is there a way to have the different months open in their own seperate
window? It's not a major issue if it can't be done, but it's nice to have
that option.

At any rate, the both of you have helped me tremendously and I can't thank
you enough. As a token of appreciation though, I have commented my forms by
giving credit to both you. That way should anyone ever read it, they will
know who the really smart people are and where to find them.

-Sky

Hello To All,

I have to many queries and want to know how to reduce them by streamlining
the actions into a more practical event module. Here's my problem. I have 12
different queries which return anniversary dates. The 12 anniversary queries
are all called by the main form which uses MSysObjects with the Like
statement that grabs everything with Anniversary as part of it's file name
and loads them into a combo box. The user then selects one of them and gets
back all people who meet the selected criterior. Here's the code for the main
form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND ((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by number,
following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from 1
through 12 and generate a query based on their selection. That way, I could
eliminate all 12 anniversary queries. By the way, I have queries for
Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a programmer.
Anyone care to tackle this one?

 

[Sky Warren]

Hi Again Tina,

Yes, I only want the user to look at, not modify the data. I love your idea
but feel some major guilt about asking you for help setting it up. I don't
want to bind you to my problems and take up so much of your time. If you feel
compelled to plod forward with this though, I would have to find you and
personally bow down in the presense of everyone and worship the ground you
walk on

Thank you so very much for all you have already done!!!

-Sky

AFAIK, you can't open the same query multiple times concurrently. i'm not
sure, because i never let my user see a query - in a single-table query,
data is exposed to addition/modification/deletion, so i only expose data in
forms, where i can control what the user does to it.

if the purpose of opening your query is just to "look" at a dataset, rather
than "make changes" to it, one possible alternative is to create a report
bound to the query and have the user open the report instead. you can use
grouping in a report to handle one or multiple months of data, and you can
modify the form and query criteria to support that.

hth

Thanks to Tina and Nikos,

I used code from both of you and it works really great! I only have one
small issue though. When I select more than one anniversary month only the
current month will display until I close that window. If I have the query for
say January already open, and then select February from the drop-down nothing
happens unless I close the query window for January first.

Is there a way to have the different months open in their own seperate
window? It's not a major issue if it can't be done, but it's nice to have
that option.

At any rate, the both of you have helped me tremendously and I can't thank
you enough. As a token of appreciation though, I have commented my forms by
giving credit to both you. That way should anyone ever read it, they will
know who the really smart people are and where to find them.

-Sky

Hello To All,

I have to many queries and want to know how to reduce them by streamlining
the actions into a more practical event module. Here's my problem. I have 12
different queries which return anniversary dates. The 12 anniversary queries
are all called by the main form which uses MSysObjects with the Like
statement that grabs everything with Anniversary as part of it's file name
and loads them into a combo box. The user then selects one of them and gets
back all people who meet the selected criterior. Here's the code for the main
form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND ((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by number,
following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from 1
through 12 and generate a query based on their selection. That way, I could
eliminate all 12 anniversary queries. By the way, I have queries for
Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a programmer.
Anyone care to tackle this one?

 

[tina]

an easy way to get started, is to use the Reports wizard to build the
report. base the report on the query. the wizard will help you with
formatting and grouping.
in your form, add a second textbox (or combobox - whatever fits the solution
you already created) so the user can enter the "ending" month.
in the query, change the criteria to

WHERE (((Month(Clients.[Policy Date])) Between
Forms!MainFormName!ControlName And Forms!MainFormName!SecondControlName))

if you have any specific problem with the report, suggest you start a new
thread in microsoft.public.access.reports. that's a pretty high-traffic
newsgroup, with lots of experienced and expert users/MVPs to help you. (i
build reports, of course, but they're not my strongest point. much better
for you to get lots of good help, rather than just my input.)

hth

Hi Again Tina,

Yes, I only want the user to look at, not modify the data. I love your idea
but feel some major guilt about asking you for help setting it up. I don't
want to bind you to my problems and take up so much of your time. If you feel
compelled to plod forward with this though, I would have to find you and
personally bow down in the presense of everyone and worship the ground you
walk on

Thank you so very much for all you have already done!!!

-Sky

AFAIK, you can't open the same query multiple times concurrently. i'm not
sure, because i never let my user see a query - in a single-table query,
data is exposed to addition/modification/deletion, so i only expose data in
forms, where i can control what the user does to it.

if the purpose of opening your query is just to "look" at a dataset, rather
than "make changes" to it, one possible alternative is to create a report
bound to the query and have the user open the report instead. you can use
grouping in a report to handle one or multiple months of data, and you can
modify the form and query criteria to support that.

hth

Thanks to Tina and Nikos,

I used code from both of you and it works really great! I only have one
small issue though. When I select more than one anniversary month only the
current month will display until I close that window. If I have the

query
for

say January already open, and then select February from the drop-down nothing
happens unless I close the query window for January first.

Is there a way to have the different months open in their own seperate
window? It's not a major issue if it can't be done, but it's nice to have
that option.

At any rate, the both of you have helped me tremendously and I can't thank
you enough. As a token of appreciation though, I have commented my

forms
by

giving credit to both you. That way should anyone ever read it, they will
know who the really smart people are and where to find them.

-Sky

:

Hello To All,

I have to many queries and want to know how to reduce them by streamlining
the actions into a more practical event module. Here's my problem. I have 12
different queries which return anniversary dates. The 12 anniversary queries
are all called by the main form which uses MSysObjects with the Like
statement that grabs everything with Anniversary as part of it's

file
name

and loads them into a combo box. The user then selects one of them

and
gets

back all people who meet the selected criterior. Here's the code for

the
main

form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND ((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by number,
following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from 1
through 12 and generate a query based on their selection. That way,

I
could

eliminate all 12 anniversary queries. By the way, I have queries for
Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a programmer.
Anyone care to tackle this one?

 

[Nikos Yannacopoulos]

Sky,

Tina's suggestion to use a report instead is valid. Now, assuming you
have put your report together, I'll give you an idea to take the next
step. On the form's design, select the listbox and change its Multi
Select property (tab Other) to Simple. Next, base your report on a query
without any WHERE clause, so it would look something like:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
ORDER BY DatePart("d",[Policy Date]);

The idea is to use some simple VBA code to "read" the selections of
months in the listbox and apply the filter on the report upon opening
it. So, the code behind the button on the form will be something like:


For Each itm In Me.lstMonth.ItemsSelected
cdtn = cdtn & Me.lstMonth.ItemData(itm) & ","
Next
If Len(cdtn) = 0 Or IsNull(cdtn) Then
msg = "No month selected! Try again."
ttl = "Report Problem"
typ = vbExclamation
MsgBox msg, typ, ttl
Exit Sub
End If
cdtn = Left(cdtn, Len(cdtn) - 1)
whr = "Month(BDate) In (" & cdtn & ")"
For Each rpt In Reports
If rpt.Name = "MyReport" Then
DoCmd.Close acReport, rpt.Name
Exit For
End If
Next
DoCmd.OpenReport "MyReport", acViewPreview, , whr

(assumed: listbox name lstMonth, report name MyReport; change as required).

This code will open the report based on selection, close and reopen if
already open, or warn on no month selection.

Enjoy it!

Nikos

Thanks to Tina and Nikos,

I used code from both of you and it works really great! I only have one
small issue though. When I select more than one anniversary month only the
current month will display until I close that window. If I have the query for
say January already open, and then select February from the drop-down nothing
happens unless I close the query window for January first.

Is there a way to have the different months open in their own seperate
window? It's not a major issue if it can't be done, but it's nice to have
that option.

At any rate, the both of you have helped me tremendously and I can't thank
you enough. As a token of appreciation though, I have commented my forms by
giving credit to both you. That way should anyone ever read it, they will
know who the really smart people are and where to find them.

-Sky

:

Hello To All,

I have to many queries and want to know how to reduce them by streamlining
the actions into a more practical event module. Here's my problem. I have 12
different queries which return anniversary dates. The 12 anniversary queries
are all called by the main form which uses MSysObjects with the Like
statement that grabs everything with Anniversary as part of it's file name
and loads them into a combo box. The user then selects one of them and gets
back all people who meet the selected criterior. Here's the code for the main
form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND ((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by number,
following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from 1
through 12 and generate a query based on their selection. That way, I could
eliminate all 12 anniversary queries. By the way, I have queries for
Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a programmer.
Anyone care to tackle this one?

 

[Nikos Yannacopoulos]

Sorry, just realized there's a small hickup in the code - a wrong field
name. Substitute line 12 with:

whr = "Month([Policy Date]) In (" & cdtn & ")"

Nikos

Sky,

Tina's suggestion to use a report instead is valid. Now, assuming you
have put your report together, I'll give you an idea to take the next
step. On the form's design, select the listbox and change its Multi
Select property (tab Other) to Simple. Next, base your report on a query
without any WHERE clause, so it would look something like:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
ORDER BY DatePart("d",[Policy Date]);

The idea is to use some simple VBA code to "read" the selections of
months in the listbox and apply the filter on the report upon opening
it. So, the code behind the button on the form will be something like:


For Each itm In Me.lstMonth.ItemsSelected
cdtn = cdtn & Me.lstMonth.ItemData(itm) & ","
Next
If Len(cdtn) = 0 Or IsNull(cdtn) Then
msg = "No month selected! Try again."
ttl = "Report Problem"
typ = vbExclamation
MsgBox msg, typ, ttl
Exit Sub
End If
cdtn = Left(cdtn, Len(cdtn) - 1)
whr = "Month(BDate) In (" & cdtn & ")"
For Each rpt In Reports
If rpt.Name = "MyReport" Then
DoCmd.Close acReport, rpt.Name
Exit For
End If
Next
DoCmd.OpenReport "MyReport", acViewPreview, , whr

(assumed: listbox name lstMonth, report name MyReport; change as required).

This code will open the report based on selection, close and reopen if
already open, or warn on no month selection.

Enjoy it!

Nikos

Thanks to Tina and Nikos,

I used code from both of you and it works really great! I only have
one small issue though. When I select more than one anniversary month
only the current month will display until I close that window. If I
have the query for say January already open, and then select February
from the drop-down nothing happens unless I close the query window for
January first.

Is there a way to have the different months open in their own seperate
window? It's not a major issue if it can't be done, but it's nice to
have that option.

At any rate, the both of you have helped me tremendously and I can't
thank you enough. As a token of appreciation though, I have commented
my forms by giving credit to both you. That way should anyone ever
read it, they will know who the really smart people are and where to
find them.

-Sky

:

Hello To All,

I have to many queries and want to know how to reduce them by
streamlining the actions into a more practical event module. Here's
my problem. I have 12 different queries which return anniversary
dates. The 12 anniversary queries are all called by the main form
which uses MSysObjects with the Like statement that grabs everything
with Anniversary as part of it's file name and loads them into a
combo box. The user then selects one of them and gets back all people
who meet the selected criterior. Here's the code for the main form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND
((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by
number, following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from
1 through 12 and generate a query based on their selection. That way,
I could eliminate all 12 anniversary queries. By the way, I have
queries for Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a
programmer. Anyone care to tackle this one?

 

[Sky Warren]

Nikos,

Okay, everything works really great but I had to leave the Where line inside
the query because the form wasn't applying the filter in it's output. It's
almost perfect except the form still does not correctly apply the filtering.
I get the correct months from the drop-down (Jan, Feb, ect) but the DatePart
functions output does not get applied. The (Day of Week) rows are all
scattered about so instead of getting:

1/1/1990
1/1/2000
1/2/1992
1/2/1956

I instead get:

1/8/1970
1/13/1984
1/1/1998

After asking around a bit I've been told by a MVP that Access Reports do not
obey the Order By rule as queries do. Don't know if there's a workaround for
the problem or not. The MVP suggested toying around with the View, Sorting
and Grouping window but no luck, it's still not sorting correctly. It would
not be an issue if I had a small database, but for January alone my report is
7 pages deep.

Do you know if there is any kind of fix for the sorting problem?

Sorry, just realized there's a small hickup in the code - a wrong field
name. Substitute line 12 with:

whr = "Month([Policy Date]) In (" & cdtn & ")"

Nikos

Sky,

Tina's suggestion to use a report instead is valid. Now, assuming you
have put your report together, I'll give you an idea to take the next
step. On the form's design, select the listbox and change its Multi
Select property (tab Other) to Simple. Next, base your report on a query
without any WHERE clause, so it would look something like:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
ORDER BY DatePart("d",[Policy Date]);

The idea is to use some simple VBA code to "read" the selections of
months in the listbox and apply the filter on the report upon opening
it. So, the code behind the button on the form will be something like:


For Each itm In Me.lstMonth.ItemsSelected
cdtn = cdtn & Me.lstMonth.ItemData(itm) & ","
Next
If Len(cdtn) = 0 Or IsNull(cdtn) Then
msg = "No month selected! Try again."
ttl = "Report Problem"
typ = vbExclamation
MsgBox msg, typ, ttl
Exit Sub
End If
cdtn = Left(cdtn, Len(cdtn) - 1)
whr = "Month(BDate) In (" & cdtn & ")"
For Each rpt In Reports
If rpt.Name = "MyReport" Then
DoCmd.Close acReport, rpt.Name
Exit For
End If
Next
DoCmd.OpenReport "MyReport", acViewPreview, , whr

(assumed: listbox name lstMonth, report name MyReport; change as required).

This code will open the report based on selection, close and reopen if
already open, or warn on no month selection.

Enjoy it!

Nikos

Thanks to Tina and Nikos,

I used code from both of you and it works really great! I only have
one small issue though. When I select more than one anniversary month
only the current month will display until I close that window. If I
have the query for say January already open, and then select February
from the drop-down nothing happens unless I close the query window for
January first.

Is there a way to have the different months open in their own seperate
window? It's not a major issue if it can't be done, but it's nice to
have that option.

At any rate, the both of you have helped me tremendously and I can't
thank you enough. As a token of appreciation though, I have commented
my forms by giving credit to both you. That way should anyone ever
read it, they will know who the really smart people are and where to
find them.

-Sky

:


Hello To All,

I have to many queries and want to know how to reduce them by
streamlining the actions into a more practical event module. Here's
my problem. I have 12 different queries which return anniversary
dates. The 12 anniversary queries are all called by the main form
which uses MSysObjects with the Like statement that grabs everything
with Anniversary as part of it's file name and loads them into a
combo box. The user then selects one of them and gets back all people
who meet the selected criterior. Here's the code for the main form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND
((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by
number, following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from
1 through 12 and generate a query based on their selection. That way,
I could eliminate all 12 anniversary queries. By the way, I have
queries for Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a
programmer. Anyone care to tackle this one?

 

[Nikos Yannacopoulos]

Sky,

The suggestion you got on sorting within the report is correct. This
should solve your sorting problem.

I'm puzzled that the filtering doesn't work the way I suggested... I
tested it and it works just fine. Any cahnce you didn't substitute that
line I had the wrong field name in, so it still reads BDate instead of
[Policy Date]? That would certainly explain it... Open the form's module
and do a search on BDate to check.
The other thing is, if you are applying the filter in the query, then
you cannot exploit the multi-selection in the listbox (so, you clould
choose, say, Feb and Oct together), which was the reason why I applied
the filter on opening the report in the first place.

HTH,
Nikos

Nikos,

Okay, everything works really great but I had to leave the Where line inside
the query because the form wasn't applying the filter in it's output. It's
almost perfect except the form still does not correctly apply the filtering.
I get the correct months from the drop-down (Jan, Feb, ect) but the DatePart
functions output does not get applied. The (Day of Week) rows are all
scattered about so instead of getting:

1/1/1990
1/1/2000
1/2/1992
1/2/1956

I instead get:

1/8/1970
1/13/1984
1/1/1998

After asking around a bit I've been told by a MVP that Access Reports do not
obey the Order By rule as queries do. Don't know if there's a workaround for
the problem or not. The MVP suggested toying around with the View, Sorting
and Grouping window but no luck, it's still not sorting correctly. It would
not be an issue if I had a small database, but for January alone my report is
7 pages deep.

Do you know if there is any kind of fix for the sorting problem?


:

Sorry, just realized there's a small hickup in the code - a wrong field
name. Substitute line 12 with:

whr = "Month([Policy Date]) In (" & cdtn & ")"

Nikos

Sky,

Tina's suggestion to use a report instead is valid. Now, assuming you
have put your report together, I'll give you an idea to take the next
step. On the form's design, select the listbox and change its Multi
Select property (tab Other) to Simple. Next, base your report on a query
without any WHERE clause, so it would look something like:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
ORDER BY DatePart("d",[Policy Date]);

The idea is to use some simple VBA code to "read" the selections of
months in the listbox and apply the filter on the report upon opening
it. So, the code behind the button on the form will be something like:


For Each itm In Me.lstMonth.ItemsSelected
cdtn = cdtn & Me.lstMonth.ItemData(itm) & ","
Next
If Len(cdtn) = 0 Or IsNull(cdtn) Then
msg = "No month selected! Try again."
ttl = "Report Problem"
typ = vbExclamation
MsgBox msg, typ, ttl
Exit Sub
End If
cdtn = Left(cdtn, Len(cdtn) - 1)
whr = "Month(BDate) In (" & cdtn & ")"
For Each rpt In Reports
If rpt.Name = "MyReport" Then
DoCmd.Close acReport, rpt.Name
Exit For
End If
Next
DoCmd.OpenReport "MyReport", acViewPreview, , whr

(assumed: listbox name lstMonth, report name MyReport; change as required).

This code will open the report based on selection, close and reopen if
already open, or warn on no month selection.

Enjoy it!

Nikos

Sky Warren wrote:


Thanks to Tina and Nikos,

I used code from both of you and it works really great! I only have
one small issue though. When I select more than one anniversary month
only the current month will display until I close that window. If I
have the query for say January already open, and then select February
from the drop-down nothing happens unless I close the query window for
January first.

Is there a way to have the different months open in their own seperate
window? It's not a major issue if it can't be done, but it's nice to
have that option.

At any rate, the both of you have helped me tremendously and I can't
thank you enough. As a token of appreciation though, I have commented
my forms by giving credit to both you. That way should anyone ever
read it, they will know who the really smart people are and where to
find them.

-Sky

:



Hello To All,

I have to many queries and want to know how to reduce them by
streamlining the actions into a more practical event module. Here's
my problem. I have 12 different queries which return anniversary
dates. The 12 anniversary queries are all called by the main form
which uses MSysObjects with the Like statement that grabs everything
with Anniversary as part of it's file name and loads them into a
combo box. The user then selects one of them and gets back all people
who meet the selected criterior. Here's the code for the main form:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like "*Anniversary*") AND
((MsysObjects.Type)=5))
ORDER BY Val(MsysObjects.Name);

The 12 queries that return the anniversary dates only differ by
number, following are two examples for January and February:

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "1/*/*"))
ORDER BY DatePart("d",[Policy Date]);

SELECT Clients.[First Name], Clients.[Last Name], Clients.Address,
Clients.City, Clients.State, Clients.[Zip Code], Clients.[Policy Date]
FROM Clients
WHERE (((Clients.[Policy Date]) Like "2/*/*"))
ORDER BY DatePart("d",[Policy Date]);

What I would like to do is have the user type or select a number from
1 through 12 and generate a query based on their selection. That way,
I could eliminate all 12 anniversary queries. By the way, I have
queries for Birthdates too so that's about 24 queries I could eliminate.

I would think there's a way to do this, but I'm not much of a
programmer. Anyone care to tackle this one?