Variables in SQL statements

  • Thread starter Thread starter Jim Jones
  • Start date Start date
J

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

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

Allen Browne said:
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.

Jim Jones said:
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
Click to expand...
Click to expand...
 
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.

Jim Jones said:
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

Allen Browne said:
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.

Jim Jones said:
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
Click to expand...
Click to expand...
Click to expand...
 
Dear Allen

Now I see! Many thanks again!

Jim Jones

Allen Browne said:
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.

Jim Jones said:
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

Allen Browne said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top