data type mismatch

[jadams0173]

I have an idea that they problem lies in the LIKE part of the statement. Can someone please help me out with this. I'm using A2K. Thanks in advance!

sqlThursday = "SELECT qryThurCommits.F1, qryThurCommits.THURSDAYCOMMIT, BOM_WC_TOOL_WAVE_ONLY.[WCT_SEC/BRD], Count(qry_NewDailyWip.SFCNUMBER) AS SFCsInWip, (Count([SFCNUMBER])-[ThursDAYCommit])*[ATTR] AS NeedToWave, IIf(IsNull([waveprocess]),'NEEDS REVIEW',[waveprocess]) AS Wave, IIf(IsNull([maxofQuantity]),0,([maxofQUANTITY])*3600) AS [GateCycleTime_(SEC/CRD)], IIf(Count([SFCNUMBER])>=[ThursdayCommit],'N','Y') AS YN, ([BOM_WC_TOOL_WAVE_ONLY]![WCT_SEC/BRD]/3600)*Abs([NeedToWave]) AS RunTime, IIf(IsNull([YIELD]),85,[YIELD]) AS NEWYIELD, ((100-[NEWYIELD])/100)+1 AS ATTR, BOM_WC_TOOL_WAVE_ONLY.Letter, IIf(IsNull([CountOfSFCNUMBER]),0,[COUNTOFSFCNUMBER]) AS WIP_PRE_WAVE, Abs((Count([SFCNUMBER])-[ThursDAYCommit])*((100-IIf(IsNull([YIELD]),85,[YIELD]))/100)+1*IIf(IsNull([maxofQuantity]),0,([maxofQUANTITY])*3600)) AS Priority " _
            & " INTO " & TabName _
            & " FROM ((((qryThurCommits LEFT JOIN qry_NewDailyWip ON qryThurCommits.F1 = qry_NewDailyWip.F1) LEFT JOIN BOM_WC_TOOL_WAVE_ONLY ON qryThurCommits.F1 = BOM_WC_TOOL_WAVE_ONLY.PARENT) LEFT JOIN QRY_UNIQUE_YIELD_WITH_REV ON qryThurCommits.F1 = QRY_UNIQUE_YIELD_WITH_REV.ITEM) LEFT JOIN QRY_WIP_PREWAVE ON qryThurCommits.F1 = QRY_WIP_PREWAVE.ASSY_ID) LEFT JOIN QRY_Gating_operation ON qryThurCommits.F1 = QRY_Gating_operation.F1 " _
            & " GROUP BY qryThurCommits.F1, qryThurCommits.THURSDAYCOMMIT, BOM_WC_TOOL_WAVE_ONLY.[WCT_SEC/BRD], IIf(IsNull([waveprocess]),'NEEDS REVIEW',[waveprocess]), IIf(IsNull([maxofQuantity]),0,([maxofQUANTITY])*3600), IIf(IsNull([YIELD]),85,[YIELD]), BOM_WC_TOOL_WAVE_ONLY.Letter, IIf(IsNull([CountOfSFCNUMBER]),0,[COUNTOFSFCNUMBER])" _
            & " HAVING (((qryThurCommits.F1) Like " * " & 13 & " * "  Or (qryThurCommits.F1) Like " * " & 9 & " * " Or (qryThurCommits.F1) Like " * " & 199 & " * " Or (qryThurCommits.F1) Like " * " & 303 & " * " Or (qryThurCommits.F1) Like " * " & 79 & " * " Or (qryThurCommits.F1) Like " * " & 16 & " * ") And Count([SFCNUMBER]) < [ThursdayCommit]" _
            & " ORDER BY Abs((Count([SFCNUMBER])-[ThursDAYCommit])*((100-IIf(IsNull([YIELD]),85,[YIELD]))/100)+1*IIf(IsNull([maxofQuantity]),0,([maxofQUANTITY])*3600)) DESC"

 

[jebry]

Hi!

You need quotes around your numbers:

"303"
etc.

Are you sure you want the spaces before and after the numbers? Right now your sql will read:

Like * 303 * Or

It seems that it should be:

Like *303* Or

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[lespaul]

