Please help me with SQL...

[Ethereal]

Private Sub setSQL()

sqlSampleTypeWithoutCustomerName = "SELECT tSample.SamTypeID, tSampleTypeList.TypeDesc, tSample.SamID FROM tSample " & _
"INNER JOIN tSampleTypeList ON tSample.SamTypeID= tSampleTypeList.SamTypeID GROUP BY " & _
"tSample.SamTypeID, tSampleTypeList.TypeDesc,tSample.SamID, tSample.SamThick," & _
"tSample.SamLami, tSample.SamTemp, tSample.SamHS,tSample.SamPrint HAVING " & _
"(((tSample.SamID)Like IIf(IsNull([Forms]![fSampleSearch]![cboSampleNumber])," * "," & _
"[Forms]![fSampleSearch]![cboSampleNumber]) And (tSample.SamID)Like IIf(IsNull([Forms]!" & _
"[fSampleSearch]![cboCustomerName])," * ",[Forms]![fSampleSearch]![cboCustomerName]))" & _
"AND((tSample.SamThick) Like IIf(IsNull([Forms]![fSampleSearch]![cboThickness])," * "," & _
"[Forms]![fSampleSearch]![cboThickness]))AND ((tSample.SamLami) Like IIf(IsNull" & _
"([Forms]![fSampleSearch]![chkLami])," * ",[Forms]![fSampleSearch]![chkLami])) " & _
"AND((tSample.SamTemp) Like IIf(IsNull([Forms]![fSampleSearch]![chkTemp])," * "," & _
"[Forms]![fSampleSearch]![chkTemp])) AND((tSample.SamHS) Like IIf(IsNull([Forms]!" & _
"[fSampleSearch]![chkHeat])," * ",[Forms]![fSampleSearch]![chkHeat])) AND((tSample." & _
"SamPrint) Like IIf(IsNull([Forms]![fSampleSearch]![chkPrint])," * ",[Forms]!" & _
"[fSampleSearch]![chkPrint])))ORDER BY tSample.SamTypeID;"

sqlThicknessWithoutCustomerName = "SELECT tSample.SamThick FROM tSample GROUP BY tSample.SamThick, tSample.SamID," & _
"tSample.SamTypeID, tSample.SamLami, tSample.SamTemp, tSample.SamHS, tSample.SamPrint" & _
"HAVING (((tSample.SamID) Like IIf(IsNull([Forms]![fSampleSearch]![cboSampleNumber])," & _
"" * ",[Forms]![fSampleSearch]![cboSampleNumber]) And (tSample.SamID) Like IIf(IsNull" & _
"([Forms]![fSampleSearch]![cboCustomerName])," * ",[Forms]![fSampleSearch]!" & _
"[cboCustomerName])) AND ((tSample.SamTypeID) Like IIf(IsNull([Forms]![fSampleSearch]!" & _
"[cboSampleType])," * ",[Forms]![fSampleSearch]![cboSampleType])) AND ((tSample.SamLami)!" & _
"Like IIf(IsNull([Forms]![fSampleSearch]![chkLami])," * ",[Forms]![fSampleSearch]!" & _
"[chkLami])) AND ((tSample.SamTemp) Like IIf(IsNull([Forms]![fSampleSearch]![chkTemp])" & _
"," * ",[Forms]![fSampleSearch]![chkTemp])) AND ((tSample.SamHS) Like IIf(IsNull([Forms]" & _
"![fSampleSearch]![chkHeat])," * ",[Forms]![fSampleSearch]![chkHeat])) AND " & _
"((tSample.SamPrint) Like IIf(IsNull([Forms]![fSampleSearch]![chkPrint])," * ",[Forms]!" & _
"[fSampleSearch]![chkPrint]))) ORDER BY tSample.SamThick DESC;"

