Type mismatch

[na9999 via AccessMonster.com]

Hi
Sorry if this is obvious but have now spent hours playing around with no joy.
Keep getting a type mismatch error with this code. I have DAO as a reference,
but not ADO. I have tried setting both as reference s and then just using ADO
but then the Dim dbs As Database and Dim qdf As QueryDef don't work.

Any help would be great!

Thanks


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

 

[Jeff Boyce]

Already responded to in another newsgroup. It's rarely necessary to post to
more than one group.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


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