By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,022 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Type Mismatch error in sql statement containing "Like"

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

P: n/a
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!!!

Nov 13 '05 #4

P: n/a
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
Nov 13 '05 #5

P: n/a
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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.