Type Mismatch error in sql statement containing "Like"

[TD]

I created a query in Access 2000 that runs perfectly. I then copied the
"sql" version of the same query and set it equal the variable "sql" in the
code below. When I run the code below I get an "Type Mismatch" error. Can
someone explain how to fix this?

Thanks,
TD
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim db As Database
Dim rst As Recordset
Dim sql As String

[Forms]![frmVendorSetup]![Name].SetFocus

sql = "SELECT tblVendors.Name FROM tblVendors WHERE (((tblVendors.Name)
Like '" & [Forms]![frmVendorSetup]![Name].[Text] & " * '));"
Set db = OpenDatabase("adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

If rst.RecordCount > 0 Then
MsgBox "This vendor may already exist, check again!", vbOKOnly,
WAIT!: GoTo Exit_cmdSave_Click:
GoTo Exit_cmdSave_Click:
ElseIf rst.RecordCount = 0 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cboName.Requery
GoTo Exit_cmdSave_Click:
End If

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

[MacDermott]

I think you have an extra space between the " and the *.
"TD" <td***@mindspring.com> wrote in message
news:x0***************@fe07.lga...

I created a query in Access 2000 that runs perfectly. I then copied the
"sql" version of the same query and set it equal the variable "sql" in the
code below. When I run the code below I get an "Type Mismatch" error. Can someone explain how to fix this?

Thanks,
TD
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim db As Database
Dim rst As Recordset
Dim sql As String

[Forms]![frmVendorSetup]![Name].SetFocus

sql = "SELECT tblVendors.Name FROM tblVendors WHERE (((tblVendors.Name) Like '" & [Forms]![frmVendorSetup]![Name].[Text] & " * '));"
Set db = OpenDatabase("adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

If rst.RecordCount > 0 Then
MsgBox "This vendor may already exist, check again!", vbOKOnly,
WAIT!: GoTo Exit_cmdSave_Click:
GoTo Exit_cmdSave_Click:
ElseIf rst.RecordCount = 0 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cboName.Requery
GoTo Exit_cmdSave_Click:
End If

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

[TD]

I checked, no space between "*' , thanks for trying though.
"MacDermott" <ma********@nospam.com> wrote in message
news:24******************@newsread2.news.atl.earth link.net...

I think you have an extra space between the " and the *.
"TD" <td***@mindspring.com> wrote in message
news:x0***************@fe07.lga...

I created a query in Access 2000 that runs perfectly. I then copied the
"sql" version of the same query and set it equal the variable "sql" in the code below. When I run the code below I get an "Type Mismatch" error.

Can

someone explain how to fix this?

Thanks,
TD
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim db As Database
Dim rst As Recordset
Dim sql As String

[Forms]![frmVendorSetup]![Name].SetFocus

sql = "SELECT tblVendors.Name FROM tblVendors WHERE

(((tblVendors.Name)

Like '" & [Forms]![frmVendorSetup]![Name].[Text] & " * '));"
Set db = OpenDatabase("adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

If rst.RecordCount > 0 Then
MsgBox "This vendor may already exist, check again!", vbOKOnly,
WAIT!: GoTo Exit_cmdSave_Click:
GoTo Exit_cmdSave_Click:
ElseIf rst.RecordCount = 0 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cboName.Requery
GoTo Exit_cmdSave_Click:
End If

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

[TD]

OK, I gave up on the way I was doing it and came up with this, which works!

Save Button code-----------------------

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim prm As Parameter

[Forms]![frmVendorSetup]![txtName].SetFocus

Set db = OpenDatabase("adsi.mdb")
Set qdf = db.QueryDefs("qryVendors")
qdf.Parameters(0) = Eval("[Forms]![frmVendorSetup]![txtName].[Text]")
Set rst = qdf.OpenRecordset

If rst.RecordCount > 0 Then
MsgBox "This vendor may already exist, check again!", vbOKOnly,
WAIT!: GoTo Exit_cmdSave_Click:
GoTo Exit_cmdSave_Click:
ElseIf rst.RecordCount = 0 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Vendor saved", vbOKOnly, DONE
cboName.Requery
GoTo Exit_cmdSave_Click:
End If

Exit_cmdSave_Click:
Exit Sub
---------------------------------------

qryVendors SQL statement (all on one line of course)-----------------------

SELECT tblVendors.VendorName
FROM tblVendors
WHERE (((tblVendors.VendorName) Like [Forms]![frmVendorSetup]![Name].[Text]
& "*"));
--------------------------------------------

Thanks for trying to help!!!

[NeilAnderson]

Hi

Are you absolutely sure that the type mismatch is being caused by the
query string.

Try this:-

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim db As Database
Dim rst As Recordset
Dim sql As String

'[Forms]![frmVendorSetup]![Name].SetFocus - don't need to do this

sql = "SELECT tblVendors.Name FROM tblVendors WHERE " & _
"tblVendors.Name) LIKE '" & _
[Forms]![frmVendorSetup]![Name].[Text] & "*'"

'you don't need the brackets, indeed they might be the problem

Set db = OpenDatabase("adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

If rst.RecordCount > 0 Then

'i'd use rst.eof = true here if you are going to use
rst.recordcount I think you have to rst.movelast first.

MsgBox "This vendor may already exist, check again!", vbOKOnly

'what is this?????--- WAIT!: GoTo Exit_cmdSave_Click: This could be
your type mismatch.

GoTo Exit_cmdSave_Click:
ElseIf rst.RecordCount = 0 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70 'I'd use me.refresh

cboName.Requery
GoTo Exit_cmdSave_Click:
End If

'do this if you open a recordset
rst.close
set rst = nothing

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

By the way what happens if it is a new vendor who just happens to have
a name like another one? How do you save?
Neil

[TD]

Actually since my last post I figured it out and this is the final solution.
It works!

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

[Forms]![frmVendorSetup]![txtName].SetFocus

sql = "SELECT tblVendors.VendorName FROM tblVendors WHERE
(((tblVendors.VendorName) Like '" & [Forms]![frmVendorSetup]![txtName] & "*"
& "'));"

Set db = OpenDatabase("adsi.mdb")
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

If rst.RecordCount > 0 Then
MsgBox "This vendor may already exist, check again!", vbOKOnly,
"WAIT!": GoTo Exit_cmdSave_Click:
GoTo Exit_cmdSave_Click:
ElseIf rst.RecordCount = 0 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Vendor saved", vbOKOnly, DONE
cboName.Requery
GoTo Exit_cmdSave_Click:
End If

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub