"Data type mismatch" without any criteria!

M

Mark Parent

I'm receiving a data type mismatch on a query that groups, but specifies no
criteria at all. This query does rely on a previous query with a criteria,
but that query on its own works fine. The second query SQL appears below:

SELECT [Report4B: Step 1].Flag, [Cust-Intent Values].Desc, Count([Report4B:
Step 1].ID) AS CountOfID
FROM [Report4B: Step 1] LEFT JOIN [Cust-Intent Values]
ON [Report4B: Step 1].[CUST-INTENT] = [Cust-Intent Values].Code
GROUP BY [Report4B: Step 1].Flag, [Cust-Intent Values].Desc;

There is no WHERE clause because I'm specifying no criteria. What's up?
 
D

Douglas J. Steele

I'm guessying that [Report4B: Step 1].[CUST-INTENT] and [Cust-Intent
Values].Code are different data types.
 
M

Mark Parent

I've checked, and both are defined as Text.

I've rechecked the source query, and there are no joins there. Since the
first query displays properly, is there any chance the error is coming from
there because it's a prerequisite for this query? (There is a criteria
there, although I'm simply setting a field to "Yes" or "No" and selecting the
'Yes")


Douglas J. Steele said:
I'm guessying that [Report4B: Step 1].[CUST-INTENT] and [Cust-Intent
Values].Code are different data types.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mark Parent said:
I'm receiving a data type mismatch on a query that groups, but specifies
no
criteria at all. This query does rely on a previous query with a
criteria,
but that query on its own works fine. The second query SQL appears below:

SELECT [Report4B: Step 1].Flag, [Cust-Intent Values].Desc,
Count([Report4B:
Step 1].ID) AS CountOfID
FROM [Report4B: Step 1] LEFT JOIN [Cust-Intent Values]
ON [Report4B: Step 1].[CUST-INTENT] = [Cust-Intent Values].Code
GROUP BY [Report4B: Step 1].Flag, [Cust-Intent Values].Desc;

There is no WHERE clause because I'm specifying no criteria. What's up?
Click to expand...
Click to expand...
 
D

Douglas J. Steele

It doesn't sound too likely.

What's the SQL of the source query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mark Parent said:
I've checked, and both are defined as Text.

I've rechecked the source query, and there are no joins there. Since the
first query displays properly, is there any chance the error is coming
from
there because it's a prerequisite for this query? (There is a criteria
there, although I'm simply setting a field to "Yes" or "No" and selecting
the
'Yes")


Douglas J. Steele said:
I'm guessying that [Report4B: Step 1].[CUST-INTENT] and [Cust-Intent
Values].Code are different data types.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Mark Parent said:
I'm receiving a data type mismatch on a query that groups, but
specifies
no
criteria at all. This query does rely on a previous query with a
criteria,
but that query on its own works fine. The second query SQL appears
below:

SELECT [Report4B: Step 1].Flag, [Cust-Intent Values].Desc,
Count([Report4B:
Step 1].ID) AS CountOfID
FROM [Report4B: Step 1] LEFT JOIN [Cust-Intent Values]
ON [Report4B: Step 1].[CUST-INTENT] = [Cust-Intent Values].Code
GROUP BY [Report4B: Step 1].Flag, [Cust-Intent Values].Desc;

There is no WHERE clause because I'm specifying no criteria. What's
up?
Click to expand...
Click to expand...
Click to expand...
 
M

Mark Parent

This is the first query:


