Type Mismatch running query

[Mark A. Sam]

I constructed an SQL Statement in a string variable, strSQL. When I run the
query I get a type mismatch error, however if I place the value of strSQL in
the QBE Grid, it contructs a valid query which runs fine.

Here is the value of strSQL:

SELECT [Order Entry ST Products].detProdCode, Sum([Order Entry ST
Tasks].prodTaskTime) AS SumTaskTime INTO
EmpTasksSpecialProjectSumTaskTimeTemp FROM (Customers INNER JOIN ([Order
Entry Header] INNER JOIN [Order Entry ST Products] ON [Order Entry
Header].ordID = [Order Entry ST Products].ordID) ON Customers.Custid =
[Order Entry Header].ordCustID) INNER JOIN [Order Entry ST Tasks] ON [Order
Entry ST Products].ordDetID = [Order Entry ST Tasks].ordDetID GROUP BY
[Order Entry ST Products].detProdCode HAVING ([Order Entry ST
Products].detProdCode)='PD';

I contructed strSQL from a query statement which runs fine in my module
using docmd.runsql.

I need to get this worked out as I have a complicated where clause to add
and little time to get this project done.

Thanks for any help.

God Bless,

Mark A. Sam

 

[Mark A. Sam]

The error (err) number is 13.

 

[John Vinson]

I constructed an SQL Statement in a string variable, strSQL. When I run the
query I get a type mismatch error, however if I place the value of strSQL in
the QBE Grid, it contructs a valid query which runs fine.

Here is the value of strSQL:

SELECT [Order Entry ST Products].detProdCode, Sum([Order Entry ST
Tasks].prodTaskTime) AS SumTaskTime INTO
EmpTasksSpecialProjectSumTaskTimeTemp FROM (Customers INNER JOIN ([Order
Entry Header] INNER JOIN [Order Entry ST Products] ON [Order Entry
Header].ordID = [Order Entry ST Products].ordID) ON Customers.Custid =
[Order Entry Header].ordCustID) INNER JOIN [Order Entry ST Tasks] ON [Order
Entry ST Products].ordDetID = [Order Entry ST Tasks].ordDetID GROUP BY
[Order Entry ST Products].detProdCode HAVING ([Order Entry ST
Products].detProdCode)='PD';

I contructed strSQL from a query statement which runs fine in my module
using docmd.runsql.

Please post the context in the VBA. I'm guessing that you're getting a
type mismatch in one of your VBA statements rather than in the query
itself. Another possibility is that detProdCode is a Lookup field so
that it actually contains a number rather than the text string PD - is
that at all possible?

In any case, the criterion on detProdCode should be in the WHERE
clause rather than the HAVING clause, in order to filter the records
before doing the sum rather than summing them and then throwing away
most of the results!

John W. Vinson[MVP]

 

[Mark A. Sam]

John, Thanks for replying.

I don't know what you mean by posting the context. Do you want to code
which contruct the variable, strSQL?

Here it is:

strSQL = "SELECT [Order Entry ST Products].detProdCode, Sum([Order Entry ST
Tasks].prodTaskTime) AS SumTaskTime INTO
EmpTasksSpecialProjectSumTaskTimeTemp "

strSQL = strSQL & "FROM (Customers INNER JOIN ([Order Entry Header] INNER
JOIN [Order Entry ST Products] ON [Order Entry Header].ordID = [Order Entry
ST Products].ordID) ON Customers.Custid = [Order Entry Header].ordCustID)
INNER JOIN [Order Entry ST Tasks] ON [Order Entry ST Products].ordDetID =
[Order Entry ST Tasks].ordDetID "

strSQL = strSQL & "GROUP BY [Order Entry ST Products].detProdCode "
strSQL = strSQL & "HAVING ([Order Entry ST Products].detProdCode)='" &
setProductFilter() & "';"

detProdCode should be the value 'pd' as it is.
detProduct code is in the having clause becuase it needs to show up in the
query results. This creasting a table which is used in another query with
tables linked by detProdCode.

This is puzzeling becuse the query runs fine when I paste it into the QBE
grid.

Thanks again.

 

[Douglas J. Steele]

Error type 13 is type mismatch. Assuming you're trying to assign the results
of the query to a recordset, are you using DAO or ADO, and have you declared
the recordset appropriately?

 

[John Vinson]

John, Thanks for replying.

I don't know what you mean by posting the context. Do you want to code
which contruct the variable, strSQL?

Here it is:

That looks OK on a quick view. At what point does the error message
arise? What line of code is highlighted? How are you executing the
query?

John W. Vinson[MVP]

 

[Rob Oldfield]

....he also means the context in terms of how you're trying to run the query.
db.execute or something else? Post the entire sub/function.

 

[Mark A. Sam]

Hello Doug,

I'm not using DAO to assign the results to a recordset. This is a maketable
query and should create a new table when it is run.

If I paste the reulsts of strSQL into the QBE Grid it works fine, but when I
use the runsql method,
DoCmd.RunCommand strSQL
I get the mismatch error error:

If I use the the execute method:
CurrentDb.Execute strSQL

I get a message box which says,

Error 3010: Table 'EmpTasksSpecialProjectSumTaskTimeTemp' already exists'

The only option I get is OK, and it doesn't replace the table.


Mark

