DCOUNT function works when rpt uses 1 query; but NOT another?!?!?

[Bob]

running access 2k;

I'm working on a sub-report with 4 columns. I want to determine the
total # of columns (up to 4) by doing a recordset count using Dcount.
My purpose in doing this is to be able to turn the ForeColor to red on
ONLY the last column. (not all records in the master report will have
4 columns)

here's the Dcount statement I'm using:
y = DCount("[complaint_no]", RecordSource)

here's the problem:
my sub report is using query "myquery" as the recordsource.
IF "myquery" doesn't have any criteria in any fields, the Dcount
function works.

BUT - if "myquery" has a field [ssn] which has "[emp][.ssn]" as the
criteria; I get the following error msg: RT err 2471 "expression
entered as query parameter gave this error: the object doesn't contain
the automation object 'emp.ssn' "

emp.ssn is in my master report, and the query works fine if I'm not
trying to figure out the Dcount.

I have my query setup this way because when I tried linking the
reports master & child fields to ssn; I was not getting the desired
results. (which is a sub-report with only the complaints associated
with each employee being used in master)

I know that Dcount has its own criteria capability (which I have not
tried using because I didn't want dcount to run ANOTHER query on top
of the 1 just run). But if my query itself already takes care of the
criteria - why isn't Dcount happy???

tia - Bob

 

[Douglas J. Steele]

You may have added field ssn from table emp when you created query myquery,
but as far as the query's concerned, the field name is simply ssn. If you
have more than one field named ssn in the query, the subsequent ones will be
renamed something like Expr1, or you can assign an alias using the AS
keyword:

Select Field1 AS MyFieldName, Field2 FROM MyTable

 

[Marshall Barton]

running access 2k;

I'm working on a sub-report with 4 columns. I want to determine the
total # of columns (up to 4) by doing a recordset count using Dcount.
My purpose in doing this is to be able to turn the ForeColor to red on
ONLY the last column. (not all records in the master report will have
4 columns)

here's the Dcount statement I'm using:
y = DCount("[complaint_no]", RecordSource)

here's the problem:
my sub report is using query "myquery" as the recordsource.
IF "myquery" doesn't have any criteria in any fields, the Dcount
function works.

BUT - if "myquery" has a field [ssn] which has "[emp][.ssn]" as the
criteria; I get the following error msg: RT err 2471 "expression
entered as query parameter gave this error: the object doesn't contain
the automation object 'emp.ssn' "

emp.ssn is in my master report, and the query works fine if I'm not
trying to figure out the Dcount.

I have my query setup this way because when I tried linking the
reports master & child fields to ssn; I was not getting the desired
results. (which is a sub-report with only the complaints associated
with each employee being used in master)

I know that Dcount has its own criteria capability (which I have not
tried using because I didn't want dcount to run ANOTHER query on top
of the 1 just run). But if my query itself already takes care of the
criteria - why isn't Dcount happy???

The report may be filtered in one way or another??

You didn't post the DCount, but [emp][.ssn] doesn't look
like a valid reference to a control on the report.

Changing the color of the 4th column is a little weird, but
if that's what you're trying to do, there might be another
way. Use a copy of you report so you don't mess up the
original. Try using some code in the detail section's
Format event:

If Me.Left < 4 * Me.Width Then
Me.txtbox.ForeColor = vbBlack
Else
Me.txtbox.ForeColor = vbRed
End If

 

[Bob]

running access 2k;

I'm working on a sub-report with 4 columns. I want to determine the
total # of columns (up to 4) by doing a recordset count using Dcount.
My purpose in doing this is to be able to turn the ForeColor to red on
ONLY the last column. (not all records in the master report will have
4 columns)

here's the Dcount statement I'm using:
y = DCount("[complaint_no]", RecordSource)

here's the problem:
my sub report is using query "myquery" as the recordsource.
IF "myquery" doesn't have any criteria in any fields, the Dcount
function works.

