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

  • Thread starter Thread starter Bob
  • Start date Start date
B

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
 
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
 
Bob said:
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???
Click to expand...

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 said:
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???
Click to expand...

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
Click to expand...

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
 
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)



Bob said:
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
Click to expand...
Click to expand...
 
Bob said:
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.
Click to expand...

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 said:
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.
Click to expand...

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.
Click to expand...

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 said:
Marshall said:
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.
Click to expand...
Click to expand...
Bob said:
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....
Click to expand...

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
 
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.
Click to expand...
Marshall said:
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
Click to expand...
[snip]
Click to expand...
Bob said:
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....
Click to expand...

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
Click to expand...

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