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

  • Thread starter Thread starter Irina
  • Start date Start date
I

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
 
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
 
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)


Rui said:
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


Irina said:
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
Click to expand...
Click to expand...
 
Douglas and Rui
THANK YOU SO MUCH FOR HELPING
Awesome! I will give those suggestions a try

Douglas J. Steele said:
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)


Rui said:
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


Irina said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
Rui said:
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


Irina said:
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
Click to expand...
Click to expand...
 
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

Douglas J. Steele said:
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)


Rui said:
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


Irina said:
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
Click to expand...
Click to expand...
Click to expand...
 
Sorry, slight typo on my part.

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



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Irina said:
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

Douglas J. Steele said:
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)


Rui said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Irina said:
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
Rui said:
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


Irina said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top