Limit the amount of queries.

[paul123456]

Hi people. i have multiple reports that are the exact same that point to different queries to give different criterea. Is there a way i can convert my query and insert it into a module.. I would like to have a module with a my selections in critereas that i want.. and then use 1 report that calls the module for the records? is this possible? I would benifit from this greatly because i can limit the amount of duplicate reports i have and also i can password protect my queries so no one can change them. I am using to many different queries and reports for no reason it seems. Maybe if theres a way i can have a on record source procedure that calls the criterea info in a module?? is this possible to do?

Thanks, PAUL

 

[Steve101]

One way to achieve this is not to have any query at all associated with a report. Simply build the SQL string in the OnOpen event of the report; then assign it to the recordsource property; and presto, it all works.

Heres a simple example:

WhereClause = "WHERE Sales BETWEEN #1/1/1# and #3/1/1# "
sq = "SELECT * " & _
     "FROM tblYourTable " & _
     WhereClause
Me.Recordsource = sq

In the above simple example, you simply build the Whereclause programatically, apply it to the rest of the sql to build the entire SQL string, then apply this to the recordsource of the report.
Obviously your WhereClause would need to be a little more substancial than the example above; the point is that you can build it programmatically, any way you like, providing it produces valid SQL syntax, and provides access to the bound fields on the report.
You might also want to investigate the reports Filter and OrderBy related properties.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[paul123456]

How would i put a INNER jOIN statement in this code??

WhereClause = "WHERE (((ID.IDNum) Between [Enter start ID Number] And [Enter End IDNum]))"

sq = "Select Main.FirstName, Main.M, Main.LastName, ID.IDNum, ID.Date, etc, etc, etc"

From Client INNER JOIN ID on MAIN.Socail = ID.Socail
WHERE (((ID.IDNum) Between [Enter Start ID Number] And [Enter end ID Number])))

is there an order by that can go here?

WhereClause
Me.Recordsource = sq


is this even possible to do? the problem i think is having a form or button call the module and have the module open the report. because if i open the report i would have to make a copy of the report in order to put this on open staement in so it will work.

Thanks, PAUL

 

[Steve101]

Paul,

ANY SQL that you can generate using the graphics query designer can be cut and pasted into a string and manipulated by code ... providing it results in valid SQL syntax .... this of course includes INNER JOINS, Order By, Group By clauses etc.

If its valid SQL syntax, then you can generate it as part of the SQL string.

What I do a lot is generate a query graphically, then show its SQL, and transplant this into code, then convert this into a valid string by adding the quote symbols etc.

Hope this makes it clearer,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[paul123456]

So basically i can just open my query..go to view Then SQL and copy and paste that into my module? and use the DoCmd SQL " "? I think the problem i am having is trying to figure out were to transplant the quotes at..is there much i have to change in the sql code? is there some type of query convertor?

Thanks, PAUL

 

[Steve101]

Paul,
I've been away; hense no earlier response to your prior questions. Here are a couple of pointers which I think will help:

(a) Instead of building the variables into the query, using the [Enter Start Id Number] type syntax, use a form to prompt for your variables.

(b) Then when you build the SQL, you take the values of these form based controls and plant them directly into the sql you are building.

(c) Here's an example. I'm assuming that you have a form frmYourForm, and it has two text controls and a button. The text controls are called StartIdFrom and StartIdTo respectively, and the button is called btnRunReport. When clicked, the button opens a report called rptYourReport.

(d) When the form is opened, you simply enter the start and end id numbers into the corresponding controls.

(e) The code behind the Click event of the button is simply to open the rptYourReport report; ie.

docmd.OpenReport rptYourReport ,acViewPreview

(f) The SQL gets built by the report itself during its OnOpen event; you'd have code something like this:

sq = "Select Main.FirstName, Main.M, Main.LastName, " & _
     "       ID.IDNum, ID.Date, etc, etc, etc " & _
     "From Client INNER JOIN ID " *& _
     "on MAIN.Socail = ID.Socail " & _
     "WHERE ID.IDNum Between " Forms!frmYourForm!StartIdFrom & " " & _
     "AND " & Forms!frmYourForm!StartIdTo & " "

I guess the trick with all of this stuff is to understand the string concatenation which is occuring to construct the entire sql clause; also to recognise how the values of the form controls are being incorporated into the sql.

Hope this clarifies things a bit.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)