Error type 13 is type mismatch. Assuming you're trying to assign the results
of the query to a recordset, are you using DAO or ADO, and have you declared
the recordset appropriately?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I constructed an SQL Statement in a string variable, strSQL. When I run
the
query I get a type mismatch error, however if I place the value of strSQL
in
the QBE Grid, it contructs a valid query which runs fine.

Here is the value of strSQL:

SELECT [Order Entry ST Products].detProdCode, Sum([Order Entry ST
Tasks].prodTaskTime) AS SumTaskTime INTO
EmpTasksSpecialProjectSumTaskTimeTemp FROM (Customers INNER JOIN ([Order
Entry Header] INNER JOIN [Order Entry ST Products] ON [Order Entry
Header].ordID = [Order Entry ST Products].ordID) ON Customers.Custid =
[Order Entry Header].ordCustID) INNER JOIN [Order Entry ST Tasks] ON
[Order
Entry ST Products].ordDetID = [Order Entry ST Tasks].ordDetID GROUP BY
[Order Entry ST Products].detProdCode HAVING ([Order Entry ST
Products].detProdCode)='PD';

I contructed strSQL from a query statement which runs fine in my module
using docmd.runsql.

I need to get this worked out as I have a complicated where clause to add
and little time to get this project done.

Thanks for any help.

God Bless,

Mark A. Sam

 

[Mark A. Sam]

John,
The error comes when the query is executed.

DoCmd.RunCommand strSQL

Mark

 

[Van T. Dinh]

Make sure you _delete_ the old Table before running the SELECT ... INTO ...
SQL.

 

[Mark A. Sam]

Thanks,

I tried that and it worked, using the Currentdb.Execute method, but it is
still a concern to me that it bombs when I use the DoCmd.RunSql method. I
think the Execute method is suppressing the problem and may not be
dependable.

God Bless,

Mark

 

[Mark A. Sam]

Its still in progress, but this is what I have.....

Private Sub Report_Click()
On Error GoTo error_Section

'Set Public Variable excluding pubdatefrom and pubDateTo which will be set
when needed
pubDateFrom = [DateFrom]
pubDateTo = [DateTo]
pubLocationFilter = [Location Filter]
pubProductFilter = [Product Filter]
pubEmployeeFilter = [Employee Filter]
pubTaskFilter = [Task Filter]
pubExclude0Cost = [Exclude0Cost]
pubExclude0SP = [Exclude0SP]

Dim strSQL As Variant
Dim strWhere As String


'Set table row for Sales Range 1 Put in loop to cover all ranges
pubSPFrom = [SPfrom1]
pubSPTo = [SPTo1]

'create table [EmpTasksSpecialProjectSumTaskTimeTemp]
'**DoCmd.SetWarnings False

strSQL = "SELECT [Order Entry ST Products].detProdCode, Sum([Order Entry ST
Tasks].prodTaskTime) AS SumTaskTime INTO
EmpTasksSpecialProjectSumTaskTimeTemp "
strSQL = strSQL & "FROM (Customers INNER JOIN ([Order Entry Header] INNER
JOIN [Order Entry ST Products] ON [Order Entry Header].ordID = [Order Entry
ST Products].ordID) ON Customers.Custid = [Order Entry Header].ordCustID)
INNER JOIN [Order Entry ST Tasks] ON [Order Entry ST Products].ordDetID =
[Order Entry ST Tasks].ordDetID "

'construct Where clause
'Note Product is Handled in the Having Clause. This is becuase the
[detProdCode] field is visible
strWhere = "Where [Order Entry ST Products].detShipDate Between #" &
setDateFrom() & "# And #" & setDateTo() & "# "
strWhere = strWhere & "AND [Order Entry ST Tasks].prodTaskTime> 0 "
If setLocationFilter <> "All" Then
strWhere = strWhere & "AND [Order Entry ST Products].DetProdLocation= '" &
setLocationFilter() & "' "
End If
If setTaskFilter <> "All" Then
strWhere = strWhere & "AND [Order Entry ST Tasks].prodTask= '" &
setTaskFilter() & "' "
End If
If setEmployeeFilter() <> 0 Then
strWhere = strWhere & "AND [Order Entry ST Tasks].prodTaskEmpNo= " &
setEmployeeFilter() & " "
End If


'strSQL = strSQL & "GROUP BY [Order Entry ST Products].detProdCode "

If setProductFilter <> "All" Then
strSQL = strSQL & "HAVING ([Order Entry ST Products].detProdCode)='" &
setProductFilter() & "';"
End If

Debug.Print strWhere
Debug.Print vbNewLine
Debug.Print strSQL

'DoCmd.RunCommand strSQL
CurrentDb.Execute strSQL


'DoCmd.SetWarnings True

exit_Section:
'DoCmd.SetWarnings True
Exit Sub

error_Section:
DoCmd.SetWarnings True
If Err = 2501 Then
Resume Next
Else
MsgBox "Error " & Err & ": " & Err.Description
Resume Next
End If

End Sub

 

[Van T. Dinh]

I always use the dbFailOnError option with Execute and never had any
inconsistent result.

Check Access VB Help on the Execute Method.

 

[John Vinson]

Thanks,

I tried that and it worked, using the Currentdb.Execute method, but it is
still a concern to me that it bombs when I use the DoCmd.RunSql method. I
think the Execute method is suppressing the problem and may not be
dependable.

On the contrary!

The Execute method - with the optional FailOnError property set in the
query definition, or explicitly included in the method call - is MORE
reliable than RunSQL. RunSQL does not trap query errors; Execute does.


John W. Vinson[MVP]