Error message with query

[Joan]

Hi,
I have the following query which I get a "Data type mismatch error in
criteria expression" when I run it. I can't figure out why. It works if I
take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] - 7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate, Dogs3Query.[Received Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND ((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan

 

[John Viescas]

Are both Received Date and FinalSaleDate date/time data type? If so, it
should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that have a Null
FinalSaleDate before attempting to evaluate the predicate that does the
calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Joan]

John,
Thanks for replying to my post.

I tried changing the Where portion of the SQL to what you suggested to try
and I still get the same message. I might mention that the
Dogs3Query.FinalSaleDate field is an expression in the Dogs3Query.:
FinalSaleDate: Format(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null)),"Short Date")

[Received Date], [ReturnedDate] and [ReturnedSaleDate] are all of the
date/time data type. In the query that I get the data type mismatch
message on, I am trying to return just the dogs that were in inventory over
1 week before they were sold. [DateSold] is the date the dog is initially
sold on. [ReturnedDate] is the date a dog is returned to the seller. For
instance if the dog is sick or injured the buyer may return a dog.
[ReturnedSaleDate] is the date that a dog is resold on. [Received Date] is
the date that a dog is initially received into inventory by the seller. So
in the Dogs3Query (inventory) there are records where the FinalSaleDate is
null as in cases where the dog has not been sold for the first time or the
dog has been sold, returned but not resold. Hope this clarifiies better
what I am trying to do with my query. What do I need to do different to
make my query work as I'd like?

Joan

Are both Received Date and FinalSaleDate date/time data type? If so, it
should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that have a Null
FinalSaleDate before attempting to evaluate the predicate that does the
calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi,
I have the following query which I get a "Data type mismatch error in
criteria expression" when I run it. I can't figure out why. It works if I
take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] - 7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate, Dogs3Query.[Received Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND ((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan

 

[John Viescas]

The problem is Format returns a string, not a date/time value. Try this:

FinalSaleDate: Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null))

You still might have a problem with the Null that this expression returns if
both ReturnedSaleDate is Null and ReturnedDate is not Null.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John,
Thanks for replying to my post.

I tried changing the Where portion of the SQL to what you suggested to try
and I still get the same message. I might mention that the
Dogs3Query.FinalSaleDate field is an expression in the Dogs3Query.:
FinalSaleDate: Format(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null)),"Short Date")

[Received Date], [ReturnedDate] and [ReturnedSaleDate] are all of the
date/time data type. In the query that I get the data type mismatch
message on, I am trying to return just the dogs that were in inventory over
1 week before they were sold. [DateSold] is the date the dog is initially
sold on. [ReturnedDate] is the date a dog is returned to the seller. For
instance if the dog is sick or injured the buyer may return a dog.
[ReturnedSaleDate] is the date that a dog is resold on. [Received Date] is
the date that a dog is initially received into inventory by the seller. So
in the Dogs3Query (inventory) there are records where the FinalSaleDate is
null as in cases where the dog has not been sold for the first time or the
dog has been sold, returned but not resold. Hope this clarifiies better
what I am trying to do with my query. What do I need to do different to
make my query work as I'd like?

Joan

Are both Received Date and FinalSaleDate date/time data type? If so, it
should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that have a Null
FinalSaleDate before attempting to evaluate the predicate that does the
calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Hi,
I have the following query which I get a "Data type mismatch error in
criteria expression" when I run it. I can't figure out why. It works

if

I

