generating sql

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
 
M

Marshall Barton

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

Sam

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
 
M

Marshall Barton

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?
 
S

Sam

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

Marshall Barton

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