Results 1 to 15 of 15
  1. #1
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295

    Deleting specifice cloumns in recordsd

    Good Morning All.



    I have tried to delete fields in recorded by the following statement:
    Code:
     rstInstructorsAllocations.Fields(F) = ""
    However this does not work because some of the datatypes Date/time; hence I temporary convert these fields to text datatype, then enter "", then change back to date/time datatype. This has been working fine for years until today when I split the database to use it over a network.

    The error message states that this procedure cannot occur over linked data sources.

    I would be very grateful if someone can give advise on the solution to this problem.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Maybe
    rstInstructorsAllocations.Fields(F) = Empty
    or
    rstInstructorsAllocations.Fields(F) = Null

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    try =vbnull as well

  4. #4
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Thanks. Unfortunately none of the responses actually leave the cell blank. The values remain.

    Looking for more suggestions.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Are you trying to DELETE the Column or Delete a value from a Column(s)?

    You can delete/remove the column with Alter Table SQL
    General syntax:
    Code:
    ALTER TABLE table_name
    DROP COLUMN column_name

  6. #6
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    I am trying to delete the values in the fields and not the column.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    A Delete query (in SQL) will delete the record, not just the value.

    If you want to delete the value of a field in a record, then you should do an Update query.

    For example:

    Suppose you have a record in tblCompany, where

    CompanyID =200
    CompanyName = Gunz-R-Us
    CompanyAliasName= 12345Limited

    and you want to "delete" the CompanyAliasName.

    Code:
    Update tblCompany
    Set CompanyAliasName = ""
    Where CompanyID = 200;
    I always suggest you do a select query first --to make sure you have the correct record(s) to be changed.

    Code:
    SELECT * from tblCompany where CompanyID = 200

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    are you encapsulating the value in a

    rst.edit
    --- set value here
    rst.update

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by George View Post
    ...hence I temporary convert these fields to text datatype...
    Perhaps not doing this would be beneficial.

  10. #10
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Yes, I am doing an update query; however, apparently date/time datatype fields do not accept "" entries. So whereas CompanyID = 200 would work fine, siginTime = 9:00 cannot be changed by signinTime = ""

  11. #11
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    yes I am encapsulating the value as described

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Some sample data

    table_name field_name data_type length
    AircraftRaw RecDate Date 8
    AircraftRaw ID Long 4
    AircraftRaw fld Text 19

    Rec Date ID fld
    08-Sep-2016 2 Aircraft N3456

    3
    12-Sep-2012 4 456723-101 FD34566
    14-Sep-2015 5 566788-303 GS23445

    6
    01-Sep-2016 7 Aircraft N5684

    8
    06-Sep-2013 9 566788-303 GS23445
    21-Sep-2012 10 456723-101 GD23478
    29-Sep-2013 11 566788-303 GS45444
    20-Sep-2009 12 434545-444 JI345345

    Select query before doing any update

    Code:
    SELECT AircraftRaw.RecDate, AircraftRaw.ID, AircraftRaw.fld, Year([recdate]) AS Expr1
    FROM AircraftRaw
    WHERE (((Year([recdate]))=2016));
    Code:
    Rec Date ID fld Expr1
    08-Sep-2016 2 Aircraft N3456 2016
    01-Sep-2016 7 Aircraft N5684 2016
    The update query
    Code:
    UPDATE AircraftRaw SET AircraftRaw.RecDate = Null
    WHERE (((Year([recdate]))=2016));
    The result:
    Rec Date ID fld

    2 Aircraft N3456

    3
    12-Sep-2012 4 456723-101 FD34566
    14-Sep-2015 5 566788-303 GS23445

    6

    7 Aircraft N5684

    8
    06-Sep-2013 9 566788-303 GS23445
    21-Sep-2012 10 456723-101 GD23478
    29-Sep-2013 11 566788-303 GS45444
    20-Sep-2009 12 434545-444 JI345345

    I hope this is helpful.

  13. #13
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    I understand your illustration but mine context is some what different, so it is not working. Maybe I show the code:

    HTML Code:
    ' This procedure will clear all the cells in the InstructorAllocations table
     
    Private Sub Command0_Click()
    Dim curDatabase As Database
        
        Set curDatabase = CurrentDb
           
        Dim rstInstructorsAllocations As Object
        Dim F As Integer
        Dim t As Integer
        Dim p As Integer
        F = 8   'Field number or position. 0 is the 1st position
        p = 1   'Field name in "InstructorsAllocations" table
        
        
      ' Because the fields with data type Date/time do not accept the null value "", the following will
      ' Convert columns with type Date/time to text so that it can accept the null value "".
      ' On completion the fields must be converted back to the original Date/time data type.
      
      'curDatabase.Execute ("ALTER TABLE UniqueCoursesUnderClasses ALTER COLUMN " & p & " TEXT") 'Change data type to text
       
      Set rstInstructorsAllocations = curDatabase.OpenRecordset("UniqueCoursesUnderClasses")
              
    t = rstInstructorsAllocations.RecordCount
    Dim j, i As Integer
     
         For j = 1 To 15
             For i = 1 To t
        
                    rstInstructorsAllocations.Edit
                   
                  rstInstructorsAllocations.Fields(F) = Null    ' Set fields to null
                    
                     rstInstructorsAllocations.Update
                    rstInstructorsAllocations.MoveNext
                
             Next i
            
                    ' Change data type back to date type
                         Set rstInstructorsAllocations = Nothing
                         'curDatabase.Execute ("ALTER TABLE UniqueCoursesUnderClasses ALTER COLUMN " & p & " DATETIME")
                         F = F + 1
                         p = p + 1
                         'curDatabase.Execute ("ALTER TABLE UniqueCoursesUnderClasses ALTER COLUMN " & p & " TEXT")
                    
                         Set rstInstructorsAllocations = curDatabase.OpenRecordset("UniqueCoursesUnderClasses")
                       
        Next j
        Set curDatabase = Nothing
         
         MsgBox "Successfully completed"
         
         
    End Sub
    Last edited by George; 09-17-2016 at 02:32 PM. Reason: append a comment

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    the fields with data type Date/time do not accept the null value ""
    That is a zero length string, not NULL.

    If you know which records should have the values changed, you can set that up as a where clause.
    My sample dealt with a Date/Time datatype.

  15. #15
    George is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2012
    Posts
    295
    Actually Null does work but my problem laid with a programming oversight:



    I discovered that my code was reading only the first record and updating that one, but I did not put data in that record to be updated, so it appeared like nothing was happening. It just did not read past the first to the other records with the data.

    The general problem is that I should have used the moveLast and MoveFirst properties in order for all the records to be counted.

    Thank you all so much

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

Similar Threads

  1. Deleting query
    By drunkenneo in forum Programming
    Replies: 3
    Last Post: 07-05-2013, 08:06 AM
  2. Deleting rows
    By ads8525 in forum Access
    Replies: 13
    Last Post: 02-10-2012, 08:52 AM
  3. Replies: 11
    Last Post: 12-14-2010, 01:25 PM
  4. deleting records
    By radicalrik in forum Queries
    Replies: 2
    Last Post: 07-14-2010, 03:10 PM
  5. Deleting certain data
    By swindmiller in forum Queries
    Replies: 3
    Last Post: 02-17-2010, 10:39 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