take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] - 7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate, Dogs3Query.[Received Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND ((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan

 

[Joan]

Hi John,
I took out the Format function and set FinalSaleDate like you
recommended: Nz([ReturnedSaleDate], IIf([ReturnedDate] Is Null, [DateSold],
Null)). However, I still get the message: "Data type mismatch in criteria
expression" . Do you have any thoughts on what to try next?

I don't understand why my query will work if I take out the criteria: "And
((Dogs3Query.[Received Date])< [FinalSaleDate] - 7))" but leave in the
criteria: "(Dogs3Query.FinalSaleDate) Is Not Null". When leaving both
limiting criteria in the query, in my QBE pane, I have the FinalSaleDate
field before the ReceivedDate field so the query will limit the records
where FinalSaleDate Is Not Null first. I've also tried the criteria: WHERE
(Dogs3Query.FinalSaleDate Is Not Null) AND ((Dogs3Query.FinalSaleDate Is
Not Null) AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

This produced the same data type mismatch in criteria expression message.

Still puzzled.

Joan

The problem is Format returns a string, not a date/time value. Try this:

FinalSaleDate: Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null))

You still might have a problem with the Null that this expression returns if
both ReturnedSaleDate is Null and ReturnedDate is not Null.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John,
Thanks for replying to my post.

I tried changing the Where portion of the SQL to what you suggested to try
and I still get the same message. I might mention that the
Dogs3Query.FinalSaleDate field is an expression in the Dogs3Query.:
FinalSaleDate: Format(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null)),"Short Date")

[Received Date], [ReturnedDate] and [ReturnedSaleDate] are all of the
date/time data type. In the query that I get the data type mismatch
message on, I am trying to return just the dogs that were in inventory over
1 week before they were sold. [DateSold] is the date the dog is initially
sold on. [ReturnedDate] is the date a dog is returned to the seller. For
instance if the dog is sick or injured the buyer may return a dog.
[ReturnedSaleDate] is the date that a dog is resold on. [Received Date] is
the date that a dog is initially received into inventory by the seller. So
in the Dogs3Query (inventory) there are records where the FinalSaleDate is
null as in cases where the dog has not been sold for the first time or the
dog has been sold, returned but not resold. Hope this clarifiies better
what I am trying to do with my query. What do I need to do different to
make my query work as I'd like?

Joan

Are both Received Date and FinalSaleDate date/time data type? If so, it
should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that have a Null
FinalSaleDate before attempting to evaluate the predicate that does the
calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hi,
I have the following query which I get a "Data type mismatch error in
criteria expression" when I run it. I can't figure out why. It

works

if

I
take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] - 7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate, Dogs3Query.[Received
Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND ((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan

 

[John Viescas]

OK, one last shot:

FinalSaleDate: CDate(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],0)))

The problem is you're getting a Null when ReturnedSaleDate is Null and you
have a non-null ReturnedDate. (An animal that has been returned but not
resold.) So, FinalSaleDate - 7 yields a Null date. Returning a zero
(December 30, 1899) should solve the problem - the test won't return these
rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi John,
I took out the Format function and set FinalSaleDate like you
recommended: Nz([ReturnedSaleDate], IIf([ReturnedDate] Is Null, [DateSold],
Null)). However, I still get the message: "Data type mismatch in criteria
expression" . Do you have any thoughts on what to try next?

I don't understand why my query will work if I take out the criteria: "And
((Dogs3Query.[Received Date])< [FinalSaleDate] - 7))" but leave in the
criteria: "(Dogs3Query.FinalSaleDate) Is Not Null". When leaving both
limiting criteria in the query, in my QBE pane, I have the FinalSaleDate
field before the ReceivedDate field so the query will limit the records
where FinalSaleDate Is Not Null first. I've also tried the criteria: WHERE
(Dogs3Query.FinalSaleDate Is Not Null) AND ((Dogs3Query.FinalSaleDate Is
Not Null) AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

This produced the same data type mismatch in criteria expression message.

Still puzzled.

Joan

The problem is Format returns a string, not a date/time value. Try this:

FinalSaleDate: Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null))

You still might have a problem with the Null that this expression

returns

if
both ReturnedSaleDate is Null and ReturnedDate is not Null.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

John,
Thanks for replying to my post.