SELECT 
   qryThurCommits.F1, 
   qryThurCommits.THURSDAYCOMMIT, 
   BOM_WC_TOOL_WAVE_ONLY.[WCT_SEC/BRD], 
   Count(qry_NewDailyWip.SFCNUMBER) AS SFCsInWip, 
   (Count([SFCNUMBER])-[ThursDAYCommit])*[ATTR] AS NeedToWave, 
   IIf(IsNull([waveprocess]),'NEEDS REVIEW',[waveprocess]) AS Wave, 
   IIf(IsNull([maxofQuantity]),0,([maxofQUANTITY])*3600) AS [GateCycleTime_(SEC/CRD)], 
   IIf(Count([SFCNUMBER])>=[ThursdayCommit],'N','Y') AS YN, 
   ([BOM_WC_TOOL_WAVE_ONLY]![WCT_SEC/BRD]/3600)*Abs([NeedToWave]) AS RunTime, 
   IIf(IsNull([YIELD]),85,[YIELD]) AS NEWYIELD, ((100-[NEWYIELD])/100)+1 AS ATTR, 
   BOM_WC_TOOL_WAVE_ONLY.Letter, 
   IIf(IsNull([CountOfSFCNUMBER]),0,[COUNTOFSFCNUMBER]) AS WIP_PRE_WAVE, 
   Abs((Count([SFCNUMBER])-[ThursDAYCommit])*((100-IIf(IsNull([YIELD]),85,[YIELD]))/100)+1*IIf(IsNull([maxofQuantity]),0,([maxofQUANTITY])*3600)) AS Priority  

INTO TestTable

FROM ((((qryThurCommits 
   LEFT JOIN qry_NewDailyWip ON qryThurCommits.F1 = qry_NewDailyWip.F1) 
   LEFT JOIN BOM_WC_TOOL_WAVE_ONLY ON qryThurCommits.F1 = BOM_WC_TOOL_WAVE_ONLY.PARENT) 
   LEFT JOIN QRY_UNIQUE_YIELD_WITH_REV ON qryThurCommits.F1 = QRY_UNIQUE_YIELD_WITH_REV.ITEM) 
   LEFT JOIN QRY_WIP_PREWAVE ON qryThurCommits.F1 = QRY_WIP_PREWAVE.ASSY_ID) 
   LEFT JOIN QRY_Gating_operation ON qryThurCommits.F1 = QRY_Gating_operation.F1 

WHERE ((qryThurCommits.F1) Like "*13*" Or
(qryThurCommits.F1) Like "*9*" Or 
(qryThurCommits.F1) Like "*199*" Or 
(qryThurCommits.F1) Like "*303*" Or 
(qryThurCommits.F1) Like "*79*" Or 
(qryThurCommits.F1) Like "*16*")

GROUP BY 
   qryThurCommits.F1, 
   qryThurCommits.THURSDAYCOMMIT, 
   BOM_WC_TOOL_WAVE_ONLY.[WCT_SEC/BRD], 
   IIf(IsNull([waveprocess]),'NEEDS REVIEW',[waveprocess]), 
   IIf(IsNull([maxofQuantity]),0,([maxofQUANTITY])*3600), 
   IIf(IsNull([YIELD]),85,[YIELD]), BOM_WC_TOOL_WAVE_ONLY.Letter, 
   IIf(IsNull([CountOfSFCNUMBER]),0,[COUNTOFSFCNUMBER])

HAVING Count([SFCNUMBER]) < [ThursdayCommit]

ORDER BY 
Abs((Count([SFCNUMBER])-[ThursDAYCommit])*((100-IIf(IsNull([YIELD]),85,[YIELD]))/100)+1*IIf(IsNull([maxofQuantity]),0,([maxofQUANTITY])*3600)) DESC

So I made the query a little more readable and restructured a little bit.

Is qryThurCommits.F1 a string field or a number field?

You do realize that the like clause for the 199 is redundant since the like *9* will also return the 199?

The LIKE function works like this (usually used on strings).

LIKE "AND*" - returns anything that STARTS with "AND"
ANDrews
ANDromeda

LIKE "*AND" - returns anything that ENDS with "AND"
sAND

LIKE "*AND*" - returns anything that CONTAINS "AND" any where in the field

rANDom


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[jadams0173]

THANKS for the responses. The F1.Thursday is text. I'm still attempting to get this to work....

 

[lespaul]

So your where clause in the CODE will need to be:

WHERE ((qryThurCommits.F1) Like ""*13*"" Or
(qryThurCommits.F1) Like ""*9*"" Or
(qryThurCommits.F1) Like ""*199*"" Or
(qryThurCommits.F1) Like ""*303*"" Or
(qryThurCommits.F1) Like ""*79*"" Or
(qryThurCommits.F1) Like ""*16*"")

It looks like you are trying to find numbers within a string, if so, you are probably better off using the MID function (if the numbers are in a fixed location or an easily identifiable location). What is the format of the text in F1?

Leslie

 

[jebry]

Hi!

You can also use:

InStr(qryThurCommits.F1, "13") <> 0 Or
etc.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst