SQL Type mismatch erro

D

David

Hello,

I am trying to convert some of my queries to SQL so that I can place them
into code. One query changes an product identifier that has dashes in it
(pniin) to a product identifier without the dashes (NIIN). The data resides
in a table called Batch1.
The SQL for the query is: SELECT Batch1.pniin, Replace([pniin],"-","") AS
NIIN
FROM Batch1;

My code reads: Private Sub Command235_Click()
On Error GoTo Err_Command235_Click

SQL = "SELECT Batch1.pniin, Replace([pniin]," - ","") AS NIIN FROM
Batch1;"
DoCmd.RunSQL SQL

Exit_Command235_Click:
Exit Sub

Err_Command235_Click:
MsgBox Err.Description
Resume Exit_Command235_Click

End Sub

I get Type Mismatch error when this runs. Sorry for being long winded but I
cannot figure out how to fix this. Please help!
 
D

Douglas J. Steele

Not sure why you're getting that specific error, but you cannot use RunSQL
with a Select statement: it's only intended for Action queries (INSERT INTO,
UPDATE, DELETE,...)

What were you hoping that running the SQL would do?
 
S

Stefan Hoffmann

hi David,
SQL = "SELECT Batch1.pniin, Replace([pniin]," - ","") AS NIIN FROM

I get Type Mismatch error when this runs. Sorry for being long winded but I
cannot figure out how to fix this. Please help!
Click to expand...
You need quotes for the string operation in your SQL string, e.g.

SQL = "SELECT [pniin], Replace([pniin], '-', '') AS NIIN FROM .."


mfG
--> stefan <--
 
D

David

Doug,

When I run the query it does the following (example):

pniin NIIN
00-634-3999 006343999
01-521-5770 015215770
01-451-8246 014518246

All I trying to do is get this into SQL language so I can place it in my
code and delete the queries.

Is this possible?

Douglas J. Steele said:
Not sure why you're getting that specific error, but you cannot use RunSQL
with a Select statement: it's only intended for Action queries (INSERT INTO,
UPDATE, DELETE,...)

What were you hoping that running the SQL would do?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David said:
Hello,

I am trying to convert some of my queries to SQL so that I can place them
into code. One query changes an product identifier that has dashes in it
(pniin) to a product identifier without the dashes (NIIN). The data
resides
in a table called Batch1.
The SQL for the query is: SELECT Batch1.pniin, Replace([pniin],"-","") AS
NIIN
FROM Batch1;

My code reads: Private Sub Command235_Click()
On Error GoTo Err_Command235_Click

SQL = "SELECT Batch1.pniin, Replace([pniin]," - ","") AS NIIN FROM
Batch1;"
DoCmd.RunSQL SQL

Exit_Command235_Click:
Exit Sub

Err_Command235_Click:
MsgBox Err.Description
Resume Exit_Command235_Click

End Sub

I get Type Mismatch error when this runs. Sorry for being long winded but
I
cannot figure out how to fix this. Please help!
Click to expand...
Click to expand...
 
D

David

Stefan:

This is what I wrote to Doug:

Doug,

When I run the query it does the following (example):

pniin NIIN
00-634-3999 006343999
01-521-5770 015215770
01-451-8246 014518246

All I trying to do is get this into SQL language so I can place it in my
code and delete the queries.

Is this possible?


Stefan Hoffmann said:
hi David,
SQL = "SELECT Batch1.pniin, Replace([pniin]," - ","") AS NIIN FROM

I get Type Mismatch error when this runs. Sorry for being long winded but I
cannot figure out how to fix this. Please help!
Click to expand...
You need quotes for the string operation in your SQL string, e.g.

SQL = "SELECT [pniin], Replace([pniin], '-', '') AS NIIN FROM .."


mfG
--> stefan <--
Click to expand...
 
D

Douglas J. Steele

I realize what the SQL is supposed to do. What I was trying to determine is
why you were trying to eliminate the query. As I said, you can't run SELECT
queries in VBA.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David said:
Doug,

When I run the query it does the following (example):

pniin NIIN
00-634-3999 006343999
01-521-5770 015215770
01-451-8246 014518246

All I trying to do is get this into SQL language so I can place it in my
code and delete the queries.

Is this possible?

Douglas J. Steele said:
Not sure why you're getting that specific error, but you cannot use
RunSQL
with a Select statement: it's only intended for Action queries (INSERT
INTO,
UPDATE, DELETE,...)

What were you hoping that running the SQL would do?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David said:
Hello,

I am trying to convert some of my queries to SQL so that I can place
them
into code. One query changes an product identifier that has dashes in
it
(pniin) to a product identifier without the dashes (NIIN). The data
resides
in a table called Batch1.
The SQL for the query is: SELECT Batch1.pniin, Replace([pniin],"-","")
AS
NIIN
FROM Batch1;

My code reads: Private Sub Command235_Click()
On Error GoTo Err_Command235_Click

SQL = "SELECT Batch1.pniin, Replace([pniin]," - ","") AS NIIN FROM
Batch1;"
DoCmd.RunSQL SQL

Exit_Command235_Click:
Exit Sub

Err_Command235_Click:
MsgBox Err.Description
Resume Exit_Command235_Click

End Sub

I get Type Mismatch error when this runs. Sorry for being long winded
but
I
cannot figure out how to fix this. Please help!
Click to expand...
Click to expand...
Click to expand...
 
D

David

Sorry about that Doug. I mis-understood what you were asking. That would
explain why it will not work then If I cannot run SELECT queries in VBA. I
have UPDATE and INSERT SQL's running in VBA and just assumed that a SELECT
query would work also. You know what happens when you assume.

Thanks

Douglas J. Steele said:
I realize what the SQL is supposed to do. What I was trying to determine is
why you were trying to eliminate the query. As I said, you can't run SELECT
queries in VBA.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


David said:
Doug,

When I run the query it does the following (example):

pniin NIIN
00-634-3999 006343999
01-521-5770 015215770
01-451-8246 014518246

All I trying to do is get this into SQL language so I can place it in my
code and delete the queries.

Is this possible?

Douglas J. Steele said:
Not sure why you're getting that specific error, but you cannot use
RunSQL
with a Select statement: it's only intended for Action queries (INSERT
INTO,
UPDATE, DELETE,...)

What were you hoping that running the SQL would do?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,

I am trying to convert some of my queries to SQL so that I can place
them
into code. One query changes an product identifier that has dashes in
it
(pniin) to a product identifier without the dashes (NIIN). The data
resides
in a table called Batch1.
The SQL for the query is: SELECT Batch1.pniin, Replace([pniin],"-","")
AS
NIIN
FROM Batch1;

My code reads: Private Sub Command235_Click()
On Error GoTo Err_Command235_Click

SQL = "SELECT Batch1.pniin, Replace([pniin]," - ","") AS NIIN FROM
Batch1;"
DoCmd.RunSQL SQL

Exit_Command235_Click:
Exit Sub

Err_Command235_Click:
MsgBox Err.Description
Resume Exit_Command235_Click

End Sub

I get Type Mismatch error when this runs. Sorry for being long winded
but
I
cannot figure out how to fix this. Please help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Opening New Form 2
drop down list: how to open on cursor 2
Compile Error : Type Mismatch 2
Append Query Type Mismatch (Apologies if this is a duplicate post) 1
Error: Compile Error,ByRef argument type mismatch 0
Type Mismatch generated by code. 2
"data type mismatch" 6
Stumped by a Type mismatch error 2

Top