RE: Programming Loop with SQL Update Query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

John C Brainard wrote:
>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
>>
>[quoted text clipped - 59 lines]
>> >
>> > End Function

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200510/1
.

Relevant Pages

  • Re: Cbool problem
    ... >I built up a string in code and now I wish to evaluate it ... > using Cbool but I get a runtime error. ... For compiled programming languages it's not that easy. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Automate import .csv into table
    ... >What does that string look like? ... >> i followed the basic instructions in the database help ... >> when i run the code i come up with a runtime error ... >> is not an EXCEL ...
    (microsoft.public.access.externaldata)
  • Re: Help SQL filter
    ... > Runtime Error '3001' Arguments are of the wrong type, ... Is this DAO or ADO? ... Because ADO does not support multiple criteria (i.e., ... build a string with your criteria ...
    (microsoft.public.vb.general.discussion)
  • RE: Check function parameters for null Values
    ... expected (normally generates Runtime Error 94). ... > The following example in mytest1 worksbut requires hard coding the controls ... I really need to specify parameters like in the second example ... > Function mytest2(a As String, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Date format conversion
    ... work for me (runtime error 13). ... The string I am wanting to convert is in yyyymmdd format eg 20070511 ...
    (microsoft.public.access.forms)