Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Delete only specific field in record to Null

Discussion in 'Information Technology' started by Gina, Jul 28, 2006.

  1. Gina

    Gina
    Expand Collapse
    Guest

    All,

    In the following SQL script I am trying to blank out the fields, but so far
    it isn't working. Can you point me in the right direction.

    Thanks!

    'Setup the table and recordset to be edited

    ' strTablename = "Billing"

    Set db = CurrentDb

    Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)



    For Each i In Me![lstItems].ItemsSelected 'Determine the number of items
    selected in the list

    Criteria = Me![lstItems].ItemData(i) 'Assign the Customer Number a
    variable



    With rst

    .MoveFirst

    .FindFirst "[ID] = " & Criteria & "" 'Locate a Customer Number that
    matches the one selected from the list

    .Edit

    .Fields("Assigned") = 0 'Uncheck the checkbox

    .Fields("AssignedTo") = "" 'Clear the person's name from the record

    .Fields("DateAssigned").Value = "" 'Clear the Assign date

    .Update

    End With

    rst.Close

    Next i 'Loop through the next record

    Set db = Nothing 'Unset the Database

    Set rst = Nothing 'Unset the recordset



    Me.lstItems.Requery

    Exit Sub


    --
    Gina
     
  2. Loading...


  3. Gina

    Gina
    Expand Collapse
    Guest

    Sorry, only the date field doesn't work.
    --
    Gina


    "Gina" wrote:

    > All,
    >
    > In the following SQL script I am trying to blank out the fields, but so far
    > it isn't working. Can you point me in the right direction.
    >
    > Thanks!
    >
    > 'Setup the table and recordset to be edited
    >
    > ' strTablename = "Billing"
    >
    > Set db = CurrentDb
    >
    > Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)
    >
    >
    >
    > For Each i In Me![lstItems].ItemsSelected 'Determine the number of items
    > selected in the list
    >
    > Criteria = Me![lstItems].ItemData(i) 'Assign the Customer Number a
    > variable
    >
    >
    >
    > With rst
    >
    > .MoveFirst
    >
    > .FindFirst "[ID] = " & Criteria & "" 'Locate a Customer Number that
    > matches the one selected from the list
    >
    > .Edit
    >
    > .Fields("Assigned") = 0 'Uncheck the checkbox
    >
    > .Fields("AssignedTo") = "" 'Clear the person's name from the record
    >
    > .Fields("DateAssigned").Value = "" 'Clear the Assign date
    >
    > .Update
    >
    > End With
    >
    > rst.Close
    >
    > Next i 'Loop through the next record
    >
    > Set db = Nothing 'Unset the Database
    >
    > Set rst = Nothing 'Unset the recordset
    >
    >
    >
    > Me.lstItems.Requery
    >
    > Exit Sub
    >
    >
    > --
    > Gina
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Gina

    It would REALLY help if you told us in what way it is not working - what
    error is being raised (if any) and on what line of code, and if no error is
    being raised then what is happening or not happening that is not as you
    expect.

    However, I can take a guess that the problem is with one or both of these
    lines:

    .Fields("AssignedTo") = "" 'Clear the person's name from the record
    .Fields("DateAssigned").Value = "" 'Clear the Assign date

    You are setting both these fields to an empty string. I assume DateAssigned
    is a date/time field so you can't set it to a string (which is text).
    Further, the AllowZeroLength property of the AssignedTo field is probably No
    (the default) so you can't set it to an empty string either.

    Usually when a field looks blank, its value is not an empty string, but
    Null.

    Try replacing both instances of /""/ above with /Null/ (without the slashes
    of course).

    By the way, you called this "SQL script" but there is not an ounce of SQL in
    it. It is actually VBA code (Visual Basic for Applications).

    However, you might find that SQL is a better way to go. In your VBA code
    you can construct a SQL command string to perform the update, and then
    execute that SQL string.

    The first step is to build a criteria string to identify which records to
    update, then build a command string that looks like this:

    Update Billing (fld1, fld2, fld3) values (val1, val2, val3) where ID in
    (id1, id2, ...);

    Try this:

    Dim sSql as string, sIdList as String, vItem as variant
    With Me!lstItems
    For Each vItem in .ItemsSelected
    sIdList = sIdList & .ItemData(vItem) & ","
    Next vItem
    End With
    ' we now have a string like this: "234, 345, 456,"
    ' remove the last comma
    sIdList = Left(sIdList , Len(sIdList )-1)
    ' now add the rest of the SQL string
    sSql = "Update Billing (Assigned, AssignedTo, DateAssigned) " _
    & "values (0, Null, Null) where ID in ( " & sIdList & ");"
    ' now execute the SQL
    CurrentDb.Execute sSql, dbFailOnError


    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:0CD24052-8BF2-45FD-A569-3C1A1E23F0CE@microsoft.com...
    > All,
    >
    > In the following SQL script I am trying to blank out the fields, but so
    > far
    > it isn't working. Can you point me in the right direction.
    >
    > Thanks!
    >
    > 'Setup the table and recordset to be edited
    >
    > ' strTablename = "Billing"
    >
    > Set db = CurrentDb
    >
    > Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)
    >
    >
    >
    > For Each i In Me![lstItems].ItemsSelected 'Determine the number of
    > items
    > selected in the list
    >
    > Criteria = Me![lstItems].ItemData(i) 'Assign the Customer Number a
    > variable
    >
    >
    >
    > With rst
    >
    > .MoveFirst
    >
    > .FindFirst "[ID] = " & Criteria & "" 'Locate a Customer Number that
    > matches the one selected from the list
    >
    > .Edit
    >
    > .Fields("Assigned") = 0 'Uncheck the checkbox
    >
    > .Fields("AssignedTo") = "" 'Clear the person's name from the record
    >
    > .Fields("DateAssigned").Value = "" 'Clear the Assign date
    >
    > .Update
    >
    > End With
    >
    > rst.Close
    >
    > Next i 'Loop through the next record
    >
    > Set db = Nothing 'Unset the Database
    >
    > Set rst = Nothing 'Unset the recordset
    >
    >
    >
    > Me.lstItems.Requery
    >
    > Exit Sub
    >
    >
    > --
    > Gina
     
  5. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Gina:

    Fields of date/Time data type can only be a valid date/time value or Null,
    not a zero length string so try making it Null:

    .Fields("DateAssigned") = Null 'Clear the Assign date

    I would suggest that you do the same with the AssignedTo field too rather
    than setting its value to a zero-length string, unless it's Required property
    is True in the table design. Also, rather than setting the value of the
    Boolean (Yes/No) Assigned field to 0, set it to False:

    .Fields("Assigned") = False 'Uncheck the checkbox

    It will work in the same way, but its not considered good programming
    practice to rely on the implementation, in this case of Boolean values as 0
    or -1, but instead to use the Boolean False and True constants.

    Ken Sheridan
    Stafford, England

    "Gina" wrote:

    > Sorry, only the date field doesn't work.
    > --
    > Gina
    >
    >
    > "Gina" wrote:
    >
    > > All,
    > >
    > > In the following SQL script I am trying to blank out the fields, but so far
    > > it isn't working. Can you point me in the right direction.
    > >
    > > Thanks!
    > >
    > > 'Setup the table and recordset to be edited
    > >
    > > ' strTablename = "Billing"
    > >
    > > Set db = CurrentDb
    > >
    > > Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)
    > >
    > >
    > >
    > > For Each i In Me![lstItems].ItemsSelected 'Determine the number of items
    > > selected in the list
    > >
    > > Criteria = Me![lstItems].ItemData(i) 'Assign the Customer Number a
    > > variable
    > >
    > >
    > >
    > > With rst
    > >
    > > .MoveFirst
    > >
    > > .FindFirst "[ID] = " & Criteria & "" 'Locate a Customer Number that
    > > matches the one selected from the list
    > >
    > > .Edit
    > >
    > > .Fields("Assigned") = 0 'Uncheck the checkbox
    > >
    > > .Fields("AssignedTo") = "" 'Clear the person's name from the record
    > >
    > > .Fields("DateAssigned").Value = "" 'Clear the Assign date
    > >
    > > .Update
    > >
    > > End With
    > >
    > > rst.Close
    > >
    > > Next i 'Loop through the next record
    > >
    > > Set db = Nothing 'Unset the Database
    > >
    > > Set rst = Nothing 'Unset the recordset
    > >
    > >
    > >
    > > Me.lstItems.Requery
    > >
    > > Exit Sub
    > >
    > >
    > > --
    > > Gina
     
  6. Gina

    Gina
    Expand Collapse
    Guest

    It worked.

    Thanks to both of you.

    I apologize for sounding uninformed, for that is what it was. I was
    submitting this for a co-worker. I also suggested the 'null' value, however,
    it sometimes takes the experts like yourselves to say it.

    As for SQL vs. VBA, You're right, he only writes VBA, I'm used to working
    with SQL - so out of habit I stated that.

    Again, thanks for your expert support as always.
    --
    Gina


    "Graham Mandeno" wrote:

    > Hi Gina
    >
    > It would REALLY help if you told us in what way it is not working - what
    > error is being raised (if any) and on what line of code, and if no error is
    > being raised then what is happening or not happening that is not as you
    > expect.
    >
    > However, I can take a guess that the problem is with one or both of these
    > lines:
    >
    > .Fields("AssignedTo") = "" 'Clear the person's name from the record
    > .Fields("DateAssigned").Value = "" 'Clear the Assign date
    >
    > You are setting both these fields to an empty string. I assume DateAssigned
    > is a date/time field so you can't set it to a string (which is text).
    > Further, the AllowZeroLength property of the AssignedTo field is probably No
    > (the default) so you can't set it to an empty string either.
    >
    > Usually when a field looks blank, its value is not an empty string, but
    > Null.
    >
    > Try replacing both instances of /""/ above with /Null/ (without the slashes
    > of course).
    >
    > By the way, you called this "SQL script" but there is not an ounce of SQL in
    > it. It is actually VBA code (Visual Basic for Applications).
    >
    > However, you might find that SQL is a better way to go. In your VBA code
    > you can construct a SQL command string to perform the update, and then
    > execute that SQL string.
    >
    > The first step is to build a criteria string to identify which records to
    > update, then build a command string that looks like this:
    >
    > Update Billing (fld1, fld2, fld3) values (val1, val2, val3) where ID in
    > (id1, id2, ...);
    >
    > Try this:
    >
    > Dim sSql as string, sIdList as String, vItem as variant
    > With Me!lstItems
    > For Each vItem in .ItemsSelected
    > sIdList = sIdList & .ItemData(vItem) & ","
    > Next vItem
    > End With
    > ' we now have a string like this: "234, 345, 456,"
    > ' remove the last comma
    > sIdList = Left(sIdList , Len(sIdList )-1)
    > ' now add the rest of the SQL string
    > sSql = "Update Billing (Assigned, AssignedTo, DateAssigned) " _
    > & "values (0, Null, Null) where ID in ( " & sIdList & ");"
    > ' now execute the SQL
    > CurrentDb.Execute sSql, dbFailOnError
    >
    >
    > --
    > Good Luck!
    >
    > Graham Mandeno [Access MVP]
    > Auckland, New Zealand
    >
    > "Gina" <Gina@discussions.microsoft.com> wrote in message
    > news:0CD24052-8BF2-45FD-A569-3C1A1E23F0CE@microsoft.com...
    > > All,
    > >
    > > In the following SQL script I am trying to blank out the fields, but so
    > > far
    > > it isn't working. Can you point me in the right direction.
    > >
    > > Thanks!
    > >
    > > 'Setup the table and recordset to be edited
    > >
    > > ' strTablename = "Billing"
    > >
    > > Set db = CurrentDb
    > >
    > > Set rst = db.OpenRecordset(strTableName, dbOpenDynaset)
    > >
    > >
    > >
    > > For Each i In Me![lstItems].ItemsSelected 'Determine the number of
    > > items
    > > selected in the list
    > >
    > > Criteria = Me![lstItems].ItemData(i) 'Assign the Customer Number a
    > > variable
    > >
    > >
    > >
    > > With rst
    > >
    > > .MoveFirst
    > >
    > > .FindFirst "[ID] = " & Criteria & "" 'Locate a Customer Number that
    > > matches the one selected from the list
    > >
    > > .Edit
    > >
    > > .Fields("Assigned") = 0 'Uncheck the checkbox
    > >
    > > .Fields("AssignedTo") = "" 'Clear the person's name from the record
    > >
    > > .Fields("DateAssigned").Value = "" 'Clear the Assign date
    > >
    > > .Update
    > >
    > > End With
    > >
    > > rst.Close
    > >
    > > Next i 'Loop through the next record
    > >
    > > Set db = Nothing 'Unset the Database
    > >
    > > Set rst = Nothing 'Unset the recordset
    > >
    > >
    > >
    > > Me.lstItems.Requery
    > >
    > > Exit Sub
    > >
    > >
    > > --
    > > Gina

    >
    >
    >
     

Share This Page