I tried changing the Where portion of the SQL to what you suggested to try
and I still get the same message. I might mention that the
Dogs3Query.FinalSaleDate field is an expression in the Dogs3Query.:
FinalSaleDate: Format(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null)),"Short Date")

[Received Date], [ReturnedDate] and [ReturnedSaleDate] are all of the
date/time data type. In the query that I get the data type mismatch
message on, I am trying to return just the dogs that were in inventory over
1 week before they were sold. [DateSold] is the date the dog is initially
sold on. [ReturnedDate] is the date a dog is returned to the seller. For
instance if the dog is sick or injured the buyer may return a dog.
[ReturnedSaleDate] is the date that a dog is resold on. [Received

Date]

is seller.
So

FinalSaleDate

is

so,

it

should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that have

a
Null

FinalSaleDate before attempting to evaluate the predicate that does the
calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hi,
I have the following query which I get a "Data type mismatch error in
criteria expression" when I run it. I can't figure out why. It

works

if
I
take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] - 7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate, Dogs3Query.[Received
Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND
((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan

 

[Joan]

John,

Sorry, I'm just not getting this. When I put : CDate(Nz([ReturnedSaleDate],
IIf([ReturnedDate] Is Null, [DateSold],0))) as the expression for
FinalSaleDate in the Dogs3Query and look at the datasheet view of the query,
there are "#Error"s in the FinalSaleDate field of every record where
[DateSold] is blank (the dog has never been sold) . I'd like FinalSaleDate
to just be blank like [DateSold] is. There is "12:00:00AM" in the
FinalSaleDate field of records where the [ReturnedDate] is not blank and the
[ReturnedSaleDate] is blank ( the dog was originally sold, but then was
returned and has not been resold). Here also, I would like FinalSaleDate to
be blank. When I run the other query with the limiting criteria in it, I
get the message: "Invalid use of Null".

Joan

OK, one last shot:

FinalSaleDate: CDate(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],0)))

The problem is you're getting a Null when ReturnedSaleDate is Null and you
have a non-null ReturnedDate. (An animal that has been returned but not
resold.) So, FinalSaleDate - 7 yields a Null date. Returning a zero
(December 30, 1899) should solve the problem - the test won't return these
rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi John,
I took out the Format function and set FinalSaleDate like you
recommended: Nz([ReturnedSaleDate], IIf([ReturnedDate] Is Null, [DateSold],
Null)). However, I still get the message: "Data type mismatch in criteria
expression" . Do you have any thoughts on what to try next?

I don't understand why my query will work if I take out the criteria: "And
((Dogs3Query.[Received Date])< [FinalSaleDate] - 7))" but leave in the
criteria: "(Dogs3Query.FinalSaleDate) Is Not Null". When leaving both
limiting criteria in the query, in my QBE pane, I have the FinalSaleDate
field before the ReceivedDate field so the query will limit the records
where FinalSaleDate Is Not Null first. I've also tried the criteria: WHERE
(Dogs3Query.FinalSaleDate Is Not Null) AND ((Dogs3Query.FinalSaleDate Is
Not Null) AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

This produced the same data type mismatch in criteria expression message.

Still puzzled.

Joan

The problem is Format returns a string, not a date/time value. Try this:

FinalSaleDate: Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null))

You still might have a problem with the Null that this expression

returns

if
both ReturnedSaleDate is Null and ReturnedDate is not Null.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John,
Thanks for replying to my post.

I tried changing the Where portion of the SQL to what you suggested

to
try

and I still get the same message. I might mention that the
Dogs3Query.FinalSaleDate field is an expression in the Dogs3Query.:
FinalSaleDate: Format(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null)),"Short Date")

[Received Date], [ReturnedDate] and [ReturnedSaleDate] are all of the
date/time data type. In the query that I get the data type mismatch
message on, I am trying to return just the dogs that were in inventory
over
1 week before they were sold. [DateSold] is the date the dog is initially
sold on. [ReturnedDate] is the date a dog is returned to the seller. For
instance if the dog is sick or injured the buyer may return a dog.
[ReturnedSaleDate] is the date that a dog is resold on. [Received

Date]

