Re: How To Steamline My Queries

From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 02/17/05 

Date: Thu, 17 Feb 2005 09:47:28 +0200

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 wrote:
> 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?

Quantcast