BUT - if "myquery" has a field [ssn] which has "[emp][.ssn]" as the
criteria; I get the following error msg: RT err 2471 "expression
entered as query parameter gave this error: the object doesn't contain
the automation object 'emp.ssn' "

emp.ssn is in my master report, and the query works fine if I'm not
trying to figure out the Dcount.

I have my query setup this way because when I tried linking the
reports master & child fields to ssn; I was not getting the desired
results. (which is a sub-report with only the complaints associated
with each employee being used in master)

I know that Dcount has its own criteria capability (which I have not
tried using because I didn't want dcount to run ANOTHER query on top
of the 1 just run). But if my query itself already takes care of the
criteria - why isn't Dcount happy???

The report may be filtered in one way or another??

You didn't post the DCount, but [emp][.ssn] doesn't look
like a valid reference to a control on the report.

Changing the color of the 4th column is a little weird, but
if that's what you're trying to do, there might be another
way. Use a copy of you report so you don't mess up the
original. Try using some code in the detail section's
Format event:

If Me.Left < 4 * Me.Width Then
Me.txtbox.ForeColor = vbBlack
Else
Me.txtbox.ForeColor = vbRed
End If

Marshall; tx for your reply.

However, If I'm not mistaken your suggestion will only work when there
are 4 columns.

the issue is that there MAY be less than 4 columns, and I want the
last column in red; even if it's the only column there.

Bob

 

[Bob]

hi Doug;

tx for your reply!

actually, the query is getting the value of [emp].[ssn] from the
master form!

the master & child are not connected in the conventional sense - when
I tried that; I wasn't getting the correct record sub-set from the
complaints query.

So I've actually buried a field from master into the query for the
child!

another complicating twist, is that in order to get only the most
recent 4 complaints, I'm using TOP 4 in my select. That may have
contributed to the problem when I tried doing a normal master/child
link.

other comments?

Bob

You may have added field ssn from table emp when you created query myquery,
but as far as the query's concerned, the field name is simply ssn. If you
have more than one field named ssn in the query, the subsequent ones will be
renamed something like Expr1, or you can assign an alias using the AS
keyword:

Select Field1 AS MyFieldName, Field2 FROM MyTable

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

running access 2k;

I'm working on a sub-report with 4 columns. I want to determine the
total # of columns (up to 4) by doing a recordset count using Dcount.
My purpose in doing this is to be able to turn the ForeColor to red on
ONLY the last column. (not all records in the master report will have
4 columns)

here's the Dcount statement I'm using:
y = DCount("[complaint_no]", RecordSource)

here's the problem:
my sub report is using query "myquery" as the recordsource.
IF "myquery" doesn't have any criteria in any fields, the Dcount
function works.

BUT - if "myquery" has a field [ssn] which has "[emp][.ssn]" as the
criteria; I get the following error msg: RT err 2471 "expression
entered as query parameter gave this error: the object doesn't contain
the automation object 'emp.ssn' "

emp.ssn is in my master report, and the query works fine if I'm not
trying to figure out the Dcount.

I have my query setup this way because when I tried linking the
reports master & child fields to ssn; I was not getting the desired
results. (which is a sub-report with only the complaints associated
with each employee being used in master)

I know that Dcount has its own criteria capability (which I have not
tried using because I didn't want dcount to run ANOTHER query on top
of the 1 just run). But if my query itself already takes care of the
criteria - why isn't Dcount happy???

tia - Bob

 

[Marshall Barton]

Marshall; tx for your reply.

However, If I'm not mistaken your suggestion will only work when there
are 4 columns.

the issue is that there MAY be less than 4 columns, and I want the
last column in red; even if it's the only column there.

Ah, sorry I misunderstood about the column.

You should still fix that reference to [emp][.ssn]. Either
you made a typo in your post or there is definitely
something wrong. If the query is supposed to get the ssn
criteia value from a form, the syntax should be:

