Okay, but the interval would be "d", not "day". And missing closing ) and there's an extra " mark.
"WHERE ....... And DateDiff('d',[updated],#" & .column(1,i) & "#)=0"
Okay, but the interval would be "d", not "day". And missing closing ) and there's an extra " mark.
"WHERE ....... And DateDiff('d',[updated],#" & .column(1,i) & "#)=0"
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Programming late at night. Never sensible. Thanks June.
Hey Ssnafu ~
Thanks for joining in...
Okay, below is the RowSource for the ListBox
Also...The PK is the EmpID (not Auto, but system generated unique ID)Code:List1.RowSource = "SELECT tbl_Associates.AgentName, tbl_Associates.Updated, tbl_Associates.Approved " & _ "FROM tbl_Associates " & _ "WHERE (((tbl_Associates.Updated)>#5/10/2016#));"
All records are unique - no duplicates
And lastly, I took your advice and removed the spaces from the field names
Results are the same... code runs great, produces the correct results for the variables in the IWindow but doesn't update the 'Approved' column to Yes...
Andy & June Good morning (well at least it is morning here in California)
Okay... Below are the Debug lines. NOTE I am trying both date variations - with the Hashtag and without.
Neither seems to work in updating the Approved field with Yes...Code:Update tbl_Associates set Approved = "Yes" Where [AgentName] = Angel Johnson and [Updated] = 5/16/2016 UPDATE Tbl_Associates SET Approved = "Yes" WHERE [AgentName] = Angel Johnson And [Updated] = #5/16/2016# Update tbl_Associates set Approved = "Yes" Where [AgentName] = Chinecqua Walker and [Updated] = 5/11/2016 UPDATE Tbl_Associates SET Approved = "Yes" WHERE [AgentName] = Chinecqua Walker And [Updated] = #5/11/2016# Update tbl_Associates set Approved = "Yes" Where [AgentName] = Grace Bowman and [Updated] = 5/23/2016 UPDATE Tbl_Associates SET Approved = "Yes" WHERE [AgentName] = Grace Bowman And [Updated] = #5/23/2016#
The agent name in the updates query should have quotes round them
The correct one is the hashtag version
Use & agentname & to delimit the text.
Nearly there I think
Hey Andy ~
Okay, I put the delimiters around agentname and now I'm getting a "Variable Not Defined" @ agentname
Below is the new line
We gotta be getting close....Code:MySQL = "UPDATE Tbl_Associates SET Approved = ""Yes"" WHERE '" & AgentName & "' = " & .Column(0, i) & " And Updated = #" & .Column(1, i) & "#" Debug.Print MySQL
Thanks Andy for staying with me on this...
Think this should do it
Your delimiters were wrong.
We gotta be getting close....Code:MySQL = "UPDATE Tbl_Associates SET Approved = Yes" WHERE AgentName = & .Column(0, i) & " And Updated = #" & .Column(1, i) & "#" Debug.Print MySQL
The delimiters are the and which go around the text only.
I would use apostrophe delimiters for the 'Yes' value as well instead of doubled quotes just because I find it easier to read.
Names make very poor unique identifiers. Don't you have an AgentID field? Recommend you include it in the listbox and use in the filter criteria. It can be a hidden column.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Success!!!
Here is the final code which works perfectly... For future readers of this thread... This could not have been done without the help of Andy49; June7 & ssnafu!!
Thank You ~ All of you!!
Code:Private Sub Command0_Click() Dim i As Integer Dim MySQL As String With [Forms]![Form1]!List1 For i = 0 To .ListCount - 1 If .Selected(i) Then MySQL = "Update tbl_Associates set Approved = ""Yes"" Where AgentName = '" & .Column(0, i) & "' and Updated = #" & .Column(1, i) & "#" CurrentDb.Execute MySQL 'Debug.Print MySQL End If Next I End With End Sub
No problem. Id agree with others though. Could you adjust this to have people being searched by a unique id number. Much more efficient.
Absolutely, Andy... Consider it done... And I agree as well, always better to have a single condition in the WHERE rather than multiple conditions...
Thanks again, Andy...