aggregate function error

C

CEV

I have a button on a form that opens a report based on a query. When I do
this, I get the error "You tried to execute a query that does not include
the specified expression 'Individuals.[Last Name]' as part of an aggregate
function."

SELECT Individuals.[Last Name], Individuals.[First Name], Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) Like [Forms]![frmMonitoringRouting]![Last
Name]) AND ((Individuals.[First Name]) Like
[Forms]![frmMonitoringRouting]![First Name]));

On the report, i have a field with teh following: =[First Name] & " " &
[Middle Initial] & " " & [Last Name]

I do not understand where I went wrong.

Thanks,

CEV
 
M

Michel Walsh

Individuals is a query? if so, that query is probably the source of your
problem, not the one for which you pasted the SQL code here.



Vanderghast, Access MVP
 
C

CEV

"Individuals" is the name of a Table.

Michel Walsh said:
Individuals is a query? if so, that query is probably the source of your
problem, not the one for which you pasted the SQL code here.



Vanderghast, Access MVP

CEV said:
I have a button on a form that opens a report based on a query. When I do
this, I get the error "You tried to execute a query that does not include
the specified expression 'Individuals.[Last Name]' as part of an aggregate
function."

SELECT Individuals.[Last Name], Individuals.[First Name], Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) Like
[Forms]![frmMonitoringRouting]![Last Name]) AND ((Individuals.[First
Name]) Like [Forms]![frmMonitoringRouting]![First Name]));

On the report, i have a field with teh following: =[First Name] & " " &
[Middle Initial] & " " & [Last Name]

I do not understand where I went wrong.

Thanks,

CEV
Click to expand...
Click to expand...
 
C

Chris

Using 'Like' in a query requires the use of a wildcard (*,%, etc.). Is there
a reason why you aren't using equals? Try:

SELECT Individuals.[Last Name], Individuals.[First Name], Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) = [Forms]![frmMonitoringRouting]![Last
Name]) AND ((Individuals.[First Name]) =
[Forms]![frmMonitoringRouting]![First Name]));

CEV said:
"Individuals" is the name of a Table.

Michel Walsh said:
Individuals is a query? if so, that query is probably the source of your
problem, not the one for which you pasted the SQL code here.



Vanderghast, Access MVP

CEV said:
I have a button on a form that opens a report based on a query. When I do
this, I get the error "You tried to execute a query that does not include
the specified expression 'Individuals.[Last Name]' as part of an aggregate
function."

SELECT Individuals.[Last Name], Individuals.[First Name], Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) Like
[Forms]![frmMonitoringRouting]![Last Name]) AND ((Individuals.[First
Name]) Like [Forms]![frmMonitoringRouting]![First Name]));

On the report, i have a field with teh following: =[First Name] & " " &
[Middle Initial] & " " & [Last Name]

I do not understand where I went wrong.

Thanks,

CEV
Click to expand...
Click to expand...
Click to expand...
 
C

CEV

OK, yes that does make sense. I changed to the = but i am still getting the
error: "You tried to execute a query that does not include the specified
expression 'Individuals.[Last Name]' as part of an aggregate function." I do
not see anything wrong with [Last Name]. I've double checked spelling.


Chris said:
Using 'Like' in a query requires the use of a wildcard (*,%, etc.). Is
there
a reason why you aren't using equals? Try:

SELECT Individuals.[Last Name], Individuals.[First Name], Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) = [Forms]![frmMonitoringRouting]![Last
Name]) AND ((Individuals.[First Name]) =
[Forms]![frmMonitoringRouting]![First Name]));

CEV said:
"Individuals" is the name of a Table.

Michel Walsh said:
Individuals is a query? if so, that query is probably the source of
your
problem, not the one for which you pasted the SQL code here.



Vanderghast, Access MVP

I have a button on a form that opens a report based on a query. When I
do
this, I get the error "You tried to execute a query that does not
include
the specified expression 'Individuals.[Last Name]' as part of an
aggregate
function."

SELECT Individuals.[Last Name], Individuals.[First Name],
Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social
Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) Like
[Forms]![frmMonitoringRouting]![Last Name]) AND ((Individuals.[First
Name]) Like [Forms]![frmMonitoringRouting]![First Name]));

On the report, i have a field with teh following: =[First Name] & " "
&
[Middle Initial] & " " & [Last Name]

I do not understand where I went wrong.

Thanks,

CEV
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

I have a button on a form that opens a report based on a query. When I do
this, I get the error "You tried to execute a query that does not include
the specified expression 'Individuals.[Last Name]' as part of an aggregate
function."

