Type mismatch

  • Thread starter na9999 via AccessMonster.com
  • Start date
N

na9999 via AccessMonster.com

Hi
Keep getting a type mismatch error with this code. I have DAO as a reference,
but not ADO.

Private Sub cmdCreateQuery_Click()
On Error GoTo Err_cmdCreateQuery_Click

Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim frmname As String
Dim dbs As DAO.Database


Set dbs = CurrentDb

strSQL = "SELECT Main.Absolut_Referece, Main.Title, "
strSQL = strSQL & "Main.Standard_Type, Main.Number, Main.Part, "
strSQL = strSQL & "Main.Subsection, Main.Year"
strSQL = strSQL & "Main.Modules"


Select Case Forms![practice]![findtype]
Case 1
strSQL = strSQL & " WHERE ((Main.Standard_Type)=[findtype])"

Case Else
strSQL = strSQL & " WHERE ((Main.Standard_Type)=" * ")"
End Select


strSQL = strSQL & " AND "

Select Case Forms![practice]![findkeyword]
Case 1
strSQL = strSQL & " WHERE ((Main.Title) LIKE " * " [findkeyword]" * ")
"

Case Else
strSQL = strSQL & " WHERE ((Main.Title)=" * ")"
End Select

strSQL = strSQL & " AND "

Select Case Forms![practice]![findnumber]
Case 1
strSQL = strSQL & " WHERE ((Main.Number)=[findnumber])"

Case Else
strSQL = strSQL & " WHERE ((Main.Number)=" * ")"
End Select

strSQL = strSQL & " AND "

Select Case Forms![practice]![findmodule]
Case 1
strSQL = strSQL & " WHERE ((Main.Modules) LIKE " * " [findmodule]" *
")"

Case Else
strSQL = strSQL & " WHERE ((Main.Modules)=" * ")"
End Select



MsgBox strSQL


dbs.QueryDefs.Delete "query"
Set qdf = dbs.CreateQueryDef("query", strSQL)

DoCmd.OpenQuery "query", acNormal, acEdit






Exit_cmdCreateQuery_Click:
Exit Sub

Err_cmdCreateQuery_Click:

If Err.Number = 3265 Then
Resume Next
Else
' MsgBox Err.Number
MsgBox Err.Description
Resume Exit_cmdCreateQuery_Click

End If

End Sub

Thanks
 
G

Graham Mandeno

Hi na9999

The problem is that you are not correctly constructing the strings that
contain * as a wildcard.

For example:
strSQL = strSQL & " WHERE ((Main.Title) LIKE " * " [findkeyword]" * ")"

Look at the part after the &:
" WHERE ((Main.Title) LIKE " * " [findkeyword]" * ")"

This means:
" WHERE ((Main.Title) LIKE "
multiplied by
" [findkeyword]"
multiplied by
")"

Since these three elements are non-numeric text, it is impossible to convert
then to numbers which can be multiplied - hence the type mismatch.

Also, you are adding the word WHERE for each condition:
WHERE condition1 AND WHERE condition2 AND WHERE ...

You should use the WHERE keyword only once:
WHERE condition1 AND condition2 AND ...

Finally, you seem to be missing a comma between Main.Year and Main.Modules.

I rather think that what you want is:

strSQL = "SELECT Main.Absolut_Referece, Main.Title, " _
& "Main.Standard_Type, Main.Number, Main.Part, " _
& "Main.Subsection, Main.Year, Main.Modules"

If Not IsNull([findtype]) Then
strWhere = "(Main.Standard_Type='" & [findtype] & "') AND "
End If

If Not IsNull([findkeyword]) Then
strWhere = strWhere & "(Main.Title='*" & [findkeyword] & "*') AND "
End If

If Not IsNull([findnumber]) Then
strWhere = "(Main.Number=" & [findnumber] & ") AND "
End If

If Not IsNull([findmodule]) Then
strWhere = strWhere & "(Main.Modules='*" & [findmodule] & "*') AND "
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere)-5) ' remove last " AND "
strSQL = strSQL & " WHERE " & strWhere
End If

This is assuming that Main.Number is a numeric field (doesn't require
quotes) and the others are text.

Pay special attention to the quotes - some are single; some are double; they
are all important!
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand

na9999 via AccessMonster.com said:
Hi
Keep getting a type mismatch error with this code. I have DAO as a
reference,
but not ADO.

Private Sub cmdCreateQuery_Click()
On Error GoTo Err_cmdCreateQuery_Click

Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim frmname As String
Dim dbs As DAO.Database


Set dbs = CurrentDb

strSQL = "SELECT Main.Absolut_Referece, Main.Title, "
strSQL = strSQL & "Main.Standard_Type, Main.Number, Main.Part, "
strSQL = strSQL & "Main.Subsection, Main.Year"
strSQL = strSQL & "Main.Modules"


