Building a SQL string (Access97/2k)

Topic

New Reply

I use this sql to do some monthly processing.
My procedure loops through the table names strTBL
and updates the DEL field.
strSQL = “UPDATE [” & strTBL & “] SET [” & strTBL & “].DEL = ‘N’ ” & _
“WHERE ((([” & strTBL & “].MINORPAC) Like ‘BALEL-TF’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘ACVS-10’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘FBA*’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘BJOHA*’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘BHRTZ-TF’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘AHRBL-10’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘RAM*’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘LUCTE-10’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘ANOMO-EDI’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘COUNI-10’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘STEMP-10’ ” & _
“Or ([” & strTBL & “].MINORPAC) Like ‘SHEPC*’))”
If I get a new account this month, how can I add the new account to the end of the Where clause without doing it manually. Would an array work or somehow loop through a table with all the account names?
What would be the best way?
Scott

Reply | Quote

Replies

I’d say put the account names in a table.
Then, open the table as a recordset and loop through it

edited to correct misplaced line

strWhere = “”
Do While Not rst.EOF
strWhere = strWHERE & “Or ([” & strTBL & “].MINORPAC) Like ‘” & rst!Acct & “*’ ”
rst.MoveNext
Loop
‘ Get rid of first “Or ”
If strWhere “” Then
strWhere = Mid(strWhere, 4)
End If
strSQL = “… WHERE ” & strWhere

Now, if you get a new account, just add it to the table. The SQL will pick it up automatically.

Reply | Quote

Hans,
Many thanks. Up and working.
Scott

Reply | Quote