is
the date that a dog is initially received into inventory by the seller.
So
in the Dogs3Query (inventory) there are records where the

FinalSaleDate

is
null as in cases where the dog has not been sold for the first time

or
the

dog has been sold, returned but not resold. Hope this clarifiies better
what I am trying to do with my query. What do I need to do

different
to

make my query work as I'd like?

Joan




Are both Received Date and FinalSaleDate date/time data type? If

so,

it
should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that

have

a

Null
FinalSaleDate before attempting to evaluate the predicate that

does
the

calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hi,
I have the following query which I get a "Data type mismatch

error
in

criteria expression" when I run it. I can't figure out why. It works
if
I
take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] - 7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate, Dogs3Query.[Received
Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND
((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan

 

[Joan]

John,

Also see my other reply first. I tried setting FinalSaleDate to:
Nz([ReturnedSaleDate],IIf([ReturnedDate] Is Null,IIf([DateSold] Is
Null,"",[DateSold])," ")). In the datasheet view of the Dogs3Query the
FinalSaleDate field appears as it should. Then in the next query, I set the
criteria of FinalSaleDate to: <>" ". This returns all records with the
FinalSaleDate field filled in. If I set the criteria of FinalSaleDate to:
<>"" And >([Received Date]+7), some of the records it returns are correct,
the FinalSaleDate is over a week past the [Received Date]. However, there
are three records which should be returned that aren't. If I change the
FinalSaleDate on these records so that it is within a week of the [Received
Date] then the query returns those. In other words, for these 3 records the
query does just the opposite of what the limiting criteria states. Puzzled
by why it is doing this. No error messages, just not behaving as one would
think that the query should behave given the criteria. I've looked at the 3
records to see if I can see anything that is different from the others that
the query returns, but have not been able to detect anything. Do you have
an idea of what would cause a query to act in such a fashion?

Thanks for you help.
Joan

OK, one last shot:

FinalSaleDate: CDate(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],0)))

The problem is you're getting a Null when ReturnedSaleDate is Null and you
have a non-null ReturnedDate. (An animal that has been returned but not
resold.) So, FinalSaleDate - 7 yields a Null date. Returning a zero
(December 30, 1899) should solve the problem - the test won't return these
rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi John,
I took out the Format function and set FinalSaleDate like you
recommended: Nz([ReturnedSaleDate], IIf([ReturnedDate] Is Null, [DateSold],
Null)). However, I still get the message: "Data type mismatch in criteria
expression" . Do you have any thoughts on what to try next?

I don't understand why my query will work if I take out the criteria: "And
((Dogs3Query.[Received Date])< [FinalSaleDate] - 7))" but leave in the
criteria: "(Dogs3Query.FinalSaleDate) Is Not Null". When leaving both
limiting criteria in the query, in my QBE pane, I have the FinalSaleDate
field before the ReceivedDate field so the query will limit the records
where FinalSaleDate Is Not Null first. I've also tried the criteria: WHERE
(Dogs3Query.FinalSaleDate Is Not Null) AND ((Dogs3Query.FinalSaleDate Is
Not Null) AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

This produced the same data type mismatch in criteria expression message.

Still puzzled.

Joan

The problem is Format returns a string, not a date/time value. Try this:

FinalSaleDate: Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null))

You still might have a problem with the Null that this expression

returns

if
both ReturnedSaleDate is Null and ReturnedDate is not Null.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John,
Thanks for replying to my post.

I tried changing the Where portion of the SQL to what you suggested

to
try

and I still get the same message. I might mention that the
Dogs3Query.FinalSaleDate field is an expression in the Dogs3Query.:
FinalSaleDate: Format(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null)),"Short Date")

[Received Date], [ReturnedDate] and [ReturnedSaleDate] are all of the
date/time data type. In the query that I get the data type mismatch
message on, I am trying to return just the dogs that were in inventory
over
1 week before they were sold. [DateSold] is the date the dog is initially
sold on. [ReturnedDate] is the date a dog is returned to the seller. For
instance if the dog is sick or injured the buyer may return a dog.
[ReturnedSaleDate] is the date that a dog is resold on. [Received

Date]

is
the date that a dog is initially received into inventory by the seller.
So
in the Dogs3Query (inventory) there are records where the

FinalSaleDate

is
null as in cases where the dog has not been sold for the first time

or
the

dog has been sold, returned but not resold. Hope this clarifiies better
what I am trying to do with my query. What do I need to do

different
to

make my query work as I'd like?

Joan




Are both Received Date and FinalSaleDate date/time data type? If

so,

it
should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that

have

a

Null
FinalSaleDate before attempting to evaluate the predicate that

does
the

calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hi,
I have the following query which I get a "Data type mismatch

error
in

criteria expression" when I run it. I can't figure out why. It works
if
I
take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] - 7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate, Dogs3Query.[Received
Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND
((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan

 

[John Viescas]

Arrrggh. The problem is with the animals that aren't sold at all. Perhaps
you should eliminate those rows in the original query:

WHERE DateSold Is Not Null

Blanks won't help. A blank Date/Time value isn't valid - or will give
strange comparison results. How about:

FinalSaleDate: IIF(IsNull([ReturnedSaleDate]), IIF(IsNull([DateSold]),
CDate(1), [DateSold]), [ReturnedSaleDate])

That gives you a valid date/time value for FinalSaleDate - but will return
December 31, 1899 if the animal is not finally sold. Your test for animals
sold in the last 7 days in the subsequent query should work and not return
any rows for animals not "finally" sold.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

John,

Also see my other reply first. I tried setting FinalSaleDate to:
Nz([ReturnedSaleDate],IIf([ReturnedDate] Is Null,IIf([DateSold] Is
Null,"",[DateSold])," ")). In the datasheet view of the Dogs3Query the
FinalSaleDate field appears as it should. Then in the next query, I set the
criteria of FinalSaleDate to: <>" ". This returns all records with the
FinalSaleDate field filled in. If I set the criteria of FinalSaleDate to:
<>"" And >([Received Date]+7), some of the records it returns are correct,
the FinalSaleDate is over a week past the [Received Date]. However, there
are three records which should be returned that aren't. If I change the
FinalSaleDate on these records so that it is within a week of the [Received
Date] then the query returns those. In other words, for these 3 records the
query does just the opposite of what the limiting criteria states. Puzzled
by why it is doing this. No error messages, just not behaving as one would
think that the query should behave given the criteria. I've looked at the 3
records to see if I can see anything that is different from the others that
the query returns, but have not been able to detect anything. Do you have
an idea of what would cause a query to act in such a fashion?

Thanks for you help.
Joan

OK, one last shot:

FinalSaleDate: CDate(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],0)))

The problem is you're getting a Null when ReturnedSaleDate is Null and you
have a non-null ReturnedDate. (An animal that has been returned but not
resold.) So, FinalSaleDate - 7 yields a Null date. Returning a zero
(December 30, 1899) should solve the problem - the test won't return these
rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi John,
I took out the Format function and set FinalSaleDate like you
recommended: Nz([ReturnedSaleDate], IIf([ReturnedDate] Is Null, [DateSold],
Null)). However, I still get the message: "Data type mismatch in criteria
expression" . Do you have any thoughts on what to try next?

