Query error 3122 on aggregrate function missing field 1

[jfcj]

Please help with the following:

First I am not using any aggregate functions!

I am using the following query to populate a report:

SELECT SO_MAIN.STR_NAME, SO_MAIN.STR_NUMBER, SO_MAIN.NSEW, SO_MAIN.SONUM, SO_PE.PE_DATE, SO_PE.PE_TYPE, SO_MAIN1.COMP_DATE, SO_MAIN.REPORTED, SO_MAIN.REP_BY, SO_MAIN.REQ_BY, SO_PE.PE_HOURS, SO_PE.ID, SO_MAIN1.JOB_CODE, JOBCODES.DESC
FROM ((SO_MAIN INNER JOIN SO_MAIN1 ON SO_MAIN.SONUM=SO_MAIN1.SONUM) INNER JOIN SO_PE ON SO_MAIN1.SONUM=SO_PE.SONUM) INNER JOIN JOBCODES ON SO_MAIN1.JOB_CODE=JOBCODES.JOB_CODE
WHERE (SO_MAIN1.JOB_CODE) Like '7*' AND (SO_MAIN1.COMP_DATE)<=#11/30/1903# AND (SO_MAIN1.COMP_DATE)>=#11/1/1903#
ORDER BY SO_MAIN.STR_NAME, SO_MAIN.STR_NUMBER, SO_MAIN.NSEW, SO_MAIN.SONUM, SO_PE.PE_DATE, SO_PE.PE_TYPE, SO_MAIN1.COMP_DATE;

I am using a querydef to get the query so that the dates can be input in a text box to be inserted into the SQL WHERE statement.

Then I am attempting to open the report using:
DoCmd.OpenReport "MonthlyActivityReport_Maintenance", acViewPreview, "MonthlyActivity_Maintenance"

I get the error on the field named "SO_MAIN.NSEW" but when I remove this field from the "ORDER BY" statement, I get the same error on the field "SO_PE.PE_DATE". Both fields are in the SELECT part of the SQL statement.

Thanks for the help.
Francis

 

[RoyVidar]

Have you tried just:

[tt]DoCmd.OpenReport "MonthlyActivityReport_Maintenance", acViewPreview[/tt]

It seems you're opening a report, based on a filtered query, but also apply a filter.

Roy-Vidar

 

[jfcj]

Hi Roy
Thanks for your help. I tried leaving the filter ("MonthlyActivity_Maintenance") off the command and ran it like the following: DoCmd.OpenReport "MonthlyActivityReport_Maintenance, acViewPreview
I get an Error 3464 Data type mismatch in criteria expression.
What would you suggest?
Francis

 

[RoyVidar]

From the query, only thing that springs to mind, is the following:

[tt](SO_MAIN1.JOB_CODE) Like '7*'[/tt]

Is the field a string? If it's numeric, then drop of the single quotes, and I don't the Like operator is supported for numerics, for text, shouldn't they be double quotes within the query?

Does the query run if you open it as a query?

Another way of passing a filter, or rather a where condition, is using the "WhereCondition" of the openreport method of the docmd object. To make that work, remove the where condition in the query, and assign it thru code, using the form controls directly, perhaps something like this:

[tt]dim sCrit as string
sCrit="SO_MAIN1.JOB_CODE Like '7*' AND " & _
"SO_MAIN1.COMP_DATE<=# " & me!txtFirstDate & "# AND " & _
"SO_MAIN1.COMP_DATE>=#" & me!txtSecondDate & "#"
DoCmd.OpenReport "MonthlyActivityReport_Maintenance", acViewPreview,,sCrit[/tt]

BTW - I'd advice not to rely on the sorting of the query, do the sorting in the report using the sort and grouping thingie. Could perhaps also try the original statement, without the Order By clause in the query?

Roy-Vidar

 

[jfcj]

Hi Roy,
Thanks for your help. I tried using the "wherecondition" on the DoCmd.OpenReport {DoCmd.OpenReport "MonthlyActivityReport_Maintenance", acViewPreview,,"(SO_MAIN1.JOB_CODE) Like '7*' AND (SO_MAIN1.COMP_DATE)<=#11/30/1903# AND (SO_MAIN1.COMP_DATE)>=#11/1/1903#"} as you suggested and received the error 3464 data type mismatch in criteria expression. I tried removing all the "ORDER BY" from my query and got the original error message 3122 you tried to execute a query that does not include the specified expression 'SO_MAIN.NSEW' as part of an aggregate function. The report I am trying to open sorts on the fields SO_MAIN.STR_NAME, SO_MAIN.STR_NUMBER, SO_MAIN.NSEW, SO_MAIN.SONUM, SO_PE.PE_DATE. I have it grouping on SO_MAIN.SONUM AND SO_PE.PE_DATE. I am at a lost--what about you?
Francis

 

[RoyVidar]

You didn't answer my questions "Is the field a string?" and "Does the query run if you open it as a query?"

Datatype mismatch usually means there is a mismatch between the datatype of one of the criteria fields and the value offered as parameter/criteria.

Don't know - what I often do, is start from scratch.

Recreate the query without where/order by clauses, run the query - does it show the expected results?

Assign it to the reports recordsource, does the report run?

Then start building the criteria/where condition incremental (one by one) and see if/when it bombs.

Roy-Vidar

 

[jfcj]

Hi Roy,
Yes the "SO_MAIN.JOBCODE" is a string. I used the single quote because it was enclosed with other SQL statements in double quotes while I was inserting the dates for the report. I am able to run the query as a query and get the expected results. I get the same records whether or not I use the "order by" clause. It always gives me 108 records, sorted if I include the "order by" and in random order if I do not. I will start from scratch and rebuild the query without any "where/order by" statements and assign it to the record source for the report and see if it runs. I will let you know how I progress!
Thanks for all the help.
Francis

 

[jfcj]

Hi Roy,
I rebuilt the query from scratch leaving off the "Order By" and did not select the "SO_MAIN.NSEW" field. This worked but since I had the "NSEW" field as part of my sort on the report, I always got a prompt for the value of "NSEW". I just hit the enter key and the report ran okay. Could you give me some ideas why this particular field is a problem. It is text of one character and should be either a space or the characters "N", "S", "E" or "W". Could the data have some NULL values that are causing me the error?
Thanks for your help and congratulations on getting tipmaster of the week!
Francis

 

[RoyVidar]

Thank you, thank you, thank you!

Does it throw an error if you include the NSEW field in your select clause too?

I'm sorry, I don't know, I'm stumped, but queries are not among my strengths. Perhaps post in the queries or reports fora (since no one else has come to our rescue here yet;-))?

Roy-Vidar

 

[dhookom]

Roy-Vidar offered up great advice regarding using a Where condition and not attempting any sorting in the report's record source. I would not include the table names in the Where condition since the fields resulting from the select statement should be unique and I don't think the report understands what source table or query they came from. I never use the same field name in multiple tables in an MDB.

Is NSEW a text, memo, calculated,... column/field?



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jfcj]

Hi Roy and Duane,
Yes I get the error 3464 (data type mismatch in criteria expression) when I include the "NSEW" field in the select for some of the reports. It appears to me that bad data in this field is affecting these SQL queries. The "NSEW" field is text of one character and should be either a space or the characters "N", "S", "E" or "W". Duane as a general rule I do not use the same field names in different tables either. I inherited this database from a clipper system. I converted the data for MS access.
Thanks for your help!
Francis

 

[dhookom]

You should try a test of
SELECT NSEW, Count(*) as NumOf
FROM SO_MAIN
GROUP BY NSEW;
See if this query works and/or returns unexpected results.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jfcj]

Hello Duane,
Thanks for your help!
I ran the query to get a count of the various records for the NSEW field. I discover that the field that I thought was spaces is, in fact, NULL values. None of the rows have a space in the NSEW field. The count for rows with a NULL value in the NSEW field is 54,781 and the count for fields with either an "N", "S", "E" or "W" is 4,668 which gives us a count of 59,449. I ran a query to check the table that the NSEW field is in and get the same record count. Your query and my query reported 59,449 rows in the table SO_MAIN. Is it the NULL values giving me the problem and what can I do about it?
Thanks,
Francis

 

[dhookom]

Sorting or grouping by a field that might be null should not have any issues. Are you showing group headers or footers on the NSEW field?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

Anyway, you may consider the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jfcj]

Hello Duane and PH,
Thanks for the help. I am just using this field to sort. My sort sequence is STREET NAME, STREET NUMBER, NSEW, SONUM and PE_DATE. I am grouping on SONUM AND PE_DATE.

PH, could you give me an example of using Nz Function? Is it possible with a SQL statement?

Thanks,
Francis

 

[dhookom]

Francis,
I am not sure I understand since you can't sort by a field without also grouping by the field. When you suggest "Group" are you meaning that you have headers and/or footers on these fields?

What happens when you filter the report to about 1/2 the records and run the report? Can you play with your criteria until you identify the record or records that might be causing an issue?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

Is it possible with a SQL statement?
Yes.
Nz(NSEW, " ") As NSEW

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jfcj]

Hello Duane,
Thanks for the help. Sorry I have been away. I do not have group headers and footers on the NSEW field. I only have group headers and footers on SONUM and PE_DATE. I have not had a chance to filter the amount of data but I hope to do that in several days.

Hello PH,
Thanks for the help on the Nz function in a SQL statement. I tried it on the NSEW and changed all the nulls to a character X but I still get the data type mismatch error 3464 when I try to run the report.

Thanks,
Francis