Data Type Mismatch in Criteria Expression

N

nytwodees

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#));
 
D

Douglas J. Steele

Not sure whether this will make a difference, but try changing your HAVING
clause to a WHERE clause:

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]
WHERE [Boiler Inspection Customers].[Boiler Street] Like "18th Ave.*"
AND ([Boiler Inspection Customers].[Last Inspection Date]) Between
#1/1/2007# And #12/31/2008#)
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 should only be used when you're trying to base your selection on the
results of the grouping (such as only selecting those records where the sum
of is greater than a particular value). WHERE clauses are evaluated before
the grouping is done, HAVING clauses are evaluated after.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


nytwodees said:
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#));
Click to expand...
 
N

nytwodees

Thanks Doug for your considerable effort and reply! I will test that SQL
when I return to work.

Note: The SQL was created by Access not me from the Design View data.

Also what explains the error message when both criteria for (Boiler Street)
eg: 18th Ave. and Ave. U, are both Text strings?

nytwodees said:
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#));
Click to expand...
 
D

Douglas J. Steele

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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


nytwodees said:
Thanks Doug for your considerable effort and reply! I will test that SQL
when I return to work.

Note: The SQL was created by Access not me from the Design View data.

Also what explains the error message when both criteria for (Boiler
Street)
eg: 18th Ave. and Ave. U, are both Text strings?

nytwodees said:
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#));
Click to expand...
Click to expand...
 
N

nytwodees

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
 
D

Douglas J. Steele

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)
 
N

nytwodees

Hi Douglas:

I changed the query as you suggested by using the WHERE statement instead of
the HAVING statement (using Access 97). Again (Boiler Street) 18th Ave.
worked properly but received the Type Mismatch Error again when I substituted
Ave. U. I tried substituting : Ave. R, Ave. S, Ave. T and they all worked
fine. It is as if Ave. U was a "Reserved Word."

I also tried Filter By Form using Ave. U as the filter and got 233 records
that matched that criteria.

Where do I go from here?
 
D

Douglas J. Steele

Oh, and how are you running it? i.e. are you simply clicking on the query,
are you using it as the RecordSource for a form or a report or are you
running it programmatically? If programmatically, what's the code you're
using?
 
N

nytwodees

Hi Douglas:

Below is the SQL:

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]
WHERE ((([Boiler Inspection Customers].[Boiler Street]) Like "Ave. U*") AND
(([Boiler Inspection Customers].[Last Inspection Date]) Between #1/1/2007#
And #12/31/2008#))
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;
 
N

nytwodees

I am using it as the RecordSource for a report. However, I am testing the
query 1st by just running the query. Since the query fails "sometimes." I've
yet to run it from the report.
 
J

John W. Vinson

Hi Douglas:

I changed the query as you suggested by using the WHERE statement instead of
the HAVING statement (using Access 97). Again (Boiler Street) 18th Ave.
worked properly but received the Type Mismatch Error again when I substituted
Ave. U. I tried substituting : Ave. R, Ave. S, Ave. T and they all worked
fine. It is as if Ave. U was a "Reserved Word."
Click to expand...

That's scary. It sounds like you may have a corrupt index. Is there an index
on the street field? If so, I'd suggest:

- back up the database
- delete the index
- Compact the database
- Recreate the index
- Compact again

to see if it works better.

Also be sure to turn off Name Autocorrect if it's on.
 
N

nytwodees

Hi Douglas & John:

I think I may have inadvertently found the solution to the problem!

By the way, (Boiler Street) was not indexed (though it should have been).

I opened the Boiler Inspection Customers input form. I located a (Boiler
Street) that had "Ave. U." I then filtered by selection. 230 records
appeared in Datasheet View. I noticed that only 1 record was missing a value
for (Boiler Streetnum). Note: in designing my table, I created 2 separate
fields (Boiler Streetnum) and (Boiler Street) for the address.

I entered a value for the empty field and reran the query. Voila! The query
runs as expected.

However, I would like to know how to edit the query so that if the (Boiler
Streetnum) field is empty the query will run correctly.

Thanks for all of your help!
 
D

Douglas J. Steele

Try using the Nz function to convert null fields to zero-length strings:

WHERE (((Nz([Boiler Inspection Customers].[Boiler Street], "")) Like "Ave.
U*") AND
(([Boiler Inspection Customers].[Last Inspection Date]) Between #1/1/2007#
And #12/31/2008#))
 
J

John W. Vinson

I opened the Boiler Inspection Customers input form. I located a (Boiler
Street) that had "Ave. U." I then filtered by selection. 230 records
appeared in Datasheet View. I noticed that only 1 record was missing a value
for (Boiler Streetnum). Note: in designing my table, I created 2 separate
fields (Boiler Streetnum) and (Boiler Street) for the address.

I entered a value for the empty field and reran the query. Voila! The query
runs as expected.

However, I would like to know how to edit the query so that if the (Boiler
Streetnum) field is empty the query will run correctly.
Click to expand...

Your current query did not reference [Boiler Streetnum]: the where clause I
last saw was

WHERE ((([Boiler Inspection Customers].[Boiler Street]) Like "Ave. U*") AND
(([Boiler Inspection Customers].[Last Inspection Date]) Between #1/1/2007#
And #12/31/2008#))

Where does [Boiler Streetnum] come into it?
 
N

nytwodees

Hi Douglas & John:

I tried using the NZ function as you described, but still got the same error
message.
 
N

nytwodees

Hi John:

Where does [Boiler Streetnum] come into it?

(Boiler Streetnum) is only data that is to be displayed like (Last Name).
It was not used as part of my filter. The only reason I mentioned it at all
was that when I found 1 record that had (Boiler Streetnum) blank or empty for
(Boiler Street)=Ave. U., I became alerted. When I entered an arbitrary
number to the blank field in that record, the query ran as expected without
any error message. I don't know why that had any affect at all!

Below is the SQL:

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]
WHERE ((([Boiler Inspection Customers].[Boiler Street]) Like "Ave. U*") AND
(([Boiler Inspection Customers].[Last Inspection Date]) Between #1/1/2007#
And #12/31/2008#))
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;


John W. Vinson said:
I opened the Boiler Inspection Customers input form. I located a (Boiler
Street) that had "Ave. U." I then filtered by selection. 230 records
appeared in Datasheet View. I noticed that only 1 record was missing a value
for (Boiler Streetnum). Note: in designing my table, I created 2 separate
fields (Boiler Streetnum) and (Boiler Street) for the address.

I entered a value for the empty field and reran the query. Voila! The query
runs as expected.

However, I would like to know how to edit the query so that if the (Boiler
Streetnum) field is empty the query will run correctly.
Click to expand...

Your current query did not reference [Boiler Streetnum]: the where clause I
last saw was

WHERE ((([Boiler Inspection Customers].[Boiler Street]) Like "Ave. U*") AND
(([Boiler Inspection Customers].[Last Inspection Date]) Between #1/1/2007#
And #12/31/2008#))

Where does [Boiler Streetnum] come into it?
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

data type mismatch in query criteria 1
MsgBox in another Instance of Excel 4
'Data Type Mismatch In Criteria Expression' error when using 'Count' in query Design; Help! 0
Data Type mismatch in criteria expression - WHY?WHY?WHy? 4
Data type mismatch in criteria expression issue 2
Data type mismatch in criteria expression 5
Data Type Mismatch in Criteria Expression 2
Data type mismatch in criteria expression 7

Top