INSERT using SELECT and parameters

[Serge Myrand]

Hi,

The first query return a type conversion error when executed within
MS-Access 2002 sp3. The proble is that parameter :MOD_TYPE is recognized
as a Text field even if the corresponding field MOD_TYPE is of type Text
and 20 characters long.

The second query run correctly and retun no error. I only replace the
parameter with a string. How can I typcast the parameter to force the
acceptation of my input value? This is a simplified version of the query
that use an INNER JOIN in the SELECT and over than 10 parameters of all
types.

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, :MOD_TYPE
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO


INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, "AAAA"
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO


thank you for your time
serge

 

[Serge Myrand]

Hi,

The first query return a type conversion error when executed within
MS-Access 2002 sp3. The problem is that parameter :MOD_TYPE is not
recognized
as a Text field even if the corresponding field MOD_TYPE is of type Text
and 20 characters long.

The second query run correctly and retun no error. I only replace the
parameter with a literal. How can I typcast the parameter to force the
acceptation of my input value? This is a simplified version of the query
that use an INNER JOIN in the SELECT and over than 10 parameters of all
types.

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, :MOD_TYPE
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, "AAAA"
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO

thank you for your time
serge

 

[Serge Myrand]

Hi,

Is MS-Access accept this kind of query?

thank you
serge

 

[John Spencer (MVP)]

Access will have problems with the colons. If those are supposed to be
parameter prompts, try surrounding them with brackets - [:Mod_Type].

 

[Serge Myrand]

Hi,

It does not change anything. When I use the literal "AAAA" in lieu of :MOD_TYPE
it works and I still have : in three places for the other parameters. Is this
kind of INSERT using some fields from a SELECT and some others from parameters
is acceptable in MS-ACCESS?

if you have any other idea it is welcome. I need the have this query to run.

Thank yopu for your time
serge

Access will have problems with the colons. If those are supposed to be
parameter prompts, try surrounding them with brackets - [:Mod_Type].

Hi,

Is MS-Access accept this kind of query?

thank you
serge

 

[Serge Myrand]

I tried [MOD_TYPE] and MOD_TYPE and it does not work. Is it possible to TYPE
CAST the parameter in the query to force MS-ACCESS to accept the value?

thank
serge

Access will have problems with the colons. If those are supposed to be
parameter prompts, try surrounding them with brackets - [:Mod_Type].

Hi,

Is MS-Access accept this kind of query?

thank you
serge

 

[Guest]

What do you mean by: "The problem is that parameter :MOD_TYPE is not
recognized as a Text field even if the corresponding field MOD_TYPE is of
type Text
and 20 characters long." The corresponding field in which table? There are
two tables to worry about: CLIENT_SELL_HIST and CLIENT_SELL.

What is :MOD_TYPE in CLIENT_SELL? Or, if this is a parameter, maybe you need
a PARAMETERS declaration: PARAMETERS MOD_SELL text

Good Luck!
--
Chaim

I tried [MOD_TYPE] and MOD_TYPE and it does not work. Is it possible to TYPE
CAST the parameter in the query to force MS-ACCESS to accept the value?

thank
serge

Access will have problems with the colons. If those are supposed to be
parameter prompts, try surrounding them with brackets - [:Mod_Type].

Hi,

Is MS-Access accept this kind of query?

thank you
serge

The first query return a type conversion error when executed within
MS-Access 2002 sp3. The problem is that parameter :MOD_TYPE is not
recognized
as a Text field even if the corresponding field MOD_TYPE is of type Text
and 20 characters long.


The second query run correctly and retun no error. I only replace the
parameter with a literal. How can I typcast the parameter to force the
acceptation of my input value? This is a simplified version of the query
that use an INNER JOIN in the SELECT and over than 10 parameters of all
types.

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, :MOD_TYPE
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO
INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, "AAAA"
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO

thank you for your time
serge

 

[Serge Myrand]

Hi,

Q- What do you mean by: "The problem is that parameter :MOD_TYPE is not
recognized as a Text field even if the corresponding field MOD_TYPE is of
type Text and 20 characters long.

