"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "

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

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


wrote in message
oups.com...
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

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"

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*')


wrote in message
oups.com...
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"

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

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






wrote in message
oups.com...
Ohhh, single quotes - got ya!
Ok, I just tried and it still did not work? Hmmm?

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

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

;-)


wrote in message
ups.com...
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

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));

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

wrote in message
oups.com...
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));