Having Like

T

TrickDownie

I have a table(Jobs) that I am trying update the start DateTime field
with Active values in another table(Downloads).

When I run the code I get teh Run-Time Error 3021 "Either BOF or EOF is
True, ....
****See below alternative

'UPDATE START DATES
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 rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

DoCmd.SetWarnings False

Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn
Set cmd2.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


If rst.RecordCount > 0 Then
Do While Not rst.EOF


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.msglog_text) Like " & """" & "*"
& "active" & "*" & """" & ") AND ((downloads.item_id)= " & rst!item_id
& "));"

******IF I REMOVED THE ((downloads.msglog_text) Like " & """" & "*" &
active & "*" & """" & ") FROM THIS SQLStirng I get the 4 records from
the downloads table[Active, Launched, Waiting, Completed] when I only
want to rst2 the Active record

cmd2.CommandText = SQLString

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

MsgBox (rst2.GetString)

If rst2.RecordCount > 0 Then

SQLString = "UPDATE jobs SET
jobs.active_jobs_msglog_crtdt = (" & rst2!MinOfmsglog_crtdt & ") "
SQLString = SQLString & "WHERE (((jobs.qrt)
= " & rst!qrt & ") AND ((jobs.year) = " & rst!Year & ") AND
((jobs.Branch_Number) = " & rst!Branch_Number & ") AND ((jobs.item_id)
= " & rst!item_id & "));"
DoCmd.RunSQL (SQLString)


End If
rst.MoveNext
rst2.Close
Loop
End If
DoCmd.SetWarnings True
End Sub
 
S

SusanV

If you put your criteria in the immediate window - ?
"((downloads.msglog_text) Like " & """" & "*" & active & "*" & """" & ") " -
you will see that it isn't parsing out quite the way you would expect, it's
returning - ((downloads.msglog_text) Like "**")

I believe what you are looking for is the criteria Like "*active*" - no?
Why do you have all this concatenation? Use single quotes inside your
doubles to differentiate. For example

strSQL = "Select * from table where field like '*active*'"

would return
Select * from table where field like '*active*'
--
hth,
SusanV


I have a table(Jobs) that I am trying update the start DateTime field
with Active values in another table(Downloads).

When I run the code I get teh Run-Time Error 3021 "Either BOF or EOF is
True, ....
****See below alternative

'UPDATE START DATES
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 rst As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset

DoCmd.SetWarnings False

Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn
Set cmd2.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


If rst.RecordCount > 0 Then
Do While Not rst.EOF


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.msglog_text) Like " & """" & "*"
& "active" & "*" & """" & ") AND ((downloads.item_id)= " & rst!item_id
& "));"

******IF I REMOVED THE ((downloads.msglog_text) Like " & """" & "*" &
active & "*" & """" & ") FROM THIS SQLStirng I get the 4 records from
the downloads table[Active, Launched, Waiting, Completed] when I only
want to rst2 the Active record

cmd2.CommandText = SQLString

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

MsgBox (rst2.GetString)

If rst2.RecordCount > 0 Then

SQLString = "UPDATE jobs SET
jobs.active_jobs_msglog_crtdt = (" & rst2!MinOfmsglog_crtdt & ") "
SQLString = SQLString & "WHERE (((jobs.qrt)
= " & rst!qrt & ") AND ((jobs.year) = " & rst!Year & ") AND
((jobs.Branch_Number) = " & rst!Branch_Number & ") AND ((jobs.item_id)
= " & rst!item_id & "));"
DoCmd.RunSQL (SQLString)


End If
rst.MoveNext
rst2.Close
Loop
End If
DoCmd.SetWarnings True
End Sub
Click to expand...
 
T

TrickDownie

Thanks for the reply

When I enter ((downloads.msglog_text) Like "*active*") it now dispalys
with some spacing;
((downloads.msglog_text) Like " * active * ")

Now when I run this I see a compile error
"Variable not defined"
 
S

SusanV

Try using singe quotes instead of doubles. As far as VBA is concerned, using
the double quote is ending the string.
((downloads.msglog_text) Like '*active*')
 
T

TrickDownie

Ohhh, single quotes - got ya!
Ok, I just tried and it still did not work? Hmmm?
 
S

SusanV

You're using this to populate a variable - SQLString if I remember
correctly?

In the VBE open the immediate window (CTRL + G) and after populating the
variable but before docmd.runsql insert the line:

debug.print SQLString

Now run the code and in the immediate window you can see (and copy) the
resulting text (actual string the variable is holding).