I don't understand why my query will work if I take out the criteria: "And
((Dogs3Query.[Received Date])< [FinalSaleDate] - 7))" but leave in the
criteria: "(Dogs3Query.FinalSaleDate) Is Not Null". When leaving both
limiting criteria in the query, in my QBE pane, I have the FinalSaleDate
field before the ReceivedDate field so the query will limit the records
where FinalSaleDate Is Not Null first. I've also tried the criteria: WHERE
(Dogs3Query.FinalSaleDate Is Not Null) AND ((Dogs3Query.FinalSaleDate Is
Not Null) AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

This produced the same data type mismatch in criteria expression message.

Still puzzled.

Joan




The problem is Format returns a string, not a date/time value. Try this:

FinalSaleDate: Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null))

You still might have a problem with the Null that this expression returns
if
both ReturnedSaleDate is Null and ReturnedDate is not Null.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John,
Thanks for replying to my post.

I tried changing the Where portion of the SQL to what you

suggested

to

try
and I still get the same message. I might mention that the
Dogs3Query.FinalSaleDate field is an expression in the Dogs3Query.:
FinalSaleDate: Format(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null)),"Short Date")

[Received Date], [ReturnedDate] and [ReturnedSaleDate] are all of the
date/time data type. In the query that I get the data type mismatch
message on, I am trying to return just the dogs that were in inventory
over
1 week before they were sold. [DateSold] is the date the dog is
initially
sold on. [ReturnedDate] is the date a dog is returned to the

seller.
For

instance if the dog is sick or injured the buyer may return a dog.
[ReturnedSaleDate] is the date that a dog is resold on. [Received Date]
is
the date that a dog is initially received into inventory by the seller.
So
in the Dogs3Query (inventory) there are records where the FinalSaleDate
is
null as in cases where the dog has not been sold for the first

time

or

the
dog has been sold, returned but not resold. Hope this clarifiies better
what I am trying to do with my query. What do I need to do different
to
make my query work as I'd like?

Joan




Are both Received Date and FinalSaleDate date/time data type?

If
so,

it
should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that

have

a
Null
FinalSaleDate before attempting to evaluate the predicate that does
the
calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hi,
I have the following query which I get a "Data type mismatch error
in
criteria expression" when I run it. I can't figure out why. It
works
if
I
take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] - 7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate,
Dogs3Query.[Received
Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND
((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan

 

[Joan]

John,
Thank you so much! My query is now working great and I can build my
report. I so appreciate all the help you have given me.
Joan

Arrrggh. The problem is with the animals that aren't sold at all. Perhaps
you should eliminate those rows in the original query:

WHERE DateSold Is Not Null

Blanks won't help. A blank Date/Time value isn't valid - or will give
strange comparison results. How about:

FinalSaleDate: IIF(IsNull([ReturnedSaleDate]), IIF(IsNull([DateSold]),
CDate(1), [DateSold]), [ReturnedSaleDate])

That gives you a valid date/time value for FinalSaleDate - but will return
December 31, 1899 if the animal is not finally sold. Your test for animals
sold in the last 7 days in the subsequent query should work and not return
any rows for animals not "finally" sold.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

John,

Also see my other reply first. I tried setting FinalSaleDate to:
Nz([ReturnedSaleDate],IIf([ReturnedDate] Is Null,IIf([DateSold] Is
Null,"",[DateSold])," ")). In the datasheet view of the Dogs3Query the
FinalSaleDate field appears as it should. Then in the next query, I set the
criteria of FinalSaleDate to: <>" ". This returns all records with the
FinalSaleDate field filled in. If I set the criteria of FinalSaleDate to:
<>"" And >([Received Date]+7), some of the records it returns are correct,
the FinalSaleDate is over a week past the [Received Date]. However, there
are three records which should be returned that aren't. If I change the
FinalSaleDate on these records so that it is within a week of the [Received
Date] then the query returns those. In other words, for these 3 records the
query does just the opposite of what the limiting criteria states. Puzzled
by why it is doing this. No error messages, just not behaving as one would
think that the query should behave given the criteria. I've looked at

the

3
records to see if I can see anything that is different from the others that
the query returns, but have not been able to detect anything. Do you have
an idea of what would cause a query to act in such a fashion?

Thanks for you help.
Joan

OK, one last shot:

FinalSaleDate: CDate(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],0)))