sqlSampleTypeWithCustomerName = "SELECT tSample.SamTypeID, tSampleTypeList.TypeDesc FROM tSampleCustomer INNER JOIN" & _
"(tSample INNER JOIN tSampleTypeList ON tSample.SamTypeID = tSampleTypeList.SamTypeID) " & _
"ON tSampleCustomer.SamID = tSample.SamID GROUP BY tSample.SamTypeID, tSampleTypeList." & _
"TypeDesc, tSampleCustomer.CustName, tSample.SamThick, tSample.SamLami, tSample.SamTemp, " & _
"tSample.SamHS, tSample.SamPrint HAVING (((tSampleCustomer.CustName) Like IIf(IsNull([Forms]" & _
"![fSampleSearch]![cboCustomerName])," * ",[Forms]![fSampleSearch]![cboCustomerName])) AND" & _
"((tSample.SamThick) Like IIf(IsNull([Forms]![fSampleSearch]![cboThickness])," * ",[Forms]" & _
"![fSampleSearch]![cboThickness])) AND ((tSample.SamLami) Like IIf(IsNull([Forms]!" & _
"[fSampleSearch]![chkLami])," * ",[Forms]![fSampleSearch]![chkLami])) AND ((tSample.SamTemp)" & _
"Like IIf(IsNull([Forms]![fSampleSearch]![chkTemp])," * ",[Forms]![fSampleSearch]![chkTemp]))" & _
"AND ((tSample.SamHS) Like IIf(IsNull([Forms]![fSampleSearch]![chkHeat])," * ",[Forms]!" & _
"[fSampleSearch]![chkHeat])) AND ((tSample.SamPrint) Like IIf(IsNull([Forms]![fSampleSearch]" & _
"![chkPrint])," * ",[Forms]![fSampleSearch]![chkPrint]))) ORDER BY tSample.SamTypeID;"

sqlThicknessWithCustomerName = "SELECT tSample.SamThick FROM tSampleCustomer INNER JOIN tSample ON " & _
"tSampleCustomer.SamID = tSample.SamID GROUP BY tSample.SamThick, tSample.SamTypeID, " & _
"tSample.SamLami, tSample.SamTemp, tSample.SamHS, tSample.SamPrint, tSampleCustomer." & _
"CustName HAVING (((tSample.SamTypeID) Like IIf(IsNull([Forms]![fSampleSearch]!" & _
"[cboSampleType])," * ",[Forms]![fSampleSearch]![cboSampleType])) AND ((tSample.SamLami)" & _
"Like IIf(IsNull([Forms]![fSampleSearch]![chkLami])," * ",[Forms]![fSampleSearch]!" & _
"[chkLami])) AND ((tSample.SamTemp) Like IIf(IsNull([Forms]![fSampleSearch]![chkTemp])," & _
"" * ",[Forms]![fSampleSearch]![chkTemp])) AND ((tSample.SamHS) Like IIf(IsNull([Forms]!" & _
"[fSampleSearch]![chkHeat])," * ",[Forms]![fSampleSearch]![chkHeat])) AND " & _
"((tSample.SamPrint) Like IIf(IsNull([Forms]![fSampleSearch]![chkPrint])," * ",[Forms]!" & _
"[fSampleSearch]![chkPrint])) AND ((tSampleCustomer.CustName) Like IIf(IsNull([Forms]!" & _
"[fSampleSearch]![cboCustomerName])," * ",[Forms]![fSampleSearch]![cboCustomerName]))) " & _
"ORDER BY tSample.SamThick DESC;"
If IsNull(cboCustomerName) Then
cboSampleType.RowSource = sqlSampleTypeWithoutCustomerName
cboThickness.RowSource = sqlThicknessWithoutCustomerName
Else
cboSampleType.RowSource = sqlSampleTypeWithCustomerName
cboThickness.RowSource = sqlThicknessWithCustomerName
End If

End Sub

-----------------------------------------------------------------

1) HOW do you guys get it into a different box when u place code ?
2) Why do I get "Run time error '13' Type Mismatch when I run the code...

 

[Kodo]

I have to ask.. why are you not putting these into queries??

 

[Ethereal]

the thought never occured to me....

 

[Ethereal]

This is what i have now that i moved them into query's, but i'm thinking this isn't how you set them to the rowsources....

Private Sub setSQL()

If IsNull(cboCustomerName) Then
cboSampleType.RowSource = qSampleSearchTypeWithoutCust
cboThickness.RowSource = qSampleSearchThickWithoutCust
Else
cboSampleType.RowSource = qSampleSearchTypeWithCust
cboThickness.RowSource = qSampleSearchThickWithCust
End If

End Sub