SELECT IIf([CUST-INTENT]="1" And [RENEW-MTG-TD]="YES","NO","YES") AS
[OFI-FLAG], IIf([PERIOD]-[Present]>4,"More than 4 months","Less than or equal
to 4 months") AS Flag, [MTG Elsewhere Campaign].ID, Val([YEAR] & [MONTH]) AS
Period, (Year(Date())*100)+Month(Date()) AS Present, [MTG Elsewhere
Campaign].[CUST-INTENT],
IIf([Cust-Intent]="1",[RENEW-MTG-OFI],IIf([Cust-Intent]="4" Or
[Cust-Intent]="3" Or [Cust-Intent]="2","","XX")) AS OFI,
IIf([Cust-Intent]="1",IIf([RENEW-MTG-TD]="NO",[RENEW-MONTH],0),IIf([Cust-Intent]="4",[REFIN-MONTH],IIf([Cust-Intent]="3",[RENOV-MONTH],IIf([Cust-Intent]="2",[PURCH-MONTH]
& [PURCH-NEXT-MONTH],"XX")))) AS [MONTH], Val([MONTH]) AS VMONTH,
IIf([Cust-Intent]="1",IIf([RENEW-MTG-TD]="NO",[RENEW-YEAR],0),IIf([Cust-Intent]='4',[REFIN-YEAR],IIf([Cust-Intent]='3',[RENOV-YEAR],IIf([Cust-Intent]='2',[PURCH-YEAR] & [PURCH-NEXT-YEAR],"XX")))) AS [YEAR], Val([YEAR]) AS VYEAR
FROM [MTG Elsewhere Campaign]
WHERE (((IIf([CUST-INTENT]="1" And [RENEW-MTG-TD]="YES","NO","YES"))="YES"));

Other than ID (autonumber) all fields are TEXT.
 
D

Duane Hookom

I attempt to avoid using calculated column names in other expressions in a
query ie: Present and PERIOD

At least one of your IIf() might return either a number 0 or text
[RENEW-YEAR] which isn't good practice.

I'm not sure why you have mixed single quotes in with double quotes?
--
Duane Hookom
Microsoft Access MVP


Mark Parent said:
This is the first query:


SELECT IIf([CUST-INTENT]="1" And [RENEW-MTG-TD]="YES","NO","YES") AS
[OFI-FLAG], IIf([PERIOD]-[Present]>4,"More than 4 months","Less than or equal
to 4 months") AS Flag, [MTG Elsewhere Campaign].ID, Val([YEAR] & [MONTH]) AS
Period, (Year(Date())*100)+Month(Date()) AS Present, [MTG Elsewhere
Campaign].[CUST-INTENT],
IIf([Cust-Intent]="1",[RENEW-MTG-OFI],IIf([Cust-Intent]="4" Or
[Cust-Intent]="3" Or [Cust-Intent]="2","","XX")) AS OFI,
IIf([Cust-Intent]="1",IIf([RENEW-MTG-TD]="NO",[RENEW-MONTH],0),IIf([Cust-Intent]="4",[REFIN-MONTH],IIf([Cust-Intent]="3",[RENOV-MONTH],IIf([Cust-Intent]="2",[PURCH-MONTH]
& [PURCH-NEXT-MONTH],"XX")))) AS [MONTH], Val([MONTH]) AS VMONTH,
IIf([Cust-Intent]="1",IIf([RENEW-MTG-TD]="NO",[RENEW-YEAR],0),IIf([Cust-Intent]='4',[REFIN-YEAR],IIf([Cust-Intent]='3',[RENOV-YEAR],IIf([Cust-Intent]='2',[PURCH-YEAR] & [PURCH-NEXT-YEAR],"XX")))) AS [YEAR], Val([YEAR]) AS VYEAR
FROM [MTG Elsewhere Campaign]
WHERE (((IIf([CUST-INTENT]="1" And [RENEW-MTG-TD]="YES","NO","YES"))="YES"));

Other than ID (autonumber) all fields are TEXT.



Mark Parent said:
I'm receiving a data type mismatch on a query that groups, but specifies no
criteria at all. This query does rely on a previous query with a criteria,
but that query on its own works fine. The second query SQL appears below:

SELECT [Report4B: Step 1].Flag, [Cust-Intent Values].Desc, Count([Report4B:
Step 1].ID) AS CountOfID
FROM [Report4B: Step 1] LEFT JOIN [Cust-Intent Values]
ON [Report4B: Step 1].[CUST-INTENT] = [Cust-Intent Values].Code
GROUP BY [Report4B: Step 1].Flag, [Cust-Intent Values].Desc;

There is no WHERE clause because I'm specifying no criteria. What's up?
Click to expand...
Click to expand...
 
M

Mark Parent

Duane:

Thanks for your note, and the note that I've mixed single and double quotes;
I suppose that's the effect of using too many programming languages....

At the end of the day, I've discovered my problem. The underlying table now
has some fields which cause the field PERIOD in the first query to have the
value #ERROR. While the first query works, these values then cause problems
with the second. The error message doesn't seem particularly appropriate at
first blush, but I've resolved the problem, and learned something, so the
time wasn't a waste.

Thank you and Douglas for your help and continued support. All the best!

Duane Hookom said:
I attempt to avoid using calculated column names in other expressions in a
query ie: Present and PERIOD

At least one of your IIf() might return either a number 0 or text
[RENEW-YEAR] which isn't good practice.

I'm not sure why you have mixed single quotes in with double quotes?
--
Duane Hookom
Microsoft Access MVP


Mark Parent said:
This is the first query:


SELECT IIf([CUST-INTENT]="1" And [RENEW-MTG-TD]="YES","NO","YES") AS
[OFI-FLAG], IIf([PERIOD]-[Present]>4,"More than 4 months","Less than or equal
to 4 months") AS Flag, [MTG Elsewhere Campaign].ID, Val([YEAR] & [MONTH]) AS
Period, (Year(Date())*100)+Month(Date()) AS Present, [MTG Elsewhere
Campaign].[CUST-INTENT],
IIf([Cust-Intent]="1",[RENEW-MTG-OFI],IIf([Cust-Intent]="4" Or
[Cust-Intent]="3" Or [Cust-Intent]="2","","XX")) AS OFI,
IIf([Cust-Intent]="1",IIf([RENEW-MTG-TD]="NO",[RENEW-MONTH],0),IIf([Cust-Intent]="4",[REFIN-MONTH],IIf([Cust-Intent]="3",[RENOV-MONTH],IIf([Cust-Intent]="2",[PURCH-MONTH]
& [PURCH-NEXT-MONTH],"XX")))) AS [MONTH], Val([MONTH]) AS VMONTH,
IIf([Cust-Intent]="1",IIf([RENEW-MTG-TD]="NO",[RENEW-YEAR],0),IIf([Cust-Intent]='4',[REFIN-YEAR],IIf([Cust-Intent]='3',[RENOV-YEAR],IIf([Cust-Intent]='2',[PURCH-YEAR] & [PURCH-NEXT-YEAR],"XX")))) AS [YEAR], Val([YEAR]) AS VYEAR
FROM [MTG Elsewhere Campaign]
WHERE (((IIf([CUST-INTENT]="1" And [RENEW-MTG-TD]="YES","NO","YES"))="YES"));

Other than ID (autonumber) all fields are TEXT.



Mark Parent said:
I'm receiving a data type mismatch on a query that groups, but specifies no
criteria at all. This query does rely on a previous query with a criteria,
but that query on its own works fine. The second query SQL appears below:

SELECT [Report4B: Step 1].Flag, [Cust-Intent Values].Desc, Count([Report4B:
Step 1].ID) AS CountOfID
FROM [Report4B: Step 1] LEFT JOIN [Cust-Intent Values]
ON [Report4B: Step 1].[CUST-INTENT] = [Cust-Intent Values].Code
GROUP BY [Report4B: Step 1].Flag, [Cust-Intent Values].Desc;

There is no WHERE clause because I'm specifying no criteria. What's up?
Click to expand...
Click to expand...
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 Criteria Expression' error when using 'Count' in query Design; Help! 0
data mismatch 2
Data type mismatch in criteria expression issue 2
Data type mismatch in criteria expression 5
Need some PWA help 0
Using IIF statement in query criteria 11
'Data type mismatch in criteria expression' error in Count query 4
'Data type mismatch in criteria expression' error in Count query 1

Top