Forms!theform.txtssn

where "theform" is the name of the form and "txtssn" is the
text box on the form that has the value of ssn you want to
limit the report.

 

[Bob]

Marshall; tx for your reply.

However, If I'm not mistaken your suggestion will only work when there
are 4 columns.

the issue is that there MAY be less than 4 columns, and I want the
last column in red; even if it's the only column there.

Ah, sorry I misunderstood about the column.

You should still fix that reference to [emp][.ssn]. Either
you made a typo in your post or there is definitely
something wrong. If the query is supposed to get the ssn
criteia value from a form, the syntax should be:

Forms!theform.txtssn

where "theform" is the name of the form and "txtssn" is the
text box on the form that has the value of ssn you want to
limit the report.

No problem; do you have another suggestion that might solve my last
red column issue?

as for the [emp].[ssn] the period was a typo; however I did use only
the form & field names. You're correct that I'm not using the proper
syntax; however it did work for everything else (other than the
Dcount). I'll give that a try & see if it clears up the Dcount
issue....

Bob

 

[Marshall Barton]

Ah, sorry I misunderstood about the column.

You should still fix that reference to [emp][.ssn]. Either
you made a typo in your post or there is definitely
something wrong. If the query is supposed to get the ssn
criteia value from a form, the syntax should be:

Forms!theform.txtssn

where "theform" is the name of the form and "txtssn" is the
text box on the form that has the value of ssn you want to
limit the report.

No problem; do you have another suggestion that might solve my last
red column issue?

as for the [emp].[ssn] the period was a typo; however I did use only
the form & field names. You're correct that I'm not using the proper
syntax; however it did work for everything else (other than the
Dcount). I'll give that a try & see if it clears up the Dcount
issue....

I think it should clear it up, but now that I'm thinking(?)
about it, tell me again why you need the DCount. If it's
just to get a count of the records in the report's record
source so you precalculate the number of columns, just use a
text box in the report header with =Count(*)

Once you figure out how many columns the report will have,
then I think you can use a slight variation of the code I
posted earlier:

If Me.Left < intNumColumns * Me.Width Then
Me.txtbox.ForeColor = vbBlack
Else
Me.txtbox.ForeColor = vbRed
End If

 

[Bob]

On Tue, 14 Oct 2003 16:59:56 -0500, Marshall Barton

[snip]

Bob wrote:
However, If I'm not mistaken your suggestion will only
work when there are 4 columns.

the issue is that there MAY be less than 4 columns, and I want the
last column in red; even if it's the only column there.

Ah, sorry I misunderstood about the column.

You should still fix that reference to [emp][.ssn]. Either
you made a typo in your post or there is definitely
something wrong. If the query is supposed to get the ssn
criteia value from a form, the syntax should be:

Forms!theform.txtssn

[snip]

No problem; do you have another suggestion that might solve my last
red column issue?

as for the [emp].[ssn] the period was a typo; however I did use only
the form & field names. You're correct that I'm not using the proper
syntax; however it did work for everything else (other than the
Dcount). I'll give that a try & see if it clears up the Dcount
issue....

I think it should clear it up, but now that I'm thinking(?)
about it, tell me again why you need the DCount. If it's
just to get a count of the records in the report's record
source so you precalculate the number of columns, just use a
text box in the report header with =Count(*)

Once you figure out how many columns the report will have,
then I think you can use a slight variation of the code I
posted earlier:

If Me.Left < intNumColumns * Me.Width Then
Me.txtbox.ForeColor = vbBlack
Else
Me.txtbox.ForeColor = vbRed
End If

Marshall -

you were right - the proper syntax DID allow the Dcount to work
correctly.

as for your other suggestion - I thought I had tried using count();
but ran into problems, which I don't remember. Using Dcount, I can
compare to see if my column counter = dcount, and when it does turn
font red.

I should re-examine the count function - I'm sure that it is probably
faster than dcount....

Bob