Work Around for Like '*active*'

[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

 

[Douglas J Steele]

You're using ADO. The wildcard character in ADO is %, not *.

See if fixing that makes the problem go away.

 

[Keith Wilby]

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

Isn't one thread on this subject enough? Isn't SusanV helping you?

 

[SusanV]

Doh! MIssed that - thanks Doug!

You're using ADO. The wildcard character in ADO is %, not *.

See if fixing that makes the problem go away.

 

[Douglas J Steele]

Don't know that he/she showed that it was ADO in all of the various threads
he/she posted!

 

[SusanV]

Well, it *was* in the code posted in the really long thread we've been
working.

My bad, nice catch on your part