How: if not null then delete text in a query

M

Michelle Watson

Fields: DispenseDate, Cassette, freezer, name, PHN, DOB-
all in one table

I would like to "delete" info in certain fields but
keep "cassette and freezer" fields at all times (unique
key). So I enter into dispensedate field to choose
which "records I wish to delete".

If i have a product in the freezer in a certain cassette
and then is taken out - I would like the cassette number
to remain in the table (have a "empty query) but to take
out all of the record information.

Tried for most of the day - is null, is not null, IIF,
switch and can not figure it out. Please point me in the
right direction.

Thanks
 
K

Ken Snell

I'm not completely clear about what you want to do. Is it this:
For each record that has a dispense date equal to the dispense date that
you specify, delete values from the fields "DispenseDate", "name", "PHN",
and "DOB"?

If yes, the update query would look something like this:

UPDATE Tablename
SET Tablename.DOB = Null, Tablename.DispenseDate = Null,
Tablename.PHN = Null, Tablename.[Name] = Null
WHERE Tablename.DispenseDate = [Enter Dispense Date];

Note: It's not a good idea to use Name as the name of a field. Name is a
reserved word in ACCESS, and you can confuse ACCESS greatly if you use such
names as names.

See ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
 
M

Michelle Watson

THank you Ken!!!! It WORKED!! I think I was trying to
complicate things too much. Now I have to do a macro to
sombine my two action queries with "send keys" (which I
never done before!). Wish me luck!

Your advice :)
Did not include fields: FREEZER, RACK, CASSETTE

Updated fields to "NULL": CELLS, DONOR NAME, HOSP #,
RECIPIENT NAME, RECIPIENT #, ACH - R, PROV, COMPONENT #,
FREEZE DATE, THAW DATE, DECEASED, DECEASED DATE, COMMENTS,
R & D
-----Original Message-----
I'm not completely clear about what you want to do. Is it this:
For each record that has a dispense date equal to the dispense date that
you specify, delete values from the
Click to expand...
fields "DispenseDate", "name", "PHN",
and "DOB"?

If yes, the update query would look something like this:

UPDATE Tablename
SET Tablename.DOB = Null, Tablename.DispenseDate = Null,
Tablename.PHN = Null, Tablename.[Name] = Null
WHERE Tablename.DispenseDate = [Enter Dispense Date];

Note: It's not a good idea to use Name as the name of a field. Name is a
reserved word in ACCESS, and you can confuse ACCESS greatly if you use such
names as names.

See ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en- us;286335


--

Ken Snell
<MS ACCESS MVP>

Fields: DispenseDate, Cassette, freezer, name, PHN, DOB-
all in one table

I would like to "delete" info in certain fields but
keep "cassette and freezer" fields at all times (unique
key). So I enter into dispensedate field to choose
which "records I wish to delete".

If i have a product in the freezer in a certain cassette
and then is taken out - I would like the cassette number
to remain in the table (have a "empty query) but to take
out all of the record information.

Tried for most of the day - is null, is not null, IIF,
switch and can not figure it out. Please point me in the
right direction.

Thanks
Click to expand...


.
Click to expand...
 
K

Ken Snell

Not clear what you mean by combine two action queries with "send keys"? What
are you trying to do?
 
Top