The problem is you're getting a Null when ReturnedSaleDate is Null and you
have a non-null ReturnedDate. (An animal that has been returned but not
resold.) So, FinalSaleDate - 7 yields a Null date. Returning a zero
(December 30, 1899) should solve the problem - the test won't return these
rows.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
Hi John,
I took out the Format function and set FinalSaleDate like you
recommended: Nz([ReturnedSaleDate], IIf([ReturnedDate] Is Null,
[DateSold],
Null)). However, I still get the message: "Data type mismatch in
criteria
expression" . Do you have any thoughts on what to try next?

I don't understand why my query will work if I take out the criteria:
"And
((Dogs3Query.[Received Date])< [FinalSaleDate] - 7))" but leave in the
criteria: "(Dogs3Query.FinalSaleDate) Is Not Null". When leaving both
limiting criteria in the query, in my QBE pane, I have the FinalSaleDate
field before the ReceivedDate field so the query will limit the records
where FinalSaleDate Is Not Null first. I've also tried the criteria: WHERE
(Dogs3Query.FinalSaleDate Is Not Null) AND

((Dogs3Query.FinalSaleDate
Is

Not Null) AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

This produced the same data type mismatch in criteria expression message.

Still puzzled.

Joan




The problem is Format returns a string, not a date/time value. Try
this:

FinalSaleDate: Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null))

You still might have a problem with the Null that this expression
returns
if
both ReturnedSaleDate is Null and ReturnedDate is not Null.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
John,
Thanks for replying to my post.

I tried changing the Where portion of the SQL to what you

suggested

to
try
and I still get the same message. I might mention that the
Dogs3Query.FinalSaleDate field is an expression in the Dogs3Query.:
FinalSaleDate: Format(Nz([ReturnedSaleDate],IIf([ReturnedDate] Is
Null,[DateSold],Null)),"Short Date")

[Received Date], [ReturnedDate] and [ReturnedSaleDate] are all

of
the

date/time data type. In the query that I get the data type mismatch
message on, I am trying to return just the dogs that were in inventory
over
1 week before they were sold. [DateSold] is the date the dog is
initially
sold on. [ReturnedDate] is the date a dog is returned to the seller.
For
instance if the dog is sick or injured the buyer may return a dog.
[ReturnedSaleDate] is the date that a dog is resold on. [Received
Date]
is
the date that a dog is initially received into inventory by the
seller.
So
in the Dogs3Query (inventory) there are records where the
FinalSaleDate
is
null as in cases where the dog has not been sold for the first

time

or
the
dog has been sold, returned but not resold. Hope this clarifiies
better
what I am trying to do with my query. What do I need to do different
to
make my query work as I'd like?

Joan




Are both Received Date and FinalSaleDate date/time data type? If
so,
it
should work - unless FinalSaleDate contains a Null. You could try:

WHERE (Dogs3Query.FinalSaleDate Is Not Null) AND
((Dogs3Query.FinalSaleDate Is Not Null)
AND (Dogs3Query.[Received Date] < ([FinalSaleDate]-7)));

Stated this way, the query engine should fail any records that have
a
Null
FinalSaleDate before attempting to evaluate the predicate that does
the
calculation.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hi,
I have the following query which I get a "Data type mismatch error
in
criteria expression" when I run it. I can't figure out why. It
works
if
I
take out "And ((Dogs3Query.[Received Date])< [FinalSaleDate] -
7)).




SELECT Dogs3Query.Salesperson, Dogs3Query.[Dog Number],
Dogs3Query.FinalStore, Dogs3Query.FinalSaleDate,
Dogs3Query.[Received
Date]
FROM Dogs3Query
WHERE (((Dogs3Query.FinalSaleDate) Is Not Null) AND
((Dogs3Query.[Received
Date])<[FinalSaleDate]-7));

Joan