Select Case Forms![practice]![findtype]
Case 1
strSQL = strSQL & " WHERE ((Main.Standard_Type)=[findtype])"

Case Else
strSQL = strSQL & " WHERE ((Main.Standard_Type)=" * ")"
End Select


strSQL = strSQL & " AND "

Select Case Forms![practice]![findkeyword]
Case 1
strSQL = strSQL & " WHERE ((Main.Title) LIKE " * " [findkeyword]" *
")
"

Case Else
strSQL = strSQL & " WHERE ((Main.Title)=" * ")"
End Select

strSQL = strSQL & " AND "

Select Case Forms![practice]![findnumber]
Case 1
strSQL = strSQL & " WHERE ((Main.Number)=[findnumber])"

Case Else
strSQL = strSQL & " WHERE ((Main.Number)=" * ")"
End Select

strSQL = strSQL & " AND "

Select Case Forms![practice]![findmodule]
Case 1
strSQL = strSQL & " WHERE ((Main.Modules) LIKE " * " [findmodule]"
*
")"

Case Else
strSQL = strSQL & " WHERE ((Main.Modules)=" * ")"
End Select



MsgBox strSQL


dbs.QueryDefs.Delete "query"
Set qdf = dbs.CreateQueryDef("query", strSQL)

DoCmd.OpenQuery "query", acNormal, acEdit






Exit_cmdCreateQuery_Click:
Exit Sub

Err_cmdCreateQuery_Click:

If Err.Number = 3265 Then
Resume Next
Else
' MsgBox Err.Number
MsgBox Err.Description
Resume Exit_cmdCreateQuery_Click

End If

End Sub

Thanks
Click to expand...
 
G

Graham Mandeno

Hey Skippy! Have you been playing with Jeff's clock again?? <vbg>

Jeff Boyce said:
That's a lot of code.

Where in the code does the type mismatch error occur?

Type mismatch usually means you are giving a value that is different than
what you told Access the field holds ... e.g., presenting a "text" type
value when you told Access the field holds "numeric".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


na9999 via AccessMonster.com said:
Hi
Keep getting a type mismatch error with this code. I have DAO as a reference,
but not ADO.

Private Sub cmdCreateQuery_Click()
On Error GoTo Err_cmdCreateQuery_Click

Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim frmname As String
Dim dbs As DAO.Database


Set dbs = CurrentDb

strSQL = "SELECT Main.Absolut_Referece, Main.Title, "
strSQL = strSQL & "Main.Standard_Type, Main.Number, Main.Part, "
strSQL = strSQL & "Main.Subsection, Main.Year"
strSQL = strSQL & "Main.Modules"


Select Case Forms![practice]![findtype]
Case 1
strSQL = strSQL & " WHERE ((Main.Standard_Type)=[findtype])"

Case Else
strSQL = strSQL & " WHERE ((Main.Standard_Type)=" * ")"
End Select


strSQL = strSQL & " AND "

Select Case Forms![practice]![findkeyword]
Case 1
strSQL = strSQL & " WHERE ((Main.Title) LIKE " * " [findkeyword]" * ")
"

Case Else
strSQL = strSQL & " WHERE ((Main.Title)=" * ")"
End Select

strSQL = strSQL & " AND "

Select Case Forms![practice]![findnumber]
Case 1
strSQL = strSQL & " WHERE ((Main.Number)=[findnumber])"

Case Else
strSQL = strSQL & " WHERE ((Main.Number)=" * ")"
End Select

strSQL = strSQL & " AND "

Select Case Forms![practice]![findmodule]
Case 1
strSQL = strSQL & " WHERE ((Main.Modules) LIKE " * "
[findmodule]" *
")"

Case Else
strSQL = strSQL & " WHERE ((Main.Modules)=" * ")"
End Select



MsgBox strSQL


dbs.QueryDefs.Delete "query"
Set qdf = dbs.CreateQueryDef("query", strSQL)

DoCmd.OpenQuery "query", acNormal, acEdit






Exit_cmdCreateQuery_Click:
Exit Sub

Err_cmdCreateQuery_Click:

If Err.Number = 3265 Then
Resume Next
Else
' MsgBox Err.Number
MsgBox Err.Description
Resume Exit_cmdCreateQuery_Click

End If

End Sub

Thanks
Click to expand...
Click to expand...
 
J

Jeff Boyce

That's a lot of code.

Where in the code does the type mismatch error occur?

Type mismatch usually means you are giving a value that is different than
what you told Access the field holds ... e.g., presenting a "text" type
value when you told Access the field holds "numeric".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
N

na9999 via AccessMonster.com

Hi Graham

Thanks that seems to have just about cleared things up. Much appreciated.

Just also wondering now I'm getting a syntax error (missing operator). This
(I'm assuming) afraid this is the first bit of VB I've done in years, is that
the SQL expression is wrong.

The expression comes out as SELECT Main........ WHERE (Main.xxxxx ='xxxx')

Should the brackets be there? If if not how would you remove them?

Thanks

Graham said:
Hi na9999

The problem is that you are not correctly constructing the strings that
contain * as a wildcard.

For example:
strSQL = strSQL & " WHERE ((Main.Title) LIKE " * " [findkeyword]" * ")"

Look at the part after the &:
" WHERE ((Main.Title) LIKE " * " [findkeyword]" * ")"

This means:
" WHERE ((Main.Title) LIKE "
multiplied by
" [findkeyword]"
multiplied by
")"

Since these three elements are non-numeric text, it is impossible to convert
then to numbers which can be multiplied - hence the type mismatch.

Also, you are adding the word WHERE for each condition:
WHERE condition1 AND WHERE condition2 AND WHERE ...

You should use the WHERE keyword only once:
WHERE condition1 AND condition2 AND ...

Finally, you seem to be missing a comma between Main.Year and Main.Modules.

I rather think that what you want is:

strSQL = "SELECT Main.Absolut_Referece, Main.Title, " _
& "Main.Standard_Type, Main.Number, Main.Part, " _
& "Main.Subsection, Main.Year, Main.Modules"

If Not IsNull([findtype]) Then
strWhere = "(Main.Standard_Type='" & [findtype] & "') AND "
End If

If Not IsNull([findkeyword]) Then
strWhere = strWhere & "(Main.Title='*" & [findkeyword] & "*') AND "
End If

If Not IsNull([findnumber]) Then
strWhere = "(Main.Number=" & [findnumber] & ") AND "
End If

If Not IsNull([findmodule]) Then
strWhere = strWhere & "(Main.Modules='*" & [findmodule] & "*') AND "
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere)-5) ' remove last " AND "
strSQL = strSQL & " WHERE " & strWhere
End If

This is assuming that Main.Number is a numeric field (doesn't require
quotes) and the others are text.

Pay special attention to the quotes - some are single; some are double; they
are all important!
Hi
Keep getting a type mismatch error with this code. I have DAO as a
Click to expand...
[quoted text clipped - 82 lines]
Thanks
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi na

You don't mention where the error occurs. If it is when you try to open the
query, then yes, the error is likely to be in the SQL. If it is anywhere
else then, no, the error is not in the SQL but in your VBA code.

When the error occurs, your code should stop and highlight a certain line as
it displays the error message. Which line is this?

Still, there is no doubt that your SQL is wrong. I just noticed for example
that you don't have any FROM clause!

I should say that what you are doing is not usual practice. Normally one
would create a data form based on your query WITHOUT the where clause:

SELECT Main.Absolute_Reference, Main.Title,
Main.Standard_Type, Main.Number, Main.Part,
Main.Subsection, Main.Year, Main.Modules
FROM Main ORDER BY Main.Title;

Then your filtering form would construct the WHERE clause and use it to open
the data form showing only the required records, like this:

If Not IsNull([findtype]) Then
strWhere = "(Main.Standard_Type='" & [findtype] & "') AND "
End If

If Not IsNull([findkeyword]) Then
strWhere = strWhere & "(Main.Title like '*" & [findkeyword] & "*') AND "
End If

If Not IsNull([findnumber]) Then
strWhere = "(Main.Number=" & [findnumber] & ") AND "
End If

If Not IsNull([findmodule]) Then
strWhere = strWhere & "(Main.Modules like '*" & [findmodule] & "*') AND
"
End If

If Len(strWhere) > 0 Then
' remove the last " AND "
strWhere = Left(strWhere, Len(strWhere)-5)
End If

DoCmd.OpenForm "MyDataForm", WhereCondition:=strWhere
--
Good Luck :)

Graham Mandeno [Access MVP]
Auckland, New Zealand



na9999 via AccessMonster.com said:
Hi Graham

Thanks that seems to have just about cleared things up. Much appreciated.

