SQL statements (INSERT...INTO)

[Kelvin]

Is it possible when coding a SQL statement to use variables within an
INSERT INTO statement i.e the variables contain some data I wish to
insert along with the standard entity.attribut e data?

E.G.

2 Entites/Tables, tblA and tblB. Both have the same Attributes/fields
'Fname' and 'Lname. So my SQL statement looks like this:-

"INSERT INTO tblB (Fname, Lname) SELECT tblA.Fname FROM tblA
WHERE tblA.Fname='" & strFname & "' VALUES(" & strLname & ");"

So I know that the WHERE clause works using the variable 'strFname' as
a container for the search criteria but is there a way of getting the
variable 'strLname' to insert with the rest of the data?

Any help would be appreciated

Cheers,

Kelvin

[Bas Cost Budde]

Re: SQL statements (INSERT...INTO)

Kelvin wrote:
[color=blue]
> Is it possible when coding a SQL statement to use variables within an
> INSERT INTO statement i.e the variables contain some data I wish to
> insert along with the standard entity.attribut e data?
>
> E.G.
>
> 2 Entites/Tables, tblA and tblB. Both have the same Attributes/fields
> 'Fname' and 'Lname. So my SQL statement looks like this:-
>
> "INSERT INTO tblB (Fname, Lname) SELECT tblA.Fname FROM tblA
> WHERE tblA.Fname='" & strFname & "' VALUES(" & strLname & ");"
>
> So I know that the WHERE clause works using the variable 'strFname' as
> a container for the search criteria but is there a way of getting the
> variable 'strLname' to insert with the rest of the data?[/color]

"INSERT INTO tblB (Fname, Lname) SELECT tblA.Fname, '"& strLname &"'
FROM tblA
WHERE tblA.Fname='" & strFname & "' VALUES(" & strLname & ");"

should do the trick
--
Bas Cost Budde

[Tony Toews]

Re: SQL statements (INSERT...INTO)

starwars <nobody@tatooin e.homelinux.net > wrote:
[color=blue]
>Comments: This message did not originate from the Sender address above.
> It was remailed automatically by anonymizing remailer software.[/color]

Folks, If you are new to the newsgroups this person is not at all representative of
the assistance you will receive.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

[Pieter Linden]

Re: SQL statements (INSERT...INTO)

If you use SELECT INTO then you should be able to do this no problem.
You could also execute the select statement, open it into a static
recordset and do a recordcount...