If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"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 |
#2
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
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 |
#3
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
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" |
#4
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
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" |
#5
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
Ohhh, single quotes - got ya!
Ok, I just tried and it still did not work? Hmmm? |
#6
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
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? |
#7
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
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 |
#8
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
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 |
#9
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
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)); |
#10
|
|||
|
|||
"HAVING LIKE " & """" & "*" 'Active" & "*" & """" & "
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)); |
|
Thread Tools | |
Display Modes | |
|
|