Access 2000 query not working same as Access 97

[Ken Lindner]

We have a report based on a query that is called from a form. Nothing
too special here.

The query has 3 coulmns that are populated directly from values
entered into entry fields on the form that calls the report using
syntax such as "StartDate: [Forms]![frmRunRprts]![tbECHStartDt]" in
the field value of the column. This also reads as
"[Forms]![frmRunRprts]![tbECHStartDt] AS StartDate" in the SQL design
view.

Under Access 97 this returns values to the query result set and as a
result the values appear on the report.

Since upgrading to Access 2000, these columns are left empty in the
query result set.

Has anybody come across this and what was your fix.

[Steve Jorgensen]

The only problem like that I've run into was when the form controls were
based on calculations. In Access 2000 and 2002, it does not seem to be
reliable to refer to the value of a calculated control. I've even seen the
occasional instance where an unbound control might not return a value if
there was a calculated control on the form with an error. Could any of
this apply?

Another problem I've seen that's (not specific to a particular Access
version) arises when the data type of a query parameter is not
automatically determined correctly. Try explicitly adding your
form/control reference to the query parameters, and specify the data type.

On 14 Sep 2003 21:48:47 -0700, ke*********@eds .com (Ken Lindner) wrote:

We have a report based on a query that is called from a form. Nothing
too special here.

The query has 3 coulmns that are populated directly from values
entered into entry fields on the form that calls the report using
syntax such as "StartDate: [Forms]![frmRunRprts]![tbECHStartDt]" in
the field value of the column. This also reads as
"[Forms]![frmRunRprts]![tbECHStartDt] AS StartDate" in the SQL design
view.

Under Access 97 this returns values to the query result set and as a
result the values appear on the report.

Since upgrading to Access 2000, these columns are left empty in the
query result set.

Has anybody come across this and what was your fix.

[TC]

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:85******** *************** *********@4ax.c om...

(snip)

The only problem like that I've run into was when the form controls were
based on calculations. In Access 2000 and 2002, it does not seem to be
reliable to refer to the value of a calculated control.

What is your workaround?

TC

[Steve Jorgensen]

On Mon, 15 Sep 2003 18:52:33 +1200, "TC" <a@b.c.d> wrote:

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:85******* *************** **********@4ax. com...

(snip)

The only problem like that I've run into was when the form controls were
based on calculations. In Access 2000 and 2002, it does not seem to be
reliable to refer to the value of a calculated control.

What is your workaround?

TC

My usual approach is to do the calculation in code instead, and write the
value to an unbound control. I'll still use plain calculated controls as
always on reports or when they are for display only on forms.

[Ken Lindner]

Thanks for the suggestion Steve

I have checked this out. It does not apply in this case.

Any other ideas appreciated.

[Marc]

ke*********@eds .com (Ken Lindner) wrote in message news:<a9******* *************** ****@posting.go ogle.com>...

Thanks for the suggestion Steve

I have checked this out. It does not apply in this case.

Any other ideas appreciated.

Ken,

Try using a filter-expression for a docmd.openrepor t statement instead
of attempting direct form references.
referencing form-fields in the query - I think - only works when you
need some results in the form itself.
In any case the [forms]!... construct seems questionable to me.
You could make a button on the form that has an eventprocedure
containing a 'docmd.openrepo rt' using the right where-condition
filter-expression built from the form-input.

Marc