Work Around for Like '*active*'

  • Thread starter Thread starter TrickDownie
  • Start date Start date
T

TrickDownie

I decided to try and Loop through the 4 results from my downloads table
and grap the Record Like '*active*'.

However, when I run the code I get the Run-time error '3021'

Code:
'UPDATE START DATETIME
Private Sub Command12_Click()

Dim SQLString As String
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim cmd2 As New ADODB.Command
Dim cmd3 As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim rst3 As New ADODB.Recordset

DoCmd.SetWarnings False

Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn
Set cmd2.ActiveConnection = cnn
Set cmd3.ActiveConnection = cnn

SQLString = "SELECT * "
SQLString = SQLString & "FROM jobs "
SQLString = SQLString & "WHERE
(((jobs.active_jobs_msglog_crtdt) Is Null));"

' MsgBox (SQLString)

cmd.CommandText = SQLString

rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenKeyset, adLockOptimistic
' MsgBox (rst!active_jobs_msglog_crtdt & ", " & rst!Branch_Number)

If rst.RecordCount > 0 Then
Do While Not rst.EOF
' MsgBox (rst!active_jobs_msglog_crtdt & ", " & rst!Item_id)

SQLString = "SELECT downloads.Branch_Number,
downloads.msglog_text, downloads.item_id, Min(downloads.msglog_crtdt)
AS MinOfmsglog_crtdt "
SQLString = SQLString & "FROM downloads "
SQLString = SQLString & "GROUP BY
downloads.Branch_Number, downloads.msglog_text, downloads.item_id "
SQLString = SQLString & "HAVING
(((downloads.Branch_Number)=" & rst!Branch_Number & ") AND
((downloads.item_id) = " & rst!item_id & "))"

cmd2.CommandText = SQLString

rst2.CursorLocation = adUseClient
rst2.Open cmd2, , adOpenKeyset, adLockOptimistic

'MsgBox (rst2.GetString)

If rst2.RecordCount > 0 Then
Do While Not rst.EOF
SQLString = "SELECT
downloads.Branch_Number, downloads.msglog_text, downloads.item_id,
downloads.msglog_crtdt "
SQLString = SQLString & "FROM downloads "
SQLString = SQLString & "WHERE
(((downloads.Branch_Number)= " & rst!Branch_Number & ") AND
((downloads.item_id)= " & rst!item_id & ")) AND
((downloads.msglog_crtdt) Like " & """" & "*" & rst2!MinOfmsglog_crtdt
& "*" & """" & ");"

cmd3.CommandText = SQLString

rst3.CursorLocation = adUseClient
rst3.Open cmd3, , adOpenKeyset,
adLockOptimistic
MsgBox (rst3.GetString)

Loop
End If
rst3.MoveNext
rst3.Close
Loop
End If
DoCmd.SetWarnings True
End Sub
 
You're using ADO. The wildcard character in ADO is %, not *.

See if fixing that makes the problem go away.
 
I decided to try and Loop through the 4 results from my downloads table
and grap the Record Like '*active*'.
Click to expand...

Isn't one thread on this subject enough? Isn't SusanV helping you?
 
Doh! MIssed that - thanks Doug!

Douglas J Steele said:
You're using ADO. The wildcard character in ADO is %, not *.

See if fixing that makes the problem go away.
Click to expand...
 
Don't know that he/she showed that it was ADO in all of the various threads
he/she posted!
 
Well, it *was* in the code posted in the really long thread we've been
working.

My bad, nice catch on your part
 
Back
Top