Delete only specific field in record to Null

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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 said:
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
Click to expand...
 
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 said:
Sorry, only the date field doesn't work.
--
Gina


Gina said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top