"Data type mismatch" without any criteria!

  • Thread starter Thread starter Mark Parent
  • Start date Start date
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?
 
I'm guessying that [Report4B: Step 1].[CUST-INTENT] and [Cust-Intent
Values].Code are different data types.
 
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...
 
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...
 
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.
 
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...
 
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...
 
Back
Top