generating sql

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Afternoon all!

I have code behind a form that generates strWhere - a where clause that
is later applied to a report based on a query...

'preview report
DoCmd.OpenReport "rptGenRpt", acPreview, , strWhere

i step through the code, and immediatley before this point i grab the
string strwhere from th eimmediate window.

if i add the where clause as the codehas built it to the query and run
the query, my results return fine.

when the code continues, and executes the above line i am instead
prompted for the parameter value of <table>.<column>
(tblEqualOps.EthnicOrig).

Ca anyone help with this?

cheers
 
How exactly is strWhere populated? I don't understand what you mean by
"grabbing it from the immediate window" - where is the data coming from to
populate the sql? From a control on the form?
 
Hi SusanV,

yes, the form has various controls the user will make use of in
selecting criteria. when they click the preview push button i call a
function createWhere(..) that builds my where clause from the controls
on the form,

hope that clears up any confusion,
Sam
 
Ok, so dim a variable for the control on the form, and populate it, then use
them in the strWhere. Something like this:

Private Sub btnOpenMyReport_Click()
Dim r As String
r = Forms!MyForm!MyControl
DoCmd.OpenReport "MyReport", acViewPreview, , "MyTable.MyField= '" & r &
"'"
End Sub
 
It will be a bit more complex with more controls, but hopefully this gives
you the concept...
SusanV
 
Thanks for your suggestions and patience so far!

I'm not sure i can apply your solution to my problem. I am afraid i
have given a misleading and confusing descrpition of my problem, so i
will try on emore time, attempting to giv e a bit more detail!

I have a report form. the form has various controls the user will make
use off to add a where clause to the basic report. this is done via
various combo boxes, each where clause is added to a listbox on the
form so the user can view/ remove and add criteria at their liesure.

the user would see a list of criteria like
WHERE: age is 20-30
gender is male
Origin is White/Irish

when the click button to generate report i call my method to build my
where clause. this function basically cycles round each item in list
box and converts it so i would end up with string similar to:

strWhere = "tblPerson.age = 2 AND tblPerson.gender = 1 AND
tblEqualOps.EthnicOrigin = 5"

then i generate report with

'preview report
DoCmd.OpenReport "rptGenRpt", acPreview, , strWhere

-----

so when the code runs i amalways prompted for the parameter value of
"tblEqualOps.EthnicOrigin".

if i take the above sql and just run it in a query it runs fine.

Can you suggest anything now? hopefully ive cleared up the details of
my problem, rather than making it more confusing!

Sam
 
seconds after i made my last post, like lightning striking, i
understood what you were suggesting.

*feels a little daft*

I am going to try that now!

Thanks again, for your help and your patiance

Sam
 
Sometimes it takes a moment <grin>

Best of luck and post back if you're still having trouble!

SusanV
 
Sam said:
I have code behind a form that generates strWhere - a where clause that
is later applied to a report based on a query...

'preview report
DoCmd.OpenReport "rptGenRpt", acPreview, , strWhere

i step through the code, and immediatley before this point i grab the
string strwhere from th eimmediate window.

if i add the where clause as the codehas built it to the query and run
the query, my results return fine.

when the code continues, and executes the above line i am instead
prompted for the parameter value of <table>.<column>
(tblEqualOps.EthnicOrig).
Click to expand...


I think this would happen if the field EthnicOrig were not
included in the query's field list or if it was included but
the Show box were unchecked.
 
Thanks all for your input!

what i did in the end: since i had most of the code their to generate
my strWhere, i amended it to build the whole strSQL. created a global
variable, and used this within the report itself to set the record
source.

for future reference, is this an acceptable way of doing things, or is
there a more efficient, more "proper" way?

ta!

Sam
 
Sam said:
what i did in the end: since i had most of the code their to generate
my strWhere, i amended it to build the whole strSQL. created a global
variable, and used this within the report itself to set the record
source.

for future reference, is this an acceptable way of doing things, or is
there a more efficient, more "proper" way?
Click to expand...


It is acceptable, but not the best practice. In general,
global variable variables should be avoided when there's
another way. Using the OpenReport method's WhereCondidtion
argument is the standard way of doing this kind of thing.

What difference did you find between the original query and
your constructed query?
 
Hi Marsh, thanks for the reply :)

You asked about the differences "between the original query and
your constructed query?"... If by original query, you mean the sql i
ran as a query from with access, and the sql i constructed with the
code, there was none.
 
Sam said:
You asked about the differences "between the original query and
your constructed query?"... If by original query, you mean the sql i
ran as a query from with access, and the sql i constructed with the
code, there was none.
Click to expand...


No, I was asking about the difference between your report's
original query combined with the OpenReport's WhereCondition
and the query you are constructing in code. Without seeing
all that you've done, I still think the difference is that
the field tblEqualOps.EthnicOrig does not appear in the
SELECT clause in your original query.

For example, if report's record source query looks like:
SELECT flda, fldb
FROM table
WHERE fldc = 123
everything is fine.

However, if your report's original record soure query looked
like:
SELECT flda, fldb
FROM table
and you applied the OpenReport method's WhereCondition:
stCriteria = "fldc = 123"
the you will see the problem you're asking about in this
thread.

What I was suggesting earlier was to change the report's
record source query to:
SELECT flda, fldb, fldc
FROM table
so the WhereCondition could filter on fldc.
 
Back
Top