Using variables

[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

 

[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

 

[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

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

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