Variables in SQL statements

[Jim Jones]

Some help needed for a novice

In my program I first input values for string variables Inputfile and
Varyear, as follows:

Dim Inputfile As String, Varyear As String
Inputfile = InputBox("Name of file to be input")
Varyear = InputBox("Year eg 08")

I then use the value of Inputfile to identify the text file from which I
import the data:

DoCmd.TransferText acImportFixed, "IMPORT CANDIDATE DETAILS", "Temp", _
"C:\Documents and Settings\jjones\Desktop\Development\" & Inputfile &
".TXT", False, ""

So far, so good

But then I want to use the second variable Varyear as part of an SQL
statement such as

DoCmd.RunSQL "UPDATE Temp SET Temp.[Year] = Varyear", -1

This does not work. So, how do I represent Varyear in a statement like this

Any help would be much appreciated.

Jim Jones

 

[Allen Browne]

Concatenate the value into the string:
DoCmd.RunSQL "UPDATE Temp SET Temp.[Year] = " & Varyear

BTW, I find it better to use Execute rather than RunSQL, because you can
find out what happened. Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Jim Jones]

Allen

The statement you have suggested seems to be incomplete - could you look at
it again for me

Many thanks for the help - it's much appreciated.

Jim Jones

Concatenate the value into the string:
DoCmd.RunSQL "UPDATE Temp SET Temp.[Year] = " & Varyear

BTW, I find it better to use Execute rather than RunSQL, because you can
find out what happened. Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Some help needed for a novice

In my program I first input values for string variables Inputfile and
Varyear, as follows:

Dim Inputfile As String, Varyear As String
Inputfile = InputBox("Name of file to be input")
Varyear = InputBox("Year eg 08")

I then use the value of Inputfile to identify the text file from which I
import the data:

DoCmd.TransferText acImportFixed, "IMPORT CANDIDATE DETAILS", "Temp", _
"C:\Documents and Settings\jjones\Desktop\Development\" & Inputfile &
".TXT", False, ""

So far, so good

But then I want to use the second variable Varyear as part of an SQL
statement such as

DoCmd.RunSQL "UPDATE Temp SET Temp.[Year] = Varyear", -1

This does not work. So, how do I represent Varyear in a statement like
this

 

[Allen Browne]

Declare a variable:
dim strSql As String
Dim iYear As Integer
iYear = 2000
strSql = "UPDATE Temp SET Temp.[Year] = " & iYear
Debug.Print strSql
DoCmd.RunSQL strSql

The statement will be incomplete if your variable does not contain a value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

The statement you have suggested seems to be incomplete - could you look
at
it again for me

Many thanks for the help - it's much appreciated.

Jim Jones

Concatenate the value into the string:
DoCmd.RunSQL "UPDATE Temp SET Temp.[Year] = " & Varyear

BTW, I find it better to use Execute rather than RunSQL, because you can
find out what happened. Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Some help needed for a novice

In my program I first input values for string variables Inputfile and
Varyear, as follows:

Dim Inputfile As String, Varyear As String
Inputfile = InputBox("Name of file to be input")
Varyear = InputBox("Year eg 08")

I then use the value of Inputfile to identify the text file from which
I
import the data:

DoCmd.TransferText acImportFixed, "IMPORT CANDIDATE DETAILS", "Temp", _
"C:\Documents and Settings\jjones\Desktop\Development\" & Inputfile
&
".TXT", False, ""

So far, so good

But then I want to use the second variable Varyear as part of an SQL
statement such as

DoCmd.RunSQL "UPDATE Temp SET Temp.[Year] = Varyear", -1

This does not work. So, how do I represent Varyear in a statement like
this

 

[Jim Jones]

Dear Allen

Now I see! Many thanks again!

Jim Jones

Declare a variable:
dim strSql As String
Dim iYear As Integer
iYear = 2000
strSql = "UPDATE Temp SET Temp.[Year] = " & iYear
Debug.Print strSql
DoCmd.RunSQL strSql

The statement will be incomplete if your variable does not contain a value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen

The statement you have suggested seems to be incomplete - could you look
at
it again for me

Many thanks for the help - it's much appreciated.

Jim Jones

Concatenate the value into the string:
DoCmd.RunSQL "UPDATE Temp SET Temp.[Year] = " & Varyear

BTW, I find it better to use Execute rather than RunSQL, because you can
find out what happened. Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Some help needed for a novice

In my program I first input values for string variables Inputfile and
Varyear, as follows:

Dim Inputfile As String, Varyear As String
Inputfile = InputBox("Name of file to be input")
Varyear = InputBox("Year eg 08")

I then use the value of Inputfile to identify the text file from which
I
import the data:

DoCmd.TransferText acImportFixed, "IMPORT CANDIDATE DETAILS", "Temp", _
"C:\Documents and Settings\jjones\Desktop\Development\" & Inputfile
&
".TXT", False, ""

So far, so good

But then I want to use the second variable Varyear as part of an SQL
statement such as

DoCmd.RunSQL "UPDATE Temp SET Temp.[Year] = Varyear", -1

This does not work. So, how do I represent Varyear in a statement like
this