Custom SQL question...

S

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
 
B

Bob Lehmann

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

Bob Lehmann
 
S

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.
 
T

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.
==============================================
 
S

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
 
T

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.
==============================================
 
S

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
 
B

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
 
T

Thomas A. Rowe

See below.

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

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


skc said:
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.
Click to expand...

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?
 
B

Bob Lehmann

There is. See my answer.


Bob Lehmann

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