Re: Data Type Mismatch in Criteria Expression

It's possible, but I'd be surprised if it's a bug.

I think you missed my point about the difference between WHERE and HAVING
though. Unless you're trying to sort on the results of the grouping, you
should always use WHERE, not HAVING. WHERE is evaluated before the grouping
is done, so you'll be grouping a smaller set of data (and hence it should be
faster)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"nytwodees" <nytwodees@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6FFA3AFF-7901-4BC2-B6C5-EED83ED52475@xxxxxxxxxxxxxxxx
Hi Douglas:

Thanks again for your reply and for bringing the "Drop Downs" to my
attention. I was unaware of that capability.

I have run the original query using Microsoft Access 2000 instead of
Access
97. In Access 2000, I do not get any errors at all. The query works as
desired. The SQL using "HAVING" and the end of the SQL as already shown
in
my original SQL view works OK. (Seems that there is no need for the
"WHERE"
statement. This leads me to believe that there is a bug in Access 97 or a
corruption on my Access files on my computer. What do you think?

Dan

"Douglas J. Steele" wrote:

The error could be because you've got a Null value in the field.

Access generated exactly what SQL you told it to. You put your criteria
under fields that had the property in the Sort row set to Group By. To
have
Access use WHERE instead of HAVING, add the fields that have criteria to
the
grid a second time and uncheck the Show box. Change the Sort row to Where
and set the criteria under these new fields instead of under the fields
that
are set to Group By.



.

Relevant Pages

  • Re: Data Type Mismatch in Criteria Expression
    ... I have run the original query using Microsoft Access 2000 instead of Access ... The SQL using "HAVING" and the end of the SQL as already shown in ... under fields that had the property in the Sort row set to Group By. ... Access use WHERE instead of HAVING, add the fields that have criteria to the ...
    (microsoft.public.access.queries)
  • Re: Query paramete error
    ... Here is the SQL view of my query. ... I have also ran this on a text field with text criteria and nothing. ... > "bug", and I used many, many parameter queries in A97. ...
    (microsoft.public.access.queries)
  • Re: mail merge filter criteria
    ... and that it will occur as soon as you have an OLE DB connection to a data source and specify "is blank" or perhaps "is not blank" as one of the criteria in your "Advanced criteria". ... is null (which in Excel appears to correspond to a cell with no text or formula in it ... The Jet SQL that is used to get data from Excel actually retrieves both ...
    (microsoft.public.word.mailmerge.fields)
  • Re: SQL queries that have parameters passed by user
    ... Erland Sommarskog wrote: ... >> several fields and filters on a criteria that I would like to be user ... >> The query works fine in MS SQL and seems to be fairly quick, ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Syntax Error
    ... match all the criteria you input. ... SQL view and never open it in the query grid view. ... The syntax for the "where" statement is incorrect becuase of this. ...
    (microsoft.public.access.queries)