Insert Problem

[Hanksor]

Below is my code for adding values to another table from a Form. Everything
works great except where it trys to add the QTR value. Where am I going
wrong? Any help will be appreciated.

Dim strQtr As String
Dim strSQL As String
Dim qtrVAL As Integer

qtrVAL = Format(Date, "q")

If qtrVAL = 1 Then
strQtr = "1st"
ElseIf qtrVAL = 2 Then
strQtr = "2nd"
ElseIf qtrVAL = 3 Then
strQtr = "3rd"
ElseIf qtrVAL = 4 Then
strQtr = "4th"
End If

strSQL = "INSERT INTO tblTest " & "(V_ID,Text1,Text2) Values([V_ID], strQtr,
[Text9])"
DoCmd.RunSQL strSQL

Thanx,

Hanksor
Oregon City,OR

 

[JethroUK©]

something strange here:

strSQL = "INSERT INTO tblTest " & "(V_ID,Text1,Text2) Values([V_ID], strQtr,
[Text9])"

you are concatenating 2 absolute strings - i.e. it's no different from:

strSQL = "INSERT INTO tblTest (V_ID,Text1,Text2) Values([V_ID], strQtr,
[Text9])"

which wont be interpreted properly

 

[Hanksor]

My problem is I'm staring right at it and can't see the problem. I can't
figure out why it won't input the value of strQTR into the SQL statement. It
alway asks for the value of strQTR when I run the code.

I changed the sql statement to read;

strSQL = "INSERT INTO tblTest (V_ID,Text1,Text2) Values([V_ID], strQtr,
[Text9])"

Still the problem..........

something strange here:

strSQL = "INSERT INTO tblTest " & "(V_ID,Text1,Text2) Values([V_ID],
strQtr,
[Text9])"

you are concatenating 2 absolute strings - i.e. it's no different from:

strSQL = "INSERT INTO tblTest (V_ID,Text1,Text2) Values([V_ID], strQtr,
[Text9])"

which wont be interpreted properly

Below is my code for adding values to another table from a Form. Everything
works great except where it trys to add the QTR value. Where am I going
wrong? Any help will be appreciated.

Dim strQtr As String
Dim strSQL As String
Dim qtrVAL As Integer

qtrVAL = Format(Date, "q")

If qtrVAL = 1 Then
strQtr = "1st"
ElseIf qtrVAL = 2 Then
strQtr = "2nd"
ElseIf qtrVAL = 3 Then
strQtr = "3rd"
ElseIf qtrVAL = 4 Then
strQtr = "4th"
End If

strSQL = "INSERT INTO tblTest " & "(V_ID,Text1,Text2) Values([V_ID], strQtr,
[Text9])"
DoCmd.RunSQL strSQL

Thanx,

Hanksor
Oregon City,OR

 

[Dirk Goldgar]

Below is my code for adding values to another table from a Form.
Everything works great except where it trys to add the QTR value.
Where am I going wrong? Any help will be appreciated.

Dim strQtr As String
Dim strSQL As String
Dim qtrVAL As Integer

qtrVAL = Format(Date, "q")

If qtrVAL = 1 Then
strQtr = "1st"
ElseIf qtrVAL = 2 Then
strQtr = "2nd"
ElseIf qtrVAL = 3 Then
strQtr = "3rd"
ElseIf qtrVAL = 4 Then
strQtr = "4th"
End If

strSQL = "INSERT INTO tblTest " & "(V_ID,Text1,Text2) Values([V_ID],
strQtr, [Text9])"
DoCmd.RunSQL strSQL

While Access can figure out that "[Text9]" is the name of a control on
the current form, it has no idea what the variable strQtr is. You need
to build that value into your SQL string as a literal. Try this:

strSQL = _
"INSERT INTO tblTest (V_ID,Text1,Text2) " & _
"Values([V_ID], '" & strQtr & "', [Text9])"

 

[Hanksor]

That did it!!!! Thank you very much. I couldn't see the forest for the
trees..... Thanks again.....

Below is my code for adding values to another table from a Form.
Everything works great except where it trys to add the QTR value.
Where am I going wrong? Any help will be appreciated.

Dim strQtr As String
Dim strSQL As String
Dim qtrVAL As Integer

qtrVAL = Format(Date, "q")

If qtrVAL = 1 Then
strQtr = "1st"
ElseIf qtrVAL = 2 Then
strQtr = "2nd"
ElseIf qtrVAL = 3 Then
strQtr = "3rd"
ElseIf qtrVAL = 4 Then
strQtr = "4th"
End If

strSQL = "INSERT INTO tblTest " & "(V_ID,Text1,Text2) Values([V_ID],
strQtr, [Text9])"
DoCmd.RunSQL strSQL

While Access can figure out that "[Text9]" is the name of a control on
the current form, it has no idea what the variable strQtr is. You need
to build that value into your SQL string as a literal. Try this:

strSQL = _
"INSERT INTO tblTest (V_ID,Text1,Text2) " & _
"Values([V_ID], '" & strQtr & "', [Text9])"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)