SELECT Individuals.[Last Name], Individuals.[First Name], Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) Like [Forms]![frmMonitoringRouting]![Last
Name]) AND ((Individuals.[First Name]) Like
[Forms]![frmMonitoringRouting]![First Name]));

On the report, i have a field with teh following: =[First Name] & " " &
[Middle Initial] & " " & [Last Name]
Click to expand...

Did you have a Totals query based on Individuals at some point? Or what is the
SQL of the report's recordsource? The error message suggests that there is a
totals query involved at some point. Possibly if you had Name Autocorrect
tracking on, there is some residue from a previous query now lost.

Try deleting the query, turning off Name Autocorrupt, compacting the database,
and recreating the query.

John W. Vinson [MVP]
 
J

John Spencer

Try copying the SQL text into a new query and see if the new query runs
correctly.

If the new query runs correctly, delete the query that you are having
problems with.
Compact and repair the database
Rename the new query to the old query name.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

CEV said:
OK, yes that does make sense. I changed to the = but i am still getting
the error: "You tried to execute a query that does not include the
specified expression 'Individuals.[Last Name]' as part of an aggregate
function." I do not see anything wrong with [Last Name]. I've double
checked spelling.


Chris said:
Using 'Like' in a query requires the use of a wildcard (*,%, etc.). Is
there
a reason why you aren't using equals? Try:

SELECT Individuals.[Last Name], Individuals.[First Name], Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) = [Forms]![frmMonitoringRouting]![Last
Name]) AND ((Individuals.[First Name]) =
[Forms]![frmMonitoringRouting]![First Name]));

CEV said:
"Individuals" is the name of a Table.

Individuals is a query? if so, that query is probably the source of
your
problem, not the one for which you pasted the SQL code here.



Vanderghast, Access MVP

I have a button on a form that opens a report based on a query. When I
do
this, I get the error "You tried to execute a query that does not
include
the specified expression 'Individuals.[Last Name]' as part of an
aggregate
function."

SELECT Individuals.[Last Name], Individuals.[First Name],
Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social
Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) Like
[Forms]![frmMonitoringRouting]![Last Name]) AND ((Individuals.[First
Name]) Like [Forms]![frmMonitoringRouting]![First Name]));

On the report, i have a field with teh following: =[First Name] & " "
&
[Middle Initial] & " " & [Last Name]

I do not understand where I went wrong.

Thanks,

CEV
Click to expand...
Click to expand...
Click to expand...
 
C

CEV

Well, I do not know exactly what the original issue was. I was using Access
2007 and re-installed 2003 to see if I could get it to work in there and I
was able to. So I do not know if it was because of Access 2007 or something
else I did, but I was able to work easier in 2003.

Thanks,

CEV

John Spencer said:
Try copying the SQL text into a new query and see if the new query runs
correctly.

If the new query runs correctly, delete the query that you are having
problems with.
Compact and repair the database
Rename the new query to the old query name.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

CEV said:
OK, yes that does make sense. I changed to the = but i am still getting
the error: "You tried to execute a query that does not include the
specified expression 'Individuals.[Last Name]' as part of an aggregate
function." I do not see anything wrong with [Last Name]. I've double
checked spelling.


Chris said:
Using 'Like' in a query requires the use of a wildcard (*,%, etc.). Is
there
a reason why you aren't using equals? Try:

SELECT Individuals.[Last Name], Individuals.[First Name],
Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) = [Forms]![frmMonitoringRouting]![Last
Name]) AND ((Individuals.[First Name]) =
[Forms]![frmMonitoringRouting]![First Name]));

:

"Individuals" is the name of a Table.

Individuals is a query? if so, that query is probably the source of
your
problem, not the one for which you pasted the SQL code here.



Vanderghast, Access MVP

I have a button on a form that opens a report based on a query. When
I do
this, I get the error "You tried to execute a query that does not
include
the specified expression 'Individuals.[Last Name]' as part of an
aggregate
function."

SELECT Individuals.[Last Name], Individuals.[First Name],
Individuals.SC,
Individuals.Supervisor, Individuals.Nurse, Individuals.[Social
Security
Number], Individuals.[Middle Initial]
FROM Individuals
WHERE (((Individuals.[Last Name]) Like
[Forms]![frmMonitoringRouting]![Last Name]) AND ((Individuals.[First
Name]) Like [Forms]![frmMonitoringRouting]![First Name]));

On the report, i have a field with teh following: =[First Name] & "
" &
[Middle Initial] & " " & [Last Name]

I do not understand where I went wrong.

Thanks,

CEV
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I count the number of individuals? 2
Please help with a query! 4
Form/Query Question 2
Length Limits on MS Access Filters 3
combining two or more table fields into a single table field 1
Names 1
Splitting Text Field 1
% of callers repeat/unique 1

Top