Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822

    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.

  2. #17
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Programming late at night. Never sensible. Thanks June.

  3. #18
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Ssnafu ~

    Thanks for joining in...

    Okay, below is the RowSource for the ListBox
    Code:
    List1.RowSource = "SELECT tbl_Associates.AgentName, tbl_Associates.Updated, tbl_Associates.Approved " & _
    "FROM tbl_Associates " & _
    "WHERE (((tbl_Associates.Updated)>#5/10/2016#));"
    Also...The PK is the EmpID (not Auto, but system generated unique ID)
    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...

  4. #19
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    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.

    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#
    Neither seems to work in updating the Approved field with Yes...

  5. #20
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Trying to loop through selected items in a listbox to update an access table

    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

  6. #21
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Hey Andy ~

    Okay, I put the delimiters around agentname and now I'm getting a "Variable Not Defined" @ agentname

    Below is the new line
    Code:
    MySQL = "UPDATE Tbl_Associates SET Approved = ""Yes"" WHERE '" & AgentName & "' = " & .Column(0, i) & " And Updated = #" & .Column(1, i) & "#"
    Debug.Print MySQL
    We gotta be getting close....

    Thanks Andy for staying with me on this...

  7. #22
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Trying to loop through selected items in a listbox to update an access table

    Think this should do it

    Your delimiters were wrong.
    Code:
    MySQL = "UPDATE Tbl_Associates SET Approved = “”Yes"” WHERE AgentName = ‘“  & .Column(0, i) & "’ And Updated = #" & .Column(1, i) & "#"
    Debug.Print MySQL
    We gotta be getting close....

    The delimiters are the ‘ and ‘ which go around the text only.

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.

  9. #24
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    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

  10. #25
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    No problem. I’d agree with others though. Could you adjust this to have people being searched by a unique id number. Much more efficient.

  11. #26
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    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...

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 16
    Last Post: 03-31-2020, 10:19 AM
  2. Replies: 4
    Last Post: 07-26-2016, 02:53 AM
  3. Replies: 2
    Last Post: 03-27-2015, 08:48 AM
  4. Loop trough listbox to update table
    By mari_hitz in forum Programming
    Replies: 4
    Last Post: 04-21-2014, 07:16 AM
  5. Selected items in listbox
    By tomodachi in forum Access
    Replies: 1
    Last Post: 09-09-2010, 01:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums