If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Data type mismatch" without any criteria!
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? |
#2
|
|||
|
|||
"Data type mismatch" without any criteria!
I'm guessying that [Report4B: Step 1].[CUST-INTENT] and [Cust-Intent
Values].Code are different data types. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Mark Parent" wrote in message ... 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? |
#3
|
|||
|
|||
"Data type mismatch" without any criteria!
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" wrote: I'm guessying that [Report4B: Step 1].[CUST-INTENT] and [Cust-Intent Values].Code are different data types. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Mark Parent" wrote in message ... 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? |
#4
|
|||
|
|||
"Data type mismatch" without any criteria!
It doesn't sound too likely.
What's the SQL of the source query? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Mark Parent" wrote in message ... 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" wrote: I'm guessying that [Report4B: Step 1].[CUST-INTENT] and [Cust-Intent Values].Code are different data types. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Mark Parent" wrote in message ... 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? |
#5
|
|||
|
|||
"Data type mismatch" without any criteria!
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" wrote: 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? |
#6
|
|||
|
|||
"Data type mismatch" without any criteria!
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" wrote: 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" wrote: 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? |
#7
|
|||
|
|||
"Data type mismatch" without any criteria!
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" wrote: 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" wrote: 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" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|