Programming Loop with SQL Update Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I cannot figure out why this won't work. It doesn't like the variables in
the sql is the problem, but i can't figure out a way around it. I am goign
to have to have a TON of update queries if i can't get this to work.

the gist is trying to get the code to automatically run the update query
looking at 7 columns in a record, then move up one, and check the next 7 for
teh "code" which is #M# if it finds that code, copy that data, and the next
6 fields to anohter table.


Any advice???

Function MCodeUpdate()


Dim CODE As String
CODE = "#M#"
Dim CodeNum As Integer
Dim CodeNum1 As String
Dim CodeNum2 As String
Dim CodeNum3 As String
Dim CodeNum4 As String
Dim CodeNum5 As String
Dim CodeNum6 As String
Dim CodeNum7 As String


Dim SQL As String

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, CodeNum1,
CodeNum2, CodeNum3, CodeNum4, CodeNum5, CodeNum6, CodeNum7 " & _
"FROM [ImportTable] " & _
"WHERE CodeNum1 = CODE;"


DoCmd.RunSQL SQL

Next CodeNum


End Function
 
Hi,
you should replace variable names with it values:

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5],
[M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 & ", " & CodeNum2 & ", " & CodeNum3 & ", " & CodeNum4 & ", " &
CodeNum5 & ", " & CodeNum6 & ", " & CodeNum7 & " " & _
"FROM [ImportTable] " & _
"WHERE CodeNum1 = CODE;"
 
I'm not sure, but I think that what you are looking for is

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 &"," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5 &
"," & CodeNum6 & "," & CodeNum7 " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE
 
the code field is declared as string. the actual code that i have to look
for is #M#

here is what i got when i tried to run that:

Runtime Error '3075'
Syntax error in date in query expression 'ImportTable.Field38 = #M#'.

here is the code that was run

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)
CODE = "#M#"


SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,[M-Detail1],
[M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6], [M-Detail7]
)" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 & "," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5
& "," & CodeNum6 & "," & CodeNum7 & " " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE






Ofer said:
I'm not sure, but I think that what you are looking for is

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 &"," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5 &
"," & CodeNum6 & "," & CodeNum7 " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



John C Brainard said:
I cannot figure out why this won't work. It doesn't like the variables in
the sql is the problem, but i can't figure out a way around it. I am goign
to have to have a TON of update queries if i can't get this to work.

the gist is trying to get the code to automatically run the update query
looking at 7 columns in a record, then move up one, and check the next 7 for
teh "code" which is #M# if it finds that code, copy that data, and the next
6 fields to anohter table.


Any advice???

Function MCodeUpdate()


Dim CODE As String
CODE = "#M#"
Dim CodeNum As Integer
Dim CodeNum1 As String
Dim CodeNum2 As String
Dim CodeNum3 As String
Dim CodeNum4 As String
Dim CodeNum5 As String
Dim CodeNum6 As String
Dim CodeNum7 As String


Dim SQL As String

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, CodeNum1,
CodeNum2, CodeNum3, CodeNum4, CodeNum5, CodeNum6, CodeNum7 " & _
"FROM [ImportTable] " & _
"WHERE CodeNum1 = CODE;"


DoCmd.RunSQL SQL

Next CodeNum


End Function
Click to expand...
Click to expand...
 
John said:
the code field is declared as string. the actual code that i have to look
for is #M#

here is what i got when i tried to run that:

Runtime Error '3075'
Syntax error in date in query expression 'ImportTable.Field38 = #M#'.

here is the code that was run

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)
CODE = "#M#"


SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,[M-Detail1],
[M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6], [M-Detail7]
)" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 & "," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5
& "," & CodeNum6 & "," & CodeNum7 & " " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE
I'm not sure, but I think that what you are looking for is
Click to expand...
[quoted text clipped - 59 lines]
Click to expand...

Your INSERT is running on every iteration whether or not #M# contains a valid
record.

By adding an IF...THEN scenario to determine whether or not the INSERT should
run might resolve the run-time error that might be caused by populating #M#
as NULL. If this is your intention, make sure the destination field/table
allows a NULL value.

Else add the following

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)
CODE = "#M#"

If LEN("#M#") > 0 Or NOT ISNULL("#M#") THEN
SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,[M-Detail1]
,
[M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7]
)" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 & "," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 &
"," & CodeNum5
& "," & CodeNum6 & "," & CodeNum7 & " " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE
Else
Next CodeNum
End If
 
Your last line should probably look like this:
"WHERE " & CodeNum1 & " = '" & CODE & "'"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



John C Brainard said:
the code field is declared as string. the actual code that i have to look
for is #M#

here is what i got when i tried to run that:

Runtime Error '3075'
Syntax error in date in query expression 'ImportTable.Field38 = #M#'.

here is the code that was run

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)
CODE = "#M#"


SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,[M-Detail1],
[M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6], [M-Detail7]
)" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 & "," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5
& "," & CodeNum6 & "," & CodeNum7 & " " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE






Ofer said:
I'm not sure, but I think that what you are looking for is

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 &"," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5 &
"," & CodeNum6 & "," & CodeNum7 " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



John C Brainard said:
I cannot figure out why this won't work. It doesn't like the variables in
the sql is the problem, but i can't figure out a way around it. I am goign
to have to have a TON of update queries if i can't get this to work.

the gist is trying to get the code to automatically run the update query
looking at 7 columns in a record, then move up one, and check the next 7 for
teh "code" which is #M# if it finds that code, copy that data, and the next
6 fields to anohter table.


Any advice???

Function MCodeUpdate()


Dim CODE As String
CODE = "#M#"
Dim CodeNum As Integer
Dim CodeNum1 As String
Dim CodeNum2 As String
Dim CodeNum3 As String
Dim CodeNum4 As String
Dim CodeNum5 As String
Dim CodeNum6 As String
Dim CodeNum7 As String


Dim SQL As String

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, CodeNum1,
CodeNum2, CodeNum3, CodeNum4, CodeNum5, CodeNum6, CodeNum7 " & _
"FROM [ImportTable] " & _
"WHERE CodeNum1 = CODE;"


DoCmd.RunSQL SQL

Next CodeNum


End Function
Click to expand...
Click to expand...
Click to expand...
 
WOWOWOWOW You guys are awesome. Thanks so much...that last line change
fixed it all.



Roger Carlson said:
Your last line should probably look like this:
"WHERE " & CodeNum1 & " = '" & CODE & "'"

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



John C Brainard said:
the code field is declared as string. the actual code that i have to look
for is #M#

here is what i got when i tried to run that:

Runtime Error '3075'
Syntax error in date in query expression 'ImportTable.Field38 = #M#'.

here is the code that was run

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)
CODE = "#M#"


SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,[M-Detail1],
[M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6], [M-Detail7]
)" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 & "," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5
& "," & CodeNum6 & "," & CodeNum7 & " " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE






Ofer said:
I'm not sure, but I think that what you are looking for is

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 &"," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5 &
"," & CodeNum6 & "," & CodeNum7 " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I cannot figure out why this won't work. It doesn't like the variables in
the sql is the problem, but i can't figure out a way around it. I am goign
to have to have a TON of update queries if i can't get this to work.

the gist is trying to get the code to automatically run the update query
looking at 7 columns in a record, then move up one, and check the next 7 for
teh "code" which is #M# if it finds that code, copy that data, and the next
6 fields to anohter table.


Any advice???

Function MCodeUpdate()


Dim CODE As String
CODE = "#M#"
Dim CodeNum As Integer
Dim CodeNum1 As String
Dim CodeNum2 As String
Dim CodeNum3 As String
Dim CodeNum4 As String
Dim CodeNum5 As String
Dim CodeNum6 As String
Dim CodeNum7 As String


Dim SQL As String

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)

SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,
[M-Detail1], [M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6],
[M-Detail7] )" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, CodeNum1,
CodeNum2, CodeNum3, CodeNum4, CodeNum5, CodeNum6, CodeNum7 " & _
"FROM [ImportTable] " & _
"WHERE CodeNum1 = CODE;"


DoCmd.RunSQL SQL

Next CodeNum


End Function
Click to expand...
Click to expand...
Click to expand...
 
John said:
the code field is declared as string. the actual code that i have to look
for is #M#

here is what i got when i tried to run that:

Runtime Error '3075'
Syntax error in date in query expression 'ImportTable.Field38 = #M#'.

here is the code that was run

For CodeNum = 38 To 100
CodeNum1 = "ImportTable.Field" & (CodeNum)
CodeNum2 = "ImportTable.Field" & (CodeNum + 1)
CodeNum3 = "ImportTable.Field" & (CodeNum + 2)
CodeNum4 = "ImportTable.Field" & (CodeNum + 3)
CodeNum5 = "ImportTable.Field" & (CodeNum + 4)
CodeNum6 = "ImportTable.Field" & (CodeNum + 5)
CodeNum7 = "ImportTable.Field" & (CodeNum + 6)
CODE = "#M#"


SQL = "INSERT INTO [M-Detail Table] ( ID, AccountNumber,[M-Detail1],
[M-Detail2], [M-Detail3], [M-Detail4], [M-Detail5], [M-Detail6], [M-Detail7]
)" & _
"SELECT ImportTable.ID, ImportTable.AccountNumber, " &
CodeNum1 & "," & CodeNum2 & "," & CodeNum3 & "," & CodeNum4 & "," & CodeNum5
& "," & CodeNum6 & "," & CodeNum7 & " " & _
"FROM [ImportTable] " & _
"WHERE " & CodeNum1 & " = " & CODE
I'm not sure, but I think that what you are looking for is
Click to expand...
[quoted text clipped - 59 lines]
Click to expand...

John,

I'm gonna take a stab in the dark but I think I see an addtional problem here.


You're declaring CODE as a String datatype but using the syntax ## which, in
some languages, is interpreted as a date. If CODE is never a date, using only
the double quotes might resolve this issue "M"

If, in fact, CODE is always a string, passing #M# into it might be causing a
conversion conflict because CODE wants a String but you're giving her a Date.
 
Back
Top