If you can't quite see what looks wrong, you can copy this text then paste
it into a new query (SQL View) you can then take a look at the new query in
Design View to see what looks kaflooey. (I use this a lot when dealing with
long or complex field names - good way to check for typos)

Another tip - instead of using concatenation -
string = "this much stuff "
string = string & "I need more stuff"
string = string & "And this stuff too"

You can use the underscore to continue to the next line - makes debugging
much easier:

string = "this much stuff " _
& "I need more stuff" _
& "And this stuff too"


I cleaned up your code a bit:

sqlstring = "SELECT downloads.Branch_Number, downloads.msglog_text,
" _
& "downloads.item_id, Min(downloads.msglog_crtdt)" _
& "AS MinOfmsglog_crtdt " _
& "FROM downloads " _
& "GROUP BY downloads.Branch_Number, " _
& "downloads.msglog_text, downloads.item_id " _
& "HAVING (((downloads.Branch_Number)= " _
& rst!Branch_Number _
& ") AND ((downloads.msglog_text) Like '*active*')" _
& "AND ((downloads.item_id)= " _
& rst!item_id _
& "));"

Debug.Print sqlstring

Give that a go. ;-)

SusanV
 
T

TrickDownie

Wow, thank you for all of you help. However, when I try and run the
above code I get syntax errors. It appears to be automatically
inserting double quotatinos at the end of line 1?

If rst.RecordCount > 0 Then
Do While Not rst.EOF

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


Debug.Print SQLString

cmd2.CommandText = SQLString

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

SusanV

Trick,

Can you copy the SQLString value from the immediate window? I do see an
error - missing a space at the end of the a couple of lines before the
quote. Let's try again:

sqlstring = "SELECT downloads.Branch_Number, downloads.msglog_text, " _
& "downloads.item_id, Min(downloads.msglog_crtdt) " _
& "AS MinOfmsglog_crtdt " _
& "FROM downloads " _
& "GROUP BY downloads.Branch_Number, " _
& "downloads.msglog_text, downloads.item_id " _
& "HAVING (((downloads.Branch_Number) = " _
& rst!Branch_Number _
& ") AND ((downloads.msglog_text) Like '*active*') " _
& "AND ((downloads.item_id) = " _
& rst!item_id _
& "));"

Debug.Print sqlstring

If that doesn't run, paste the result so we can get this fixed

;-)
 
T

TrickDownie

Alright, here are the results!

SELECT downloads.Branch_Number, downloads.msglog_text,
downloads.item_id, Min(downloads.msglog_crtdt) AS MinOfmsglog_crtdt
FROM downloads GROUP BY downloads.Branch_Number, downloads.msglog_text,
downloads.item_id HAVING (((downloads.Branch_Number) = 11) AND
((downloads.msglog_text) Like '*active*') AND ((downloads.item_id) =
1));
SELECT downloads.Branch_Number, downloads.msglog_text,
downloads.item_id, Min(downloads.msglog_crtdt) AS MinOfmsglog_crtdt
FROM downloads GROUP BY downloads.Branch_Number, downloads.msglog_text,
downloads.item_id HAVING (((downloads.Branch_Number) = 11) AND
((downloads.msglog_text) Like '*active*') AND ((downloads.item_id) =
1));
 
S

SusanV

What error are you getting? If you paste those results into a new query in
SQL view, does it run?
 
T

TrickDownie

I am seeing the Run-time Error 3021

The immediate window displays

SELECT downloads.Branch_Number, downloads.msglog_text,
downloads.item_id, Min(downloads.msglog_crtdt) AS MinOfmsglog_crtdt
FROM downloads GROUP BY downloads.Branch_Number, downloads.msglog_text,
downloads.item_id HAVING (((downloads.Branch_Number) = 11) AND
((downloads.msglog_text) Like '*active*') AND ((downloads.item_id) =
1));

If I click debug button it highlights MsgBox (rst2.GetString)
 
S

SusanV

Oh, comment that out - or if you need it, get rid of the parenthesis -
parens are used when you're passing to a value:
msgbox rst2.GetString

MyString = msgbox(rst2.GetString)
 
S

SusanV

Also - I totally missed this - Doug Steele pointed out that your wildcard
should be a % not a * - ADO doesn't accept the asterisk.
 
D

Douglas J Steele

<picky>
ADO accepts it. It just doesn't treat it as a wildcard character. <g>
</picky>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Public Sub to run Private Function 1
Work Around for Like '*active*' 5
VBA can't bring back Like '*active*' records 1
Login Code to Open BRANCHES FORM 4
Copy and create recordset 2
User requests password in email. 4
LOGIN email passwords 4
!AddNew doesn't. ...cannot insert NULL 'rowguid' 3

Top