Custom SQL question...

[skc]

I have developed a custom web application, where I am
updating a record in my Access 2000 table using the UPDATE
query.

Now I wish to blank some fields from a record. I am using
the syntax:

UPDATE tablename Set fieldtoblank1=' ', fieldtoblank2=' '
WHERE ID='::ID::'

....but this does not work.

I need help.

Thanks,

skc

 

[Bob Lehmann]

Not sure what "doesn't work" means, but if ID is numeric, remove the ticks.

Bob Lehmann

 

[skc]

Still does not work.

I get "Data type mismatch..." - it is as if I am saving a
character space into the field instead of
deleting/blanking it.

I am doing something wrong here.

 

[Thomas A. Rowe]

You need to check your database field attributes to allow Nulls values.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

 

[skc]

Thomas,

I checked this and I have my 3 fields which I want to
blank using an update command set as:

1. Field1: Number
- Required = No

2. Field2: Text
- Required = No
- Allow zero length = Yes

3. Field3: Date/Time
- Required = No

Please advise - I believe that I have the correct settings?

Skc

 

[Thomas A. Rowe]

Date/Time Fields are a problem, I am not sure how to set it to null/empty
Number fields, I would set to zero

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

 

[skc]

Thomas,

For number fields - you mean poke a zero '0' into the
field?

There must be another way to do all of this including the
date/time field.

skc

 

[Bob Lehmann]

You'll have to work out all the weird FP gunk like ::ID::

I also don't know what your table really looks like, or what datatype id is,
so you may have to make changes.

But this should work....
update table1 set field1=null, field2='CHANGED', field3=null
where id=1

Bob Lehmann

 

[Thomas A. Rowe]

See below.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================

Thomas,

For number fields - you mean poke a zero '0' into the
field?
Yes


There must be another way to do all of this including the
date/time field.

I have so far never had a need to clear a date field, but I do get errors if I leave it blank, after
it was filled in.


Have you considered storing this group of info in another table, where you could just delete the
record?

 

[Bob Lehmann]

There is. See my answer.


Bob Lehmann

Thomas,

For number fields - you mean poke a zero '0' into the
field?

There must be another way to do all of this including the
date/time field.

skc