A- :MOD_TYPE is a place holder that contains a string, I want to write into the
field MOD_TYPE of the table CLIENT_SELL_HIST. MS-Access return a type conversion
failure error message. So MS-Access does not recognize that :MOD_TYPE contains a
string (Text)

Q- " The corresponding field in which table? There are
two tables to worry about: CLIENT_SELL_HIST and CLIENT_SELL.

A- :MOD_TYPE contains a text value to write in the field MOD_TYPE of the table
CLIENT_SELL_HIST

Q- What is :MOD_TYPE in CLIENT_SELL? Or, if this is a parameter, maybe you need
a PARAMETERS declaration: PARAMETERS MOD_SELL text

A- :MOD_TYPE is a place holder teh table CLIENT_SELL does not have a MOD_TYPE or
:MOD_TYPE field.


Thank you for your time
serge

Good Luck!
--
Chaim

I tried [MOD_TYPE] and MOD_TYPE and it does not work. Is it possible to TYPE
CAST the parameter in the query to force MS-ACCESS to accept the value?

thank
serge

Access will have problems with the colons. If those are supposed to be
parameter prompts, try surrounding them with brackets - [:Mod_Type].

Serge Myrand wrote:

Hi,

Is MS-Access accept this kind of query?

thank you
serge

The first query return a type conversion error when executed within
MS-Access 2002 sp3. The problem is that parameter :MOD_TYPE is not
recognized
as a Text field even if the corresponding field MOD_TYPE is of type Text
and 20 characters long.


The second query run correctly and retun no error. I only replace the
parameter with a literal. How can I typcast the parameter to force the
acceptation of my input value? This is a simplified version of the query
that use an INNER JOIN in the SELECT and over than 10 parameters of all
types.

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, :MOD_TYPE
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO
INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, "AAAA"
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO

thank you for your time
serge

 

[John Spencer (MVP)]

Where are you trying to run this query? In Access, from a VB project, somewhere else?

If you are using Access, then you can't assign values to the your Placeholders
(variables) and use them directly in a query. You can assign values to
variables and use a custom user function (VBA) in the query to get the values.

Or you can store the values in controls on an OPEN form and reference the form
controls like below

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO,
Forms!YourFormName!YourMODTYPEControlName
FROM CLIENT_SELL
WHERE PART_NO = Forms!YourFormName!YourPartNoControlName AND
REV_NO = Forms!YourFormName!YourREV_NOControlName AND
CLIENT_NO = Forms!YourFormName!YourCLIENT_NOControlName


Custom function would be something like the following in a code module.

Public Function GetPartNo()
GetPartNo = PartNoVariable
End Function

Access does not deal well with items that are named with a colon as the first
character of the name.

Hi,

Q- What do you mean by: "The problem is that parameter :MOD_TYPE is not
recognized as a Text field even if the corresponding field MOD_TYPE is of
type Text and 20 characters long.

A- :MOD_TYPE is a place holder that contains a string, I want to write into the
field MOD_TYPE of the table CLIENT_SELL_HIST. MS-Access return a type conversion
failure error message. So MS-Access does not recognize that :MOD_TYPE contains a
string (Text)

Q- " The corresponding field in which table? There are
two tables to worry about: CLIENT_SELL_HIST and CLIENT_SELL.

A- :MOD_TYPE contains a text value to write in the field MOD_TYPE of the table
CLIENT_SELL_HIST

Q- What is :MOD_TYPE in CLIENT_SELL? Or, if this is a parameter, maybe you need
a PARAMETERS declaration: PARAMETERS MOD_SELL text

A- :MOD_TYPE is a place holder teh table CLIENT_SELL does not have a MOD_TYPE or
:MOD_TYPE field.

Thank you for your time
serge

Good Luck!
--
Chaim

I tried [MOD_TYPE] and MOD_TYPE and it does not work. Is it possible to TYPE
CAST the parameter in the query to force MS-ACCESS to accept the value?

thank
serge



:

Access will have problems with the colons. If those are supposed to be
parameter prompts, try surrounding them with brackets - [:Mod_Type].

Serge Myrand wrote:

Hi,

Is MS-Access accept this kind of query?

thank you
serge

The first query return a type conversion error when executed within
MS-Access 2002 sp3. The problem is that parameter :MOD_TYPE is not
recognized
as a Text field even if the corresponding field MOD_TYPE is of type Text
and 20 characters long.


The second query run correctly and retun no error. I only replace the
parameter with a literal. How can I typcast the parameter to force the
acceptation of my input value? This is a simplified version of the query
that use an INNER JOIN in the SELECT and over than 10 parameters of all
types.

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, :MOD_TYPE
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO
INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, "AAAA"
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO

thank you for your time
serge

 

[Serge Myrand]

Thank you John,

In fact, I use this code in a Delphi application (with ADO 2.8 and JET OLED DB 4.0 sp 8).
The syntax is not the same in VB, but the query is simple and return about the same error
message. To solve the problem, I use an INSERT + an UPADTE and it's ok... for know. Most
of the time you can use parameters as place holders in a SELECT but apparently not when
the SELECT is part of an INSERT. All the references MUST come from the table fields.

Thank you very much for your time
bonne vacances d'été
serge

Where are you trying to run this query? In Access, from a VB project, somewhere else?

If you are using Access, then you can't assign values to the your Placeholders
(variables) and use them directly in a query. You can assign values to
variables and use a custom user function (VBA) in the query to get the values.

Or you can store the values in controls on an OPEN form and reference the form
controls like below

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO,
Forms!YourFormName!YourMODTYPEControlName
FROM CLIENT_SELL
WHERE PART_NO = Forms!YourFormName!YourPartNoControlName AND
REV_NO = Forms!YourFormName!YourREV_NOControlName AND
CLIENT_NO = Forms!YourFormName!YourCLIENT_NOControlName

Custom function would be something like the following in a code module.

Public Function GetPartNo()
GetPartNo = PartNoVariable
End Function

Access does not deal well with items that are named with a colon as the first
character of the name.

Hi,

Q- What do you mean by: "The problem is that parameter :MOD_TYPE is not
recognized as a Text field even if the corresponding field MOD_TYPE is of
type Text and 20 characters long.

A- :MOD_TYPE is a place holder that contains a string, I want to write into the
field MOD_TYPE of the table CLIENT_SELL_HIST. MS-Access return a type conversion
failure error message. So MS-Access does not recognize that :MOD_TYPE contains a
string (Text)

Q- " The corresponding field in which table? There are
two tables to worry about: CLIENT_SELL_HIST and CLIENT_SELL.

A- :MOD_TYPE contains a text value to write in the field MOD_TYPE of the table
CLIENT_SELL_HIST

Q- What is :MOD_TYPE in CLIENT_SELL? Or, if this is a parameter, maybe you need
a PARAMETERS declaration: PARAMETERS MOD_SELL text

A- :MOD_TYPE is a place holder teh table CLIENT_SELL does not have a MOD_TYPE or
:MOD_TYPE field.

Thank you for your time
serge

Good Luck!
--
Chaim

:



I tried [MOD_TYPE] and MOD_TYPE and it does not work. Is it possible to TYPE
CAST the parameter in the query to force MS-ACCESS to accept the value?

thank
serge



:

Access will have problems with the colons. If those are supposed to be
parameter prompts, try surrounding them with brackets - [:Mod_Type].

Serge Myrand wrote:

Hi,

Is MS-Access accept this kind of query?

thank you
serge

The first query return a type conversion error when executed within
MS-Access 2002 sp3. The problem is that parameter :MOD_TYPE is not
recognized
as a Text field even if the corresponding field MOD_TYPE is of type Text
and 20 characters long.


The second query run correctly and retun no error. I only replace the
parameter with a literal. How can I typcast the parameter to force the
acceptation of my input value? This is a simplified version of the query
that use an INNER JOIN in the SELECT and over than 10 parameters of all
types.

INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, :MOD_TYPE
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO
INSERT INTO CLIENT_SELL_HIST (PART_NO, REV_NO, CLIENT_NO, MOD_TYPE)
SELECT DISTINCT PART_NO, REV_NO, CLIENT_NO, "AAAA"
FROM CLIENT_SELL
WHERE PART_NO = ART_NO AND REV_NO = :REV_NO AND CLIENT_NO =
:CLIENT_NO

thank you for your time
serge