Using variables

J

Jim Jones

I am trying to populate a new table (Tbl_MyTable_Temp) with records from an
existing table (Tbl_MyTable). The records to be included are determined by
one of the fields in the existing table, the value of which is input by the
user.

First I create the new table using an SQL statement as follows:
Dim StrSQL As String
StrSQL = "CREATE TABLE Tbl_Mytable_temp ........
DoCmd.RUNSQL StrSQL

I then use the following code to enable the user to input a value to a
string variable named Syllabus
Dim Syllabus As String
Syllabus = InputBox("Input Syllabus")

So far so good, but the I try to use the following code to populate
Tbl_Mytable_Temp with records from Tbl_Mytable for which one field [Syllcode]
has the same value as that inout by the user:

Dim StrSQL as String
StrSQL = "INSERT INTO MyTable_temp SELECT MyTable.* FROM MyTable WHERE
[MyTable].[syllcode] = Syllabus ;"
DoCmd.RunSQL StrSQL

However, the variable called Syllabus is not recognised in the SQL
statement. What am I doing wrong?

Any help would be much appreciated.

Jim Jones
Botswana
 
G

Graham R Seach

Jim,

The Jet Expression Service knows nothing about VBA variables, so you have to
supply the *value* of the variable, not its name.

StrSQL = "INSERT INTO MyTable_temp " & _
"SELECT MyTable.* " & _
"FROM MyTable " & _
"WHERE [MyTable].[syllcode] = """ & Syllabus & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
J

Jim Jones

Graham

Many thanks for the prompt response. Your solution worked immediately, and
I can now get on with the job.

I could see that the variable was not being recognized when it was part of
the SQL statement, but finding out how what to do in such circumstances is
difficult for a VBA novice such as myself. That's why this site is so useful
to folk like me - being able to take advantage of the expertise of MVP's such
as yourself is invaluable.

Many thanks again

Jim Jones
Botswana

Graham R Seach said:
Jim,

The Jet Expression Service knows nothing about VBA variables, so you have to
supply the *value* of the variable, not its name.

StrSQL = "INSERT INTO MyTable_temp " & _
"SELECT MyTable.* " & _
"FROM MyTable " & _
"WHERE [MyTable].[syllcode] = """ & Syllabus & """"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Jim Jones said:
I am trying to populate a new table (Tbl_MyTable_Temp) with records from an
existing table (Tbl_MyTable). The records to be included are determined
by
one of the fields in the existing table, the value of which is input by
the
user.

First I create the new table using an SQL statement as follows:
Dim StrSQL As String
StrSQL = "CREATE TABLE Tbl_Mytable_temp ........
DoCmd.RUNSQL StrSQL

I then use the following code to enable the user to input a value to a
string variable named Syllabus
Dim Syllabus As String
Syllabus = InputBox("Input Syllabus")

So far so good, but the I try to use the following code to populate
Tbl_Mytable_Temp with records from Tbl_Mytable for which one field
[Syllcode]
has the same value as that inout by the user:

Dim StrSQL as String
StrSQL = "INSERT INTO MyTable_temp SELECT MyTable.* FROM MyTable WHERE
[MyTable].[syllcode] = Syllabus ;"
DoCmd.RunSQL StrSQL

However, the variable called Syllabus is not recognised in the SQL
statement. What am I doing wrong?

Any help would be much appreciated.

Jim Jones
Botswana
 

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

Top