VBA type mismatch error - BUT the same exact query runs fine by it

[Irina]

Hello, can you tell me why I am getting
Run time error '13' type mismatch in code below
but query works if it is run as a query by itself
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"

appreciate your response
thanks

 

[Rui]

when copying the code to vba you need to be carefull with the quotes char and
the underscore char

you want something like:

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[forms]![frmClientReportFields2].[Individual] & "*"


Rui

 

[Douglas J. Steele]

Since Individual must be a Text field (or else there's no point using Like
on it), you need quotes around the value:

Since we seem to be dealing with names here, it's possible that name could
include an apostrophe (D'Arcy O'Brien), so use

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like """ & _
[forms]![frmClientReportFields2].[Individual] & """*"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

when copying the code to vba you need to be carefull with the quotes char
and
the underscore char

you want something like:

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[forms]![frmClientReportFields2].[Individual] & "*"


Rui

Hello, can you tell me why I am getting
Run time error '13' type mismatch in code below
but query works if it is run as a query by itself
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"

appreciate your response
thanks

 

[Irina]

Douglas and Rui
THANK YOU SO MUCH FOR HELPING
Awesome! I will give those suggestions a try

Since Individual must be a Text field (or else there's no point using Like
on it), you need quotes around the value:

Since we seem to be dealing with names here, it's possible that name could
include an apostrophe (D'Arcy O'Brien), so use

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like """ & _
[forms]![frmClientReportFields2].[Individual] & """*"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

when copying the code to vba you need to be carefull with the quotes char
and
the underscore char

you want something like:

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[forms]![frmClientReportFields2].[Individual] & "*"


Rui

Hello, can you tell me why I am getting
Run time error '13' type mismatch in code below
but query works if it is run as a query by itself
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"

appreciate your response
thanks

 

[Irina]

Hi Rui
I tried your code, it gives me error
"Syntax error (missing operator) in query expression
'tblClientReportFields2.Individual Like "Smith"*"
do you have any suggestions on how to fix this error?

thanks

when copying the code to vba you need to be carefull with the quotes char and
the underscore char

you want something like:

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[forms]![frmClientReportFields2].[Individual] & "*"


Rui

Hello, can you tell me why I am getting
Run time error '13' type mismatch in code below
but query works if it is run as a query by itself
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"

appreciate your response
thanks

 

[Irina]

Hi Douglas
i am getting a syntax error in your code
for missing operator

do you have any suggestions on how to fix this error?

thanks

Since Individual must be a Text field (or else there's no point using Like
on it), you need quotes around the value:

Since we seem to be dealing with names here, it's possible that name could
include an apostrophe (D'Arcy O'Brien), so use

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like """ & _
[forms]![frmClientReportFields2].[Individual] & """*"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

when copying the code to vba you need to be carefull with the quotes char
and
the underscore char

you want something like:

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[forms]![frmClientReportFields2].[Individual] & "*"


Rui

Hello, can you tell me why I am getting
Run time error '13' type mismatch in code below
but query works if it is run as a query by itself
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"

appreciate your response
thanks

 

[Douglas J. Steele]

Sorry, slight typo on my part.

Rather than & """*" at the end, it should be & "*"""



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas
i am getting a syntax error in your code
for missing operator

do you have any suggestions on how to fix this error?

thanks

Since Individual must be a Text field (or else there's no point using
Like
on it), you need quotes around the value:

Since we seem to be dealing with names here, it's possible that name
could
include an apostrophe (D'Arcy O'Brien), so use

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like """ & _
[forms]![frmClientReportFields2].[Individual] & """*"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

when copying the code to vba you need to be carefull with the quotes
char
and
the underscore char

you want something like:

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[forms]![frmClientReportFields2].[Individual] & "*"


Rui


:

Hello, can you tell me why I am getting
Run time error '13' type mismatch in code below
but query works if it is run as a query by itself
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"

appreciate your response
thanks

 

[Rui]

My mistake, forgot about that.

Just modify the last bit of code
[forms]![frmClientReportFields2].[Individual] & "*"
to
[forms]![frmClientReportFields2].[Individual] & "'*'"

(two single quotes added around the *)

Cheers
Rui

Hi Rui
I tried your code, it gives me error
"Syntax error (missing operator) in query expression
'tblClientReportFields2.Individual Like "Smith"*"
do you have any suggestions on how to fix this error?

thanks

when copying the code to vba you need to be carefull with the quotes char and
the underscore char

you want something like:

sql_code = "SELECT tblClientReport.*, tblClientReport.Individual " & _
" FROM tblClientReport " & _
" WHERE tblClientReport.Individual Like " & _
[forms]![frmClientReportFields2].[Individual] & "*"


Rui

Hello, can you tell me why I am getting
Run time error '13' type mismatch in code below
but query works if it is run as a query by itself
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"

appreciate your response
thanks