Just also wondering now I'm getting a syntax error (missing operator).
This
(I'm assuming) afraid this is the first bit of VB I've done in years, is
that
the SQL expression is wrong.

The expression comes out as SELECT Main........ WHERE (Main.xxxxx ='xxxx')

Should the brackets be there? If if not how would you remove them?

Thanks

Graham said:
Hi na9999

The problem is that you are not correctly constructing the strings that
contain * as a wildcard.

For example:
strSQL = strSQL & " WHERE ((Main.Title) LIKE " * " [findkeyword]" *
")"

Look at the part after the &:
" WHERE ((Main.Title) LIKE " * " [findkeyword]" * ")"

This means:
" WHERE ((Main.Title) LIKE "
multiplied by
" [findkeyword]"
multiplied by
")"

Since these three elements are non-numeric text, it is impossible to
convert
then to numbers which can be multiplied - hence the type mismatch.

Also, you are adding the word WHERE for each condition:
WHERE condition1 AND WHERE condition2 AND WHERE ...

You should use the WHERE keyword only once:
WHERE condition1 AND condition2 AND ...

Finally, you seem to be missing a comma between Main.Year and
Main.Modules.

I rather think that what you want is:

strSQL = "SELECT Main.Absolut_Referece, Main.Title, " _
& "Main.Standard_Type, Main.Number, Main.Part, " _
& "Main.Subsection, Main.Year, Main.Modules"

If Not IsNull([findtype]) Then
strWhere = "(Main.Standard_Type='" & [findtype] & "') AND "
End If

If Not IsNull([findkeyword]) Then
strWhere = strWhere & "(Main.Title='*" & [findkeyword] & "*') AND "
End If

If Not IsNull([findnumber]) Then
strWhere = "(Main.Number=" & [findnumber] & ") AND "
End If

If Not IsNull([findmodule]) Then
strWhere = strWhere & "(Main.Modules='*" & [findmodule] & "*') AND "
End If

If Len(strWhere) > 0 Then
strWhere = Left(strWhere, Len(strWhere)-5) ' remove last " AND "
strSQL = strSQL & " WHERE " & strWhere
End If

This is assuming that Main.Number is a numeric field (doesn't require
quotes) and the others are text.

Pay special attention to the quotes - some are single; some are double;
they
are all important!
Hi
Keep getting a type mismatch error with this code. I have DAO as a
Click to expand...
[quoted text clipped - 82 lines]
Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Nah! Actually up and going...

Jeff

Graham Mandeno said:
Hey Skippy! Have you been playing with Jeff's clock again?? <vbg>

Jeff Boyce said:
That's a lot of code.

Where in the code does the type mismatch error occur?

Type mismatch usually means you are giving a value that is different than
what you told Access the field holds ... e.g., presenting a "text" type
value when you told Access the field holds "numeric".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


na9999 via AccessMonster.com said:
Hi
Keep getting a type mismatch error with this code. I have DAO as a reference,
but not ADO.

Private Sub cmdCreateQuery_Click()
On Error GoTo Err_cmdCreateQuery_Click

Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim frmname As String
Dim dbs As DAO.Database


Set dbs = CurrentDb

strSQL = "SELECT Main.Absolut_Referece, Main.Title, "
strSQL = strSQL & "Main.Standard_Type, Main.Number, Main.Part, "
strSQL = strSQL & "Main.Subsection, Main.Year"
strSQL = strSQL & "Main.Modules"


Select Case Forms![practice]![findtype]
Case 1
strSQL = strSQL & " WHERE ((Main.Standard_Type)=[findtype])"

Case Else
strSQL = strSQL & " WHERE ((Main.Standard_Type)=" * ")"
End Select


strSQL = strSQL & " AND "

Select Case Forms![practice]![findkeyword]
Case 1
strSQL = strSQL & " WHERE ((Main.Title) LIKE " * "
Click to expand...
[findkeyword]"
* ")
"

Case Else
strSQL = strSQL & " WHERE ((Main.Title)=" * ")"
End Select

strSQL = strSQL & " AND "

Select Case Forms![practice]![findnumber]
Case 1
strSQL = strSQL & " WHERE ((Main.Number)=[findnumber])"

Case Else
strSQL = strSQL & " WHERE ((Main.Number)=" * ")"
End Select

strSQL = strSQL & " AND "

Select Case Forms![practice]![findmodule]
Case 1
strSQL = strSQL & " WHERE ((Main.Modules) LIKE " * "
[findmodule]" *
")"

Case Else
strSQL = strSQL & " WHERE ((Main.Modules)=" * ")"
End Select



MsgBox strSQL


dbs.QueryDefs.Delete "query"
Set qdf = dbs.CreateQueryDef("query", strSQL)

DoCmd.OpenQuery "query", acNormal, acEdit






Exit_cmdCreateQuery_Click:
Exit Sub

Err_cmdCreateQuery_Click:

If Err.Number = 3265 Then
Resume Next
Else
' MsgBox Err.Number
MsgBox Err.Description
Resume Exit_cmdCreateQuery_Click

End If

End Sub

Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Type mismatch 1
VBA works in orignal but fails in copy of database 1
Compile Error Help 1
NotInList help 3
Code will work on one table and form but not on another? 19
Syntax error (missing operator) 4
Multi-Select List Box 2
export vba not working 0

Top