Results 1 to 14 of 14
  1. #1
    Refereejohn is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Location
    Glasgow, Scotland
    Posts
    11

    Delete Names and Date of Birth - Insert Text

    I am looking for a code to delete a LastName, FirstName and Date of Birth in a rowe and insert "removed" in the field, can anyone help? I am using Microsoft Access 2010 in a Desktop Database.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Although not difficult to do using an update query, this sounds like a bad idea.
    Please explain why you want to do this as there may well be a better solution
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Refereejohn is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Location
    Glasgow, Scotland
    Posts
    11
    Quote Originally Posted by ridders52 View Post
    Although not difficult to do using an update query, this sounds like a bad idea.
    Please explain why you want to do this as there may well be a better solution
    Pray forgive my errant request, I should have said I wanted to remove these details on exiting the shared Database when the DateReturned Field =Date()+28, this is to remove the persons name and date of birth to conform to European Data Protection Regulations as I help to run a league. I can access these details through the players registration number. I would simply like to remove these personal details after a number of days. I have never used If Function, I did do a two day course on Command Button but this was too fast for me to keep up with the class. Hope this helps you Ridder?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm somewhat confused. The new GDPR law requires organisations to remove personal data unless 'clients' agree for this to continue to be stored.
    For players who are involved in a league and whose registration details are maintained for administration, consent can easily be obtained
    Also, if you can still access the name and date of birth using the players' registration numbers, why do you store the info somewhere else as well?
    It shouldn't matter about being >= 28 days. The data should only be stored once.

    Am I getting the wrong end of the stick here?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Refereejohn is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Location
    Glasgow, Scotland
    Posts
    11
    Yes, the way the administration runs some members send out letters and this is done by members who do not have access to the registration details. Once a set number of days is past I'd like to remove the information automatically that is all irrespective of the reason, besides it would help me in using VBA.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK - the update query below assumes you have a table called Members with fields LastName, FirstName, DOB & ReturnedDate

    It will replace the 2 name fields with 'Removed' but the DOB is a date field so it has to be changed to null
    The only records that will be changed are those where the returned date is exactly 28 days in the future (see previous post)
    I suspect you may want to modify this date+28 requirement as it doesn't make sense to me

    Code:
    UPDATE Members SET Members.LastName = 'Removed', Members.FirstName = 'Removed', Members.DOB = Null 
    WHERE (((Members.DateReturned)=Date()+28));
    Suggest you test it on a copy of your data to ensure it does what you actually want

    If you meant where DateReturned is 28 days or more in the past then:

    Code:
    UPDATE Members SET Members.LastName = 'Removed', Members.FirstName = 'Removed', Members.DOB = Null 
    WHERE (((Members.DateReturned)<=Date()-28));
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Refereejohn is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Location
    Glasgow, Scotland
    Posts
    11
    Quote Originally Posted by Refereejohn View Post
    Yes, the way the administration runs some members send out letters and this is done by members who do not have access to the registration details. Once a set number of days is past I'd like to remove the information automatically that is all irrespective of the reason, besides it would help me in using VBA.
    Thank you so much for your welcome reply. I was in London for a few days (great weather) now I am back in Glasgow and returning to my quest.
    I have inserted the code for the minus formula, your second one, into the Form I am using, at the conclusion I get the message: -

    "Expected: end of statement."

    Could be we are almost there? The double quotes above are mine.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I thought you wanted a query
    If you are using in form VBA, did you include line continuations?
    This works for me from a form:

    Code:
    Private Sub Command1_Click()    
        CurrentDb.Execute "UPDATE Members SET Members.LastName = 'Removed', Members.FirstName = 'Removed', Members.DOB = Null" & _
            " WHERE (((Members.DateReturned)<=Date()-28));"
            
        Me.Recalc
    End Sub
    The recalc line updates the display (and stays on the current record).
    Me.Requery would also work but go back to the first record if you have a single form
    Last edited by isladogs; 06-05-2018 at 04:45 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Refereejohn is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Location
    Glasgow, Scotland
    Posts
    11
    Quote Originally Posted by ridders52 View Post
    I thought you wanted a query
    If you are using in form VBA, did you include line continuations?
    This works for me from a form:

    Code:
    Private Sub Command1_Click()    
        CurrentDb.Execute "UPDATE Members SET Members.LastName = 'Removed', Members.FirstName = 'Removed', Members.DOB = Null" & _
            " WHERE (((Members.DateReturned)<=Date()-28));"
            
        Me.Recalc
    End Sub
    The recalc line updates the display (and stays on the current record).
    Me.Requery would also work but go back to the first record if you have a single form
    I have used the Code you sent me checking and re-checking my entry. at conclusion the De-Bug offers me, Compile error: Sub or Function not defined. I notice at the beginning of my module I have Private Sub Form_Close() as I would like the code to apply on exit. Could the Private sub be affecting the outcome?

    I have tried Me.Requery and Me.Recalc

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The word 'Private' isn't the issue. That just means the code can only be called from within the form.
    If you are trying to do this in the form close event, you don't need the recalc line as you don't want to see the update.
    However, doing it there may be the problem.

    As a test, move the code to a command button on the form. It should work as it does for me.
    If not, check your code compiles.
    Perhaps a reference issue (unlikely here ) or corruption?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Refereejohn is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Location
    Glasgow, Scotland
    Posts
    11
    Quote Originally Posted by ridders52 View Post
    The word 'Private' isn't the issue. That just means the code can only be called from within the form.
    If you are trying to do this in the form close event, you don't need the recalc line as you don't want to see the update.
    However, doing it there may be the problem.

    As a test, move the code to a command button on the form. It should work as it does for me.
    If not, check your code compiles.
    Perhaps a reference issue (unlikely here ) or corruption?
    Thank you so much for all your help for this Novice. I see a course is available at Oxford University (I attended the last one so if I choose to go this would be repitition) but I felt it was too fast for me [I am in my sixties] and it was all centered around a button.

    I cant find the Star to end this thread but great you were able to help me. Forgive delay as I travelled to London for Trooping the Colour and had a terrific day!

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    No problem John
    For info, I'm also in my sixties, taking up Access fairly late in life & have never done any training in Access - completely self taught.
    Its never too late to learn with both forums & you tube videos being particularly useful.
    There are 2 excellent video courses by Steve Bishop & Richard Rost that both cover everything from beginner to expert

    Glad you had a great day in London - much better than sitting in front of a computer all day

    To mark as solved, click the Thread Tools drop down - its bottom of the list.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    Refereejohn is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Location
    Glasgow, Scotland
    Posts
    11
    Thank you for swift reply Colin, I have purchased up to Expert 25 with Richard Rost and find his tutorials the best. I am currently on Expert 6 with Richard and suspended his sending tutorials until I catch up but I will return to Access Learning Zone, I treat this as a hobby therefore less stress. I see Richard has continued to create Developer lessons so I will get around to this - eventually.

    But for now I'll chip away at it...

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Steve Bishop's videos are free and I think at least as good but it's personal preference. Depends whose voice you find least irritating and whether you want to pay.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date of birth from ID No
    By Perfac in forum Programming
    Replies: 2
    Last Post: 01-17-2018, 03:10 PM
  2. get age from date to birth
    By earlcools in forum Queries
    Replies: 2
    Last Post: 01-27-2015, 07:03 PM
  3. How to change date of birth into text
    By Ayiramala in forum Access
    Replies: 13
    Last Post: 12-28-2014, 09:21 PM
  4. Calculate age from Date of Birth
    By djcmalvern in forum Programming
    Replies: 8
    Last Post: 05-03-2013, 06:18 AM
  5. Calculate age from birth date
    By mick in forum Forms
    Replies: 2
    Last Post: 07-03-2010, 04:31 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