Data type mismatch in criteria expression

[ccholai]

SELECT TOP 1 TreeCount.CDate, TreeCount.Trial, Count(TreeCount.Leafs)
AS NoOfLeafs, Avg(TreeCount.TreeC) AS TreeCount
FROM TreeCount
GROUP BY TreeCount.CDate, TreeCount.Trial
HAVING FrondCount.Trial = " &trial &"

Without the having statement - it works fine but once I put in the
criteria it comes up with Data type mismatch in criteria expression

Any help would be very appreciated. Thanks

 

[Allen Browne]

Sorry: I don't understand that HAVING clause either.

What's FrondCount? Where does this table come in? Its not in the FROM
clause.

What's &trial? Where did this come from? What are the quotes for? They are
not else around the string.

 

[ccholai]

Hi Allen,

sorry - the HAVING clause should read:

HAVING TreeCount.Trial = " &trial &"

"&trial&" is a parameter value - the user types in the trial and and
should pick up the associated info for that trial.

 

[John Vinson]

Allen,

sorry - the HAVING clause should read:

HAVING TreeCount.Trial = " &trial &"

"&trial&" is a parameter value - the user types in the trial and and
should pick up the associated info for that trial.

Access does not use & as a delimiter for parameters; it uses square
brackets.

John W. Vinson[MVP]

 

[Allen Browne]

Ah, yes.
John is correct.

Try:
SELECT TOP 1 TreeCount.CDate,
TreeCount.Trial,
Count(TreeCount.Leafs) AS NoOfLeafs,
Avg(TreeCount.TreeC) AS TreeCount
FROM TreeCount
GROUP BY TreeCount.CDate, TreeCount.Trial
HAVING TreeCount.Trial = [How Many];

If Trial is a numeric field (not a text field), can I also suggest that you
declare this parameter. Choose Parameters on the Query menu. Enter exactly
the same name, and specify the data type (e.g. Long for a whole number.)

 

[c8tz]

Thanks guys - it's ok now!!!

Ah, yes.
John is correct.

Try:
SELECT TOP 1 TreeCount.CDate,
TreeCount.Trial,
Count(TreeCount.Leafs) AS NoOfLeafs,
Avg(TreeCount.TreeC) AS TreeCount
FROM TreeCount
GROUP BY TreeCount.CDate, TreeCount.Trial
HAVING TreeCount.Trial = [How Many];

If Trial is a numeric field (not a text field), can I also suggest that you
declare this parameter. Choose Parameters on the Query menu. Enter exactly
the same name, and specify the data type (e.g. Long for a whole number.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access does not use & as a delimiter for parameters; it uses square
brackets.

John W. Vinson[MVP]