Data Type Mismatch in Criteria Expression

Tech-Archive recommends: Fix windows errors by optimizing your registry

I am using Microsoft Access 97 and have an internediate level skill.

I have a a query to filter records by both street name (Boiler Street) and
expiration date (Last Inspection Date).

When I enter the following criteria in Design View:

(Boiler Street) Like "18th Ave.*"
and
(Last Inspection Date) Between #1/1/07# And #12/31/08#

The query runs as expected and I get the correct results.

However, If I only change the (Boiler Street) criteria as follows and leave
the (Last Inspection Date) unchanged:

(Boiler Street) Like "Ave. U*"
and
(Last Inspection Date) Between #1/1/07# And #12/31/08#

the query will not run and I get the "Data Type Mismatch in Criteria
Expression" error message.

I also noticed that if I erase the (Boiler Street) criteria and leave it
"empty," I get the same error message.

Is this a bug In Access? How can I resolve this problem?

I've copied the SQL View below:

SELECT DISTINCTROW [Boiler Inspection Customers].Company, [Boiler Inspection
Customers].TitleList, [Boiler Inspection Customers].[First Name], [Boiler
Inspection Customers].[Last Name], [Boiler Inspection Customers].Phone,
[Boiler Inspection Customers].StreetNum, [Boiler Inspection
Customers].Street, [Boiler Inspection Customers].ZipCode, Val([Boiler
Inspection Customers]![Boiler StreetNum]) AS Expr1, [Boiler Inspection
Customers].[Boiler StreetNum], [Boiler Inspection Customers].[Boiler Street],
[Boiler Inspection Customers].BoilerID, [Boiler Inspection
Customers].Pressure, [Boiler Inspection Customers].Block, [Boiler Inspection
Customers].Lot, [Boiler Inspection Customers].Floors, [Boiler Inspection
Customers].Apts, Sum([Boiler Inspection Customers].[Number of Boilers]) AS
[SumOfNumber of Boilers], ([Company] & [First Name] & [Last Name]) AS
FullName, [Boiler Inspection Customers].[Last Inspection Date], [Boiler
Inspection Customers].City, [Boiler Inspection Customers].OwnerState, [Boiler
Inspection Customers].BoilerName, [Boiler Inspection
Customers].BoilerZipCode, [Boiler Inspection Customers].NotforLabel
FROM [Boiler Inspection Customers]
GROUP BY [Boiler Inspection Customers].Company, [Boiler Inspection
Customers].TitleList, [Boiler Inspection Customers].[First Name], [Boiler
Inspection Customers].[Last Name], [Boiler Inspection Customers].Phone,
[Boiler Inspection Customers].StreetNum, [Boiler Inspection
Customers].Street, [Boiler Inspection Customers].ZipCode, Val([Boiler
Inspection Customers]![Boiler StreetNum]), [Boiler Inspection
Customers].[Boiler StreetNum], [Boiler Inspection Customers].[Boiler Street],
[Boiler Inspection Customers].BoilerID, [Boiler Inspection
Customers].Pressure, [Boiler Inspection Customers].Block, [Boiler Inspection
Customers].Lot, [Boiler Inspection Customers].Floors, [Boiler Inspection
Customers].Apts, ([Company] & [First Name] & [Last Name]), [Boiler Inspection
Customers].[Last Inspection Date], [Boiler Inspection Customers].City,
[Boiler Inspection Customers].OwnerState, [Boiler Inspection
Customers].BoilerName, [Boiler Inspection Customers].BoilerZipCode, [Boiler
Inspection Customers].NotforLabel
HAVING ((([Boiler Inspection Customers].[Boiler Street]) Like "18th Ave.*")
AND (([Boiler Inspection Customers].[Last Inspection Date]) Between
#1/1/2007# And #12/31/2008#));


.

Relevant Pages

  • Re: Data Type Mismatch in Criteria Expression
    ... Where does [Boiler Streetnum] come into it? ... SELECT DISTINCTROW [Boiler Inspection Customers].Company, ... I entered a value for the empty field and reran the query. ...
    (microsoft.public.access.queries)
  • finding #
    ... what would be the best way to find the "#" character within a query? ... I would want the query to only list fire # and boiler # ...
    